How to get intermediate values from smooth graph in Excel ?

[Guest]

For example:
x y
1 5.5
2 8.5
3 10.5
4 11.5
For x=1.56, what will be the corresponding value of y from drawn graph ?

 

[Jon Peltier]

Check this post in the archives:

http://www.mcse.ms/archive144-2004-1-346324.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Bernard Liengme]

You could get an approximate value using interpolation: this imagines a
straight line drawn thru two points that encompass you x value
The slope of the line joining x=1 and x=2 is (8.5-5.5)/(2-1) = 3
So y =3x+c
The line goes thru the point (1, 5.5) so 5.5 = 3*1+c hence c = 2.5
For x=1.56; y=3*1.56 + 2.5 = 7.81

For a better approximation insert a trendline on the chart (use Help and
then return here with questions)
I fitted a second order polynomial (a quadratic) and got y =-0.5x^2 + 4.5x +
1/5 with a R2 value of 1 (a very good fit), I used LINEST (see
http://www.stfx.ca/people/bliengme/ExcelTips/Polynomial.htm)
to get these values into cells on the worksheet. When I use x=1.56 my
y-value is 7.3032

best wishes

 

[Guest]

If the points are connected linearly with no smoothing, would not f(x) at x =
1.56 be equal to 7.18.

This is based on the interpolation formula:
(where x[1], y[1] are the known values immediately preceding the
interpolation; x[2], y[2] are the known values immediately following the
interpolation; x, y are the values at the point of the interpolation)

Starting with the interpolation formula:

y - y[1] x - x[1]
----------- = ------------
y[2] - y [1] y - y[1]

From which we get:

y = (x - x[1])(y[2] - y[1])
----------------------------- + y[1]
x[2] - x[1]

Substituting in the values for x = 1.56 we have


(1.56 - 1)(8.5 - 5.5)
y = ----------------------- + 5.5 = 7.18
2 - 1

 

[Guest]

I think you may have made a typo on your linear interpolation, Berrnard. I
believe that you meant y = 3*1.56 + 2.5 = 7.18

Steve

 

[Guest]

Tushar, you specifically asked about how Excel would draw it, which I
addressed in my previous post. If you are more generally interested in in
different methods of interpolation, then there are several reasonable
approached:

linear: 7.18 (as noted by several respondants)

Bezier: 7.248992 (as noted in my previous post)

cubic spline: 7.2568768
http://groups.google.com/group/microsoft.public.excel.programming/msg/6a2966520eccdb1f

polynomial fit (order >=2): 7.3032 =TREND(ydata,xdata^{1,2},1.56^{1,2})
since the posted observations exactly fit 1.5+4.5*x-x^2/2

rational linear: 7.34210526315789 from fitting the monotonic function
y=(a+b*x)/(1+c*x)

Jerry

 

[Guest]

Brian Murphy has shown that the Excel chart smoother appears to use Bezier
curves. You can plug your values directly into his example file
http://www.xlrotor.com/Smooth_curve_bezier_example_file.zip
to get 7.248992 as the value interpolated by the chart smoother at 1.56

Jerry