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.

InterestingI’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.