Interpolation function

[Robert Clapham]

Can anyone help please, I'm looking to be able to
straight line interpolate in a table between fixed points.

For instance, if I have a column of dates and a column of
prices, I would like to be able to input a date in
between the dates in the column and have the function
interpolate the price for that date.

Does such a thing exist in Excel?

Many thanks
Robert

[John Walkenbach]

Excel does not have that capability. Here's a custom VBA function that might
work for you. Copy it to a VBA module. Then you can use a formula like this:

=VINTERPOLATE(E2,B3:C45,2)

Function VINTERPOLATE(lookup_value, table_array, col_index_num)
' Performs linear interpolation
Dim NumRows As Long, i As Long
Dim range1 As Range, range2 As Range
NumRows = table_array.Rows.Count
Set range1 = table_array.Columns(1)
Set range2 = table_array.Columns(col_index_num)
' check for case if val = last value in range1
If lookup_value = range1.Cells(NumRows) Then
VINTERPOLATE = range2.Cells(NumRows)
Exit Function
End If
' Return an error if lookup_value is not within range1
If lookup_value > range1.Cells(NumRows) Or lookup_value < range1.Cells(1)
Then
VINTERPOLATE = Evaluate("NA()")
Exit Function
End If
' Do linear interpolation
For i = 1 To NumRows - 1
If lookup_value >= range1.Cells(i) And lookup_value <= range1.Cells(i + 1)
Then
VINTERPOLATE = (range2.Cells(i + 1) + (range2.Cells(i) - _
range2.Cells(i + 1)) * (lookup_value - range1.Cells(i + 1)) / _
(range1.Cells(i) - range1.Cells(i + 1)))
Exit Function
End If
Next i
End Function

John Walkenbach
For Excel tips, macros, & downloads...
http://j-walk.com/ss

"Robert Clapham" <robert_...@scotiacapital.com> wrote in message
news:129401c2343c$deac6f80$19ef2ecf@tkmsftngxa01...

[Robert Clapham]

Many thanks

>.
>