Created by Myrna Larson
An UDF that returns values “between” the points in the lookup table.
Function InterpolateVLOOKUP(x As Single, Table As Range, _
YCol As Integer)
‘Returns the interpolated number from the first column of
‘Table and the YCol column of table.
‘x – the number between two numbers in the first column of Table
‘Table – The range of values with at least YCol columns
‘YCol – the column of Table used for the Y-side of the interpolation
‘Dim TableRow As Integer, Temp As Variant
Dim TableRow As Long, Temp As Variant ‘see below
Dim x0 As Double, x1 As Double, y0 As Double, y1 As Double
Dim d As Double
‘TableRow – the row of Table that is less than or equal to x
‘x0, x1, y0, y1 – the coordinates to interpolate
‘d – Your guess is as good as mine
‘skip the error when match returns N/A
On Error Resume Next
‘Find the row in Table that’s less than or equal to x
Temp = Application.WorksheetFunction.Match(x, Table.Resize(, 1), 1)
‘If Match returns an error, return the error
If IsError(Temp) Then
InterpolateVLOOKUP = CVErr(Temp)
Else
‘TableRow = CInt(Temp)
‘If your table has more than 32,767 rows, this will fail, so
‘it should be CLng. I don’t know how Temp could be anything
‘but a Long Integer, though.
TableRow = Temp
‘The first coordinates are the from the row just less than x
x0 = Table(TableRow, 1)
y0 = Table(TableRow, YCol)
‘If x is the same as the row just less than (or equal to) x,
‘return the value from YCol, just like a normal VLOOKUP
If x = x0 Then
InterpolateVLOOKUP = y0
Else
‘Get the second coordinates from the next row
x1 = Table(TableRow + 1, 1)
y1 = Table(TableRow + 1, YCol)
‘Do the math
InterpolateVLOOKUP = (x – x0) / (x1 – x0) * (y1 – y0) + y0
End If
End If
End Function
YCol As Integer)
‘Returns the interpolated number from the first column of
‘Table and the YCol column of table.
‘x – the number between two numbers in the first column of Table
‘Table – The range of values with at least YCol columns
‘YCol – the column of Table used for the Y-side of the interpolation
‘Dim TableRow As Integer, Temp As Variant
Dim TableRow As Long, Temp As Variant ‘see below
Dim x0 As Double, x1 As Double, y0 As Double, y1 As Double
Dim d As Double
‘TableRow – the row of Table that is less than or equal to x
‘x0, x1, y0, y1 – the coordinates to interpolate
‘d – Your guess is as good as mine
‘skip the error when match returns N/A
On Error Resume Next
‘Find the row in Table that’s less than or equal to x
Temp = Application.WorksheetFunction.Match(x, Table.Resize(, 1), 1)
‘If Match returns an error, return the error
If IsError(Temp) Then
InterpolateVLOOKUP = CVErr(Temp)
Else
‘TableRow = CInt(Temp)
‘If your table has more than 32,767 rows, this will fail, so
‘it should be CLng. I don’t know how Temp could be anything
‘but a Long Integer, though.
TableRow = Temp
‘The first coordinates are the from the row just less than x
x0 = Table(TableRow, 1)
y0 = Table(TableRow, YCol)
‘If x is the same as the row just less than (or equal to) x,
‘return the value from YCol, just like a normal VLOOKUP
If x = x0 Then
InterpolateVLOOKUP = y0
Else
‘Get the second coordinates from the next row
x1 = Table(TableRow + 1, 1)
y1 = Table(TableRow + 1, YCol)
‘Do the math
InterpolateVLOOKUP = (x – x0) / (x1 – x0) * (y1 – y0) + y0
End If
End If
End Function
Editor’s Note: Distracting comments and code modifications by me. Here’s an example:
Eight is between 7 and 9, so it returns 10 which is between 9 and 11.
Interesting–I’m sure there was an application in mind when it was created, so blame my lack of imagination, but I can’t think of anything I’d use this for. Can you give me an idea?
Myrna’s UDF is definitely cleaner than:
=((8-INDEX(D3:D10,MATCH(8,D3:D10,1)))/(INDEX(D3:D10,MATCH(8,D3:D10,1)+1)-INDEX(D3:D10,MATCH(8,D3:D10,1))))*(INDEX(E3:E10,MATCH(8,D3:D10,1)+1)-INDEX(E3:E10,MATCH(8,D3:D10,1)))+INDEX(E3:E10,MATCH(8,D3:D10,1))
focasting stuff?
forcasting, sorry!
The regular lookup is a step function, which is great for looking up tax rates or commissions. But there are many reasons to want a smoother function. Suppose a surveyor measured your yard, and determined a profile along one edge of the property which looked like this (don’t know how it will look in proportional ascii):
D=Distance from corner of lot (ft)
H=Height above corner of lot (ft)
D H
0 0
10 0
20 4
30 4
It’s a trivial example, but suppose you wanted to know the height at 15 feet. It’s flat from 0 to 10 ft, rises to 4 feet high over the next 10 feet, then is flat at 4 ft high for another 10 feet. VLOOKUP tells me that at 15 feet, the height is zero, while making a simple XY chart and manually interpolating shows it’s obviously not zero. I didn’t check Dick’s rendering of Myrna’s code, but I assume it will agree with the chart that at 15 feet from the corner, the terrain is 2 feet high.
It’s also possible to use an “InterpolateLookup” type formula for this purpose:
=PERCENTILE(E3:E10,PERCENTRANK(D3:D10,8,20))
if you are just using linear approximation, would it be more accurate to use the FORECAST function?
Simon: No. FORECAST creates a regression line taking all the data into account, not a piecewise linear interpolation. To do the latter one could use
=FORECAST(targetVal,
INDEX(YVals,MatchIdx):INDEX(YVals,MatchIdx+1),
INDEX(XVals,MatchIdx):INDEX(XVals,MatchIdx+1))
where MatchIdx is =MATCH(targetVal,XVals,1)
Comments on the original code:
It might be safer to test abs(x-x0)
I am trying to delete all rows in a data set that have a cell in the row containing an error (#N/A). Can anyone help with some code that would enable me to do this?
I worked out something quite similar when I had heat rates (fuel efficiencies for power plants) for specified levels of output, but I needed heat rates at intermediate levels as well. Interesting to note the ultimate source…
Option Explicit
Function Interpolate(X As Double, XRange As Range, YRange As Range, Optional InterpType As Integer = 0) As Double
‘ Function returns interpolated value of “Y” for supplied X, based on supplied ranges of known
‘ X and Y values. Optional “InterpType” argument controls how function responds to X arguments
‘ outside the known X values:
‘ If InterpType is:
‘ = 0: returns an error (default)
‘ = 1: extrapolates based on last two X-Y pairs (either two highest or two lowest)
‘ = 2: extrapolates based on first and last X-Y pair (full range of supplied values)
‘ = 3: extrapolates based on first or last X-Y pair and the origin (0-0)
‘ Other values return an error
‘ Function is based on the “InterpolateVLOOKUP” UDF developed by Myrna Larson, and published in the
‘ Excel Expert’s E-Letter, which can be found at: http://www.j-walk.com/ss/excel/eee/eee002.txt
Dim blErr As Boolean
Dim iBase As Integer
Dim iComp As Integer
Dim i As Integer
Dim dX0 As Double
Dim dX1 As Double
Dim dY0 As Double
Dim dY1 As Double
Dim Temp As Variant
On Error Resume Next
‘ ensure that XRange is sorted ascending so match function result is reliable
For i = 1 To XRange.Count – 1
If XRange(i + 1) = max(XRange)
Select Case InterpType
Case 0
If X XRange(XRange.Count) Then ‘X is IS > highest value in range – represents an error
blErr = True
End If
Case 1
iComp = iBase – 1
Case 2
iComp = 1
Case 3
iComp = 0
Case Else
blErr = True
End Select
Case Else
‘match returned position of value next larger than X, within XRange
iComp = iBase + 1
End Select
dX0 = XRange(iBase)
dY0 = YRange(iBase)
If X = dX0 Then
Interpolate = dY0
Else
dX1 = XRange(iComp)
dY1 = YRange(iComp)
End If
If blErr = True Then
Err.Raise Number:=11
Interpolate = CVErr(Err)
‘return “div0? error, to ensure that error propagates through s/sheet calculations
Else
Interpolate = (X – dX0) / (dX1 – dX0) * (dY1 – dY0) + dY0
End If
End If
End Function
I have had this workbook lying around on for a few years now and I finally got around to creating a tutorial out of it.
Interpolation
http://www.tushar-mehta.com/excel/newsgroups/interpolation/index.html
It includes, for a function of 1 variable, i.e., y=f(x), piece-wise linear interpolation using worksheet functions and formulas as well as VBA. For a function of 2 variables, i.e., z=f(x,y), it includes VBA code.
I am looking for a excel function that will interpolate from a 3-d table with two inputs.
Could you help?
thanks
Carlos
I am completely new to UDFs and I’m trying to use your functions. When I try to use the function by Myrna Larson I get #VALUE! in the cell, where I would like to be recieving the interpolated value. When I use the function by dcardno I get #VALUE! and and error. Once back into Microsoft Visual Basic the top function line is highlighted yellow with an error to the left. Any help would be greatly appreciated. Such a function would be highly useful to me.
Thankyou
David – Myrna’s code works for me. To step through the code you can set a break point in the code by left-clicking in the left margin next to any executable statement (ie not a Dim, comment or blank line). You should get a brown circle marking the break point. Then go back to the worksheet, select a cell containing the UDF you want to check, press F2 then enter. You should be taken to the line of code where you inserted the break point. Now press F8 to step through the code one line at a time, and you should be able to find the problem. Don’t forget to clear the break point by clicking on it, or use the debug menu, clear all breakpoints.
The dcardno code seems to have lost some lines when it was pasted here. That sometimes happens.
The Range() indexing is backwards. Anything that looks like Range(column, row) should be rewritten as Range(row, column), eg:
x0 = Table(1, TableRow)
y0 = Table(YCol, TableRow)
rather than:
x0 = Table(TableRow, 1)
y0 = Table(TableRow, YCol)
Tushar Mehta’s solution as provided in his web site worked as charm. 2D interpolation is not a problem anymore.