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:

Code:

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

## Bookmarks