how to do linear interpolation between entries in lookup table

[jimeisen]

What is the best way to do linear interpolation between entries in a lookup
table?

 

[Niek Otten]

One way (not necessarily "the best" way) is to use this UDF. Instructions for UDFs at the end.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel
' =========================================================================

Function TableInterpol(ToFind As Double, Table As Range, ResultColumnNr As Long, _
Optional SortDir, Optional KeyColumnNr)
' Niek Otten, March 22 2006
' Works like Vlookup, but interpolates and has some extra options
' 1st argument: Key to look for. Numbers only!
' 2nd argument: Range to look in and get the result from. Numbers only!
' 3rd argument: Relative column number in the range to extract the result from
' Optional 4th argument: defaults to: "Ascending"; any supplied argument forces Descending
' Optional 5th argument: Relative column number in the range to search the key in,
' defaults to 1

Dim RowNrLow As Long
Dim RowNrHigh As Long
Dim ResultLow As Double
Dim ResultHigh As Double
Dim KeyFoundLow As Double
Dim KeyFoundHigh As Double

If IsMissing(SortDir) Then
SortDir = 1
Else
SortDir = -1
End If

If IsMissing(KeyColumnNr) Then
KeyColumnNr = 1
End If

RowNrLow = Application.WorksheetFunction.Match(ToFind, Intersect(Table, Table.Cells(KeyColumnNr). _
EntireColumn), SortDir)
ResultLow = Table(RowNrLow, ResultColumnNr)

If ToFind = ResultLow Then
TableInterpol = Table(RowNrLow, ResultColumnNr) ' do not interpolate for exact matches
Exit Function
End If

RowNrHigh = RowNrLow + 1
ResultHigh = Table(RowNrHigh, ResultColumnNr)
KeyFoundLow = Table(RowNrLow, KeyColumnNr)
KeyFoundHigh = Table(RowNrHigh, KeyColumnNr)
TableInterpol = ResultLow + (ToFind - KeyFoundLow) / (KeyFoundHigh - KeyFoundLow) _
* (ResultHigh - ResultLow)

End Function
' =========================================================================


================================================
Pasting a User Defined Function (UDF)
Niek Otten, March 31, 2006

If you find a VBA function on the Internet or somebody mails you one, and you don't know how to implement it, follow these
steps:

Select all the text of the function.
CTRL+C (that is, press and hold down the CTRL key, press C, release both). This a shortcut for Copy.
Go to Excel. Press ALT+F11 (same method: press and hold the ALT key, press the F11 key and release both). You are now in the
Visual Basic Editor (VBE).
From the menu bar, choose Insert>Module. There should now be a blank module sheet in front of you. Click in it and then
press CTRL+V (same method.). This a shortcut for Paste. You should now see the text of the function in the Module.
Press ALT+F11 again to return to your Excel worksheet.
You should now be able to use the function as if it were a built-in function of Excel, like =SUM(..)
================================================


| What is the best way to do linear interpolation between entries in a lookup
| table?

 

[John Collins]

One way (not necessarily "the best" way) is to use this UDF. Instructions for UDFs at the end.

I have written and used similar functions, but they can become very slow
in large files, especially when automatic recalculation sort of gets
lost and recalculates every thing every time whether it needs it nor
not. I think functions written in the modules are far far slower than
built in functions.

An alternative is to use MATCH to find your table entry row then use
INDEX to pick out the four values you need for interpolation.

John