Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to do linear interpolation between entries in lookup table
What is the best way to do linear interpolation between entries in a lookup
table? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to do linear interpolation between entries in lookup table
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 InsertModule. 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(..) ================================================ "jimeisen" wrote in message ... | What is the best way to do linear interpolation between entries in a lookup | table? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to do linear interpolation between entries in lookup table
Niek Otten wrote:
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup table | Excel Discussion (Misc queries) | |||
Sum items in a lookup table. | Excel Discussion (Misc queries) | |||
How do I lookup a corresponding value in another table | Excel Worksheet Functions | |||
pivot table sort entries that don't yet appear in table | Excel Discussion (Misc queries) | |||
Multiple table lookup | Excel Discussion (Misc queries) |