interpolation routine for Excel

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

Has anyone written an interpolation function for Excel? I have an equation
that works, but it is a pain to edit and check each time. There should be a
way to standardize it for use with most tables.
 
The first function which I find when I type the word "interpolate" into
Excel help is FORECAST. Have you tried that?
 
The FORECAST() function does a great job of linear interpolation as well as
forecasting.
 
Why not write a UDF that could be used in any file if you store it in
Personal.xls
best wishes
 
The problem with forecast is that it does a linear regression of the line
through the points so that the line may not actually cross any of the points.
What many of us would like is a function that draws a straight line between
each individual point and extrapolates the value based on each line segment.
 
The problem with forecast is that it does a linear regression of the line
through the points so that the line may not actually cross any of the points.
What many of us would like is a function that draws a straight line between
each individual point and extrapolates the value based on each line segment.
Click to expand...

Just set up your formulas to use the two points that straddle your unknown.
--ron
 
Unfortunately that equation can get a little hairy and can exceed some of the
limitations for formulae. At least any formula that I have seen.
 
Unfortunately that equation can get a little hairy and can exceed some of the
limitations for formulae. At least any formula that I have seen.
Click to expand...

It's hard for me to deal in these kinds of hypotheticals. But if you have a
data table, it's usually easy enough to find the appropriate values for which
to use the FORECAST function by using one or more of the lookup functions, or
by using the combination of INDEX and MATCH.
--ron
 
The percentile(-rank) formulas also have built-in interpolation which can be
combined in a simple and efficient way. With the general set up of x and y
values in columns a and b and a lookup value z in cell c1, try entering:

=percentile(b:b,percentrank(a:a,c1,30))

(the 30 just ensures full precision is used in the calculation). This
formula applies to any increasing x and y values, which accounts for many
cases of interest, and does not depend on the order of x or y. With a
decreasing trend you can use "1-percentrank" in the formula instead.

For greater precision (cubic) curves can be used to join points:

=trend(y,((max(rank(z,(z,x)),2)-rank(x,x)-0.5)^2<3)*x^{0,1,2,3},z^{0,1,2,3},0)

(which is an extension of the general linear case using 3's in place of
1's). Plotting this for a range of z values should show a very good fit to
the data. HTH.
 
Back
Top