How Do I Get VLOOKUP to Interpolate? 1

[Christine74]

I have an array of values and am using the VLOOKUP function to pull the appropriate values from my table so I can run some calculations using those variables.

The problem I'm having is that the values being extracted by VLLOKUP are the *exact* values listed in the array, but I would prefer values that have been linearly interpolated. How can I do this?

I hope my description was clear.

Thanks!

-Christine

 

[GregLocock]

thread770-51434

has several approaches. I found using INDEX and MATCH was the neatest

Cheers

Greg Locock

 

[Christine74]

Thanks, Greg. That thread is just what I'm looking for!

I'm very surpised to learn that Excel doesn't have such a function. I was hoping that maybe there was some add-in or something that I missed that would this, but apparently there isn't.

Thanks again,

-Christine

 

[d23]

Christine74

I'm not sure what you are working with, but with your sheet can you make several columns and do the vlookup as column 1, column 2 etc.??? This could return several answers for a single problem. It then depends on how you write the formulas in the array or columns that you are looking up.

I'm not that good at explaining so I hope this makes since and helps.

David

 

[GBinns]

I have found the worksheet function "forecast" to be very useful for interpolating.

Gavin.

 

[KevinNZ]

There are a number of addin fuctions out there you can down load. Try looking up "Excel fuction interpolation" in Goole.

cheers
Kevin

 

[athomas236]

As far as I know forecast cannot be used for interpolation unless the data has an exact linear relationship.

For example with data y=x^2, x= 1 to 10, using forecast at x=4 gives value of 22 instead of 16.

If I am doing anything wrong I would be pleased to know.

Regards
athomas236

 

[athomas236]

Not sure why VLOOKUP is suggested for interpolation I always use the following function for interpolation.

regards,
athomas236

Function Interp(X, Y, Xe)
' This function interpolates and extrapolates linearly on
' arrays of X and Y values
' Input is as follows
' Array of X values that are in a sequence of increasing values
' Array of Y values
' Value Xe for which value of Y is required

' Count number of X values
For Each Item In X
CountX = CountX + 1
Next Item

' Count number of Y values
For Each Item In Y
CountY = CountY + 1
Next Item

If CountX >= 2 Then GoTo 10
' Error not enough values so end calculation
Msg = "Need at least 2 values of X"
Ans = MsgBox(Msg, vbOKOnly)
Interp = "Error"
GoTo 400

10 If CountX = CountY Then GoTo 100
' Error should be equal number of X and Y values
Msg = "Need equal number of X & Y values"
Ans = MsgBox(Msg, vbOKOnly)
Interp = "Error"
GoTo 400

100 If Xe > X(1) Then GoTo 200
' Xe less than smallest X
Interp = Y(2) - ((Y(2) - Y(1)) / (X(2) - X(1)) * (X(2) - Xe))
GoTo 400

200 If Xe < X(CountX) Then GoTo 300
' Xe greater than largest X
Interp = Y(CountX - 1) + ((Y(CountX) - Y(CountX - 1)) / (X(CountX) - X(CountX - 1)) * (Xe - X(CountX - 1)))
GoTo 400

300 For Item = 1 To CountX
If Xe >= X(Item) And Xe <= X(Item + 1) Then GoTo 310
Next Item
310 Interp = (Y(Item + 1) - Y(Item)) / (X(Item + 1) - X(Item)) * (Xe - X(Item)) + Y(Item)

400 '
End Function