How do I find value on a graph between data points?

I have a set of data points, ie. x = 1,2,3,4,5,6 corresponding with
y=4,17,18,22,15,7

I want a cell that will work out the expected value for y for any value (ie.
x=3.3, y=?) when the x value is provided to it from another cell. I want to
be able to change that input cell and automatically recalculate the new value
of y.

I need some kind of routines that finds the data points either side of the
required value and interpolate between them. How do I make this routine and
carry out the interpolation?

Although not designed for this use, the FORECAST function will do an
excellent job of interpolating between two known point.

It will also, of course extrapolate.
--
Gary''s Student


"Banio" wrote:

> I have a set of data points, ie. x = 1,2,3,4,5,6 corresponding with
> y=4,17,18,22,15,7
>
> I want a cell that will work out the expected value for y for any value (ie.
> x=3.3, y=?) when the x value is provided to it from another cell. I want to
> be able to change that input cell and automatically recalculate the new value
> of y.
>
> I need some kind of routines that finds the data points either side of the
> required value and interpolate between them. How do I make this routine and
> carry out the interpolation?

If you make a XY-Scatter chart of the x/y, you can then right click on
the data series and select Add Trendline. Select polynomial (order 2)
and in the options tab select Display Equation on Chart.

This will show you that the closest function to your data is the
quadratic:
y = -2.4464x2 + 17.496x - 10.3

If you have your input value in cell K1, then the formula
=-2.4464*K1^2 + 17.496*K1 - 10.3
will produce the value closest to your data quadratic pattern.

HTH
Kostis Vezerides

Excel's chart smoother appears to use Bezier curves. Brian Murphy has
posted code for Bezier curves, but the link he gave
http://www.xlrotor.com/excel_stuff.htm
appears to be obsolete. I

In most cases, the difference between Bezier curves and cubic splines is
minimal. An example where the difference is noticeable is
http://www.google.com/groups?threadm...0no_e-mail.com
David Braden has posted code for cubic splines, but Google changed their
addressing and my saved link is now broken. You might find it by searching.

Jerry

Banio wrote:

> I have a set of data points, ie. x = 1,2,3,4,5,6 corresponding with
> y=4,17,18,22,15,7
>
> I want a cell that will work out the expected value for y for any value (ie.
> x=3.3, y=?) when the x value is provided to it from another cell. I want to
> be able to change that input cell and automatically recalculate the new value
> of y.
>
> I need some kind of routines that finds the data points either side of the
> required value and interpolate between them. How do I make this routine and
> carry out the interpolation?

One additional problem w/ Bezier curves not mentioned in earlier posts: if
you want to fit a *function* to your data, you may run into problems with
B-curves, as they can (depending on the data) end up with non-functional
(i.e., 1 to several) correspondences.

Dave

"Jerry W. Lewis" <post_a_reply@no_e-mail.com> wrote in message
news:435ECB6A.1010302@no_e-mail.com...
> Excel's chart smoother appears to use Bezier curves. Brian Murphy has
> posted code for Bezier curves, but the link he gave
> http://www.xlrotor.com/excel_stuff.htm
> appears to be obsolete. I
>
> In most cases, the difference between Bezier curves and cubic splines is
> minimal. An example where the difference is noticeable is
> http://www.google.com/groups?threadm...0no_e-mail.com
> David Braden has posted code for cubic splines, but Google changed their
> addressing and my saved link is now broken. You might find it by
> searching.
>
> Jerry
>
> Banio wrote:
>
>> I have a set of data points, ie. x = 1,2,3,4,5,6 corresponding with
>> y=4,17,18,22,15,7
>>
>> I want a cell that will work out the expected value for y for any value
>> (ie. x=3.3, y=?) when the x value is provided to it from another cell. I
>> want to be able to change that input cell and automatically recalculate
>> the new value of y.
>>
>> I need some kind of routines that finds the data points either side of
>> the required value and interpolate between them. How do I make this
>> routine and carry out the interpolation?
>

In article <435ECB6A.1010302@no_e-mail.com>, post_a_reply@no_e-mail.com
says...
> Excel's chart smoother appears to use Bezier curves. Brian Murphy has
> posted code for Bezier curves, but the link he gave
> http://www.xlrotor.com/excel_stuff.htm
> appears to be obsolete. I
>
http://www.xlrotor.com/resources/files.shtml

Scroll down to 'Excel Related Tools and Sites'

--
Regards,

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

In article <435ECB6A.1010302@no_e-mail.com>, post_a_reply@no_e-mail.com
says...
> Excel's chart smoother appears to use Bezier curves. Brian Murphy has
> posted code for Bezier curves, but the link he gave
> http://www.xlrotor.com/excel_stuff.htm
> appears to be obsolete. I
>
> In most cases, the difference between Bezier curves and cubic splines is
> minimal. An example where the difference is noticeable is
> http://www.google.com/groups?threadm...0no_e-mail.com
> David Braden has posted code for cubic splines, but Google changed their
> addressing and my saved link is now broken. You might find it by searching.
>
> Jerry
>
> Banio wrote:
>
> > I have a set of data points, ie. x = 1,2,3,4,5,6 corresponding with
> > y=4,17,18,22,15,7
> >
> > I want a cell that will work out the expected value for y for any value (ie.
> > x=3.3, y=?) when the x value is provided to it from another cell. I want to
> > be able to change that input cell and automatically recalculate the new value
> > of y.
> >
> > I need some kind of routines that finds the data points either side of the
> > required value and interpolate between them. How do I make this routine and
> > carry out the interpolation?
>
>