How Do I Get VLOOKUP to Interpolate?
How Do I Get VLOOKUP to Interpolate?
(OP)
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
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





RE: How Do I Get VLOOKUP to Interpolate?
has several approaches. I found using INDEX and MATCH was the neatest
Cheers
Greg Locock
RE: How Do I Get VLOOKUP to Interpolate?
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
RE: How Do I Get VLOOKUP to Interpolate?
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
RE: How Do I Get VLOOKUP to Interpolate?
Gavin.
RE: How Do I Get VLOOKUP to Interpolate?
cheers
Kevin
RE: How Do I Get VLOOKUP to Interpolate?
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
RE: How Do I Get VLOOKUP to Interpolate?
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