Linear Interpolation

[Guest]

Hello,

I am trying to create a formula that will lookup values in a column and find
those values in another column and then place the value in the row next to it
in a specific cell. So far this works with the formula
(=VLOOKUP(I6,$D$11:$E$223,2)). Now most of the time the value for cell I6
(thru I n) will not be found in the list ($D$11:$E$223) so I want to revise
the formula to do one of two things:

1. Always "interpolate" between points even when an exact match is found in
($D$11:$E$223)

2. When the value for cell I6 (thru I n) is not found then find the values
less than and greater than I6 and interpolate between the two points a value
based on the slope of the function defined by the two sets of corresponding
data.


this is driving me insane!!!! Please help me! I have 20 years of data to
enter and if I do it manually it will take 20 years.

 

[Tom Ogilvy]

http://groups.google.co.uk/groups?a...981&as_maxd=2&as_maxm=6&as_maxy=2005&safe=off

That should all be one line in your browser navigation window.

 

[Guest]

In order to come up with the interpolation formula (the easy way) just graph
the source data and add a linear trend line. Right click the trend line and
one of the options is to add the formula to the trend line. This will give
you the formula of the best line through all of the points on the graph. If
you wanted to interpolate between any two point look at the slope() and
intercept() formulas. Using these you should be able to create the Y=aX + b
formulas for each set of points.

 

[Tom Ogilvy]

Just a thought, but
that wouldn't work for the situation described/asked for unless the data was
linear and fell exactly on the trend line.

 

[Guest]

Agreed on the trend line part. It does assume that the formula of the best
line is desired. This is why I gave the Slope and Intercept formulas which
can be used on any two points. Using these formulas a linear formula can be
created for each point to the next point in the series. This will require
more calculations but it will work. Depends on what the user wants. I have
done something similar using both linear and polynomial regression (Trend
Formula) with success.

 

[Tom Ogilvy]

If the OP wants a linear interpolation between just two points in his data
as stated, unless his data falls exactly on his trendline, then using the
formula for the trend line will not work (the formula for the trendline is a
best fit for all the data points vice the two points being addressed -
unless the special case that the data falls exactly on the trendline). No
doubt you have had repeated success in all your endeavors, but no matter how
well made, a left shoe doesn't fit a right foot.

 

[Guest]

"repeated success in all your endeavors" seems to be an overly optomistic
evaluation of my Excel endeavors, but if you insist. I was just giving
two alternatives to Metalmaniac which I thought was identified in my original
reply. Which one will work best is up to him. I gave the trend line solution
because of his statement "based on the slope of the function defined by the
two sets of coresponding data".

 

[Tom Ogilvy]

My comments were only aimed at the use of the Trend line in a chart method.

Slope and intercept are useful, but personally I would look at Forecast or
Trend in this case.

but in any event, identifying the X /Y values to interpolate are more the
challenge in using any of them. Those methods should be contained/discussed
in the link provided.

 

[Guest]

As always it has been a pleasure butting propellors with you but I think we
have probably beaten this horse to death by now. Trend, Forecast, Trend Line,
Slope and Intercept. If that doesn't cover it nothing will. One day we will
have to get together and argue in person.

 

[Tushar Mehta]

I didn't go through all the hits in the google archive (the link Tom
provided) but using a formula for a general purpose solution is kinda
messy. By contrast a user defined function (UDF) might be a lot
cleaner. And, it can written to handle multiple a vector of input
values and return a vector of results with a single call!

In any case, if you want to stick with formulas, you would need to do
the following. Suppose the x and y data are in columns A and B
starting with row 1 (i.e., no header).

Then, define the following names (Insert | Names > Define...):
XVals =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
YVals =OFFSET(XVals,,1)
XMax =INDEX(XVals,COUNTA(Sheet1!$A:$A))
XMin =INDEX(XVals,1)
YMax =OFFSET(XMax,,1)
YMin =OFFSET(XMin,,1)

Now, if you want the interpolated y value corresponding to the x value
in D2, use

=IF(OR(D2<XMin,D2>XMax),"out of bounds",IF(ABS(D2-XMax)
<0.00000001,YMax,FORECAST(D2,OFFSET(YMin,MATCH(D2,XVals,1)-
1,0,2,1),OFFSET(XMin,MATCH(D2,XVals,1)-1,0,2,1))))

I wrote a UDF for linear interpolation some time back. I will try and
find it, but no promises.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions