Insert Text representing an array into a custom function

[MylesMc]

Hey guys,

I have attached a spreadsheet where the current problem I'm working on involves data found on Columns Y through AK. I have an output table that I need to run some interpolation calculations on. The attached spreadsheet is an exerpt of a larger spreadsheet that is too big to attach to this forum. It contains a module (module1.bas) for an interpolation equation. The function is named interp. The arguement is interp(X range, Y Range, X Value) and returns a Y value at the X value interpolated from the X and Y ranges.

I now have text representing a range (example text: M227:M263) that has been concatenated from calculations performed to get the correct ranges of data that needs to be interpolated over. I need a way to place this concatenated text into my interp function and get a valid answer (=interp(M227:M263,N227:N263,W5). simply placing the cell containing the text string representing the range does not work. I need this to be dynamic such that cells in columns AI and AJ can autofill and the functions within the cells contain the correct ranges as currently shown in columns AE through AH.

Any help is appreciated.

The code for the interp function contained in module1.bas is shown below and will have to be manually added to the spreadsheet:

Function interp(havecol As Range, wantcol As Range, have As Variant)
'count how many values are in the interpolation range
k = 1
Do While (havecol.Cells(k, 1).Value <> 0)
    k = k + 1
Loop
k = k - 1
'find where in the column the value is
'as soon as reach a value greater, stop--i is the place above

If have < havecol.Cells(k, 1).Value Then
    If have > havecol.Cells(1, 1).Value Then
        i = 1
        Do While (have > havecol.Cells(i, 1).Value)
            i = i + 1
        Loop
        valhi = havecol.Cells(i, 1).Value
        vallo = havecol.Cells(i - 1, 1).Value
        lookhi = wantcol.Cells(i, 1).Value
        looklo = wantcol.Cells(i - 1, 1).Value
        interp = (have - vallo) / (valhi - vallo) * (lookhi - looklo) + looklo
    Else

'extrapolate off the low end...
        If have <> 0 Then
'            interp = wantcol.Cells(1, 1).Value
            valhi = havecol.Cells(2, 1).Value
            vallo = havecol.Cells(1, 1).Value
            lookhi = wantcol.Cells(2, 1).Value
            looklo = wantcol.Cells(1, 1).Value
            interp = (have - vallo) / (valhi - vallo) * (lookhi - looklo) + looklo
        Else
            interp = 0
        End If
    End If
Else
'extrapolate off the high end...
    valhi = havecol.Cells(k, 1).Value
    vallo = havecol.Cells(k - 1, 1).Value
    lookhi = wantcol.Cells(k, 1).Value
    looklo = wantcol.Cells(k - 1, 1).Value
    interp = (have - vallo) / (valhi - vallo) * (lookhi - looklo) + looklo
End If
End Function

Thanks, Myles Mc

[Andy Pope]

You can use the INDIRECT function

so the formula would look something like,

=interp(INDIRECT(AE5),INDIRECT(AF5),W5)

Only problem is you don't seem to creating the range N227:N263. So you can include the OFFSET function to get that range

=interp(INDIRECT(AE5),OFFSET(INDIRECT(AE5),0,1),W5)

[JoePublic]

Your formulas are a little confusing as the ranges currently in the formulas don't appear to match the text ranges, but basically you need INDIRECT:
=interp(INDIRECT(AE5),INDIRECT(AF5),W5)
for example.

[MylesMc]

Worked a treat, Thanks!
I love learning new functions!

Also, thanks to Joe Public for providing a very similar response.

Yes, I see that I had an error in my spreadsheet example, good eyes!

You can use the INDIRECT function

so the formula would look something like,

=interp(INDIRECT(AE5),INDIRECT(AF5),W5)

Only problem is you don't seem to creating the range N227:N263. So you can include the OFFSET function to get that range

=interp(INDIRECT(AE5),OFFSET(INDIRECT(AE5),0,1),W5)