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?
--
David Biddulph

"Steve" wrote in message
...
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 FORECAST() function does a great job of linear interpolation as well as
forecasting.
--
Gary''s Student - gsnu200806


"Steve" wrote:

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.

Why not write a UDF that could be used in any file if you store it in
Personal.xls
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Steve" wrote in message
...
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 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.

"Gary''s Student" wrote:

The FORECAST() function does a great job of linear interpolation as well as
forecasting.
--
Gary''s Student - gsnu200806


"Steve" wrote:

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.

On Thu, 9 Oct 2008 14:46:11 -0700, Brazell
wrote:

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.

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.

"Ron Rosenfeld" wrote:

On Thu, 9 Oct 2008 14:46:11 -0700, Brazell
wrote:

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.

Just set up your formulas to use the two points that straddle your unknown.
--ron

On Fri, 17 Oct 2008 07:31:02 -0700, Brazell
wrote:

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.

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.

"Steve" wrote:

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.

Hello,

If you like to use a UDF:
http://www.sulprobil.com/html/interpolate.html

Regards,
Bernd

That is perfect! Thanks!

"Bernd P" wrote:

Hello,

If you like to use a UDF:
http://www.sulprobil.com/html/interpolate.html

Regards,
Bernd