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

B

Banio

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?
 
G

Gary''s Student

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.
 
V

vezerid

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
 
J

Jerry W. Lewis

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=3E7291CA.7020907@no_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
 
D

David J. Braden

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Find Max value within a Offset 2
Connecting ALL Data Points; Sequential Or Not ? 1
How do I get an intermediate value between data points on a graph? 1
Finding Intersection Points between different lines 1
Store Excel value 0
Using =RANDBETWEEN(a,b) in a paers of a whole colume 1
MS Excel - changing reference column value based on another column 2
Excel - changing column reference based on value of other column 2

Top