If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to get intermediate values from smooth graph in Excel ?
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 ? |
#2
|
|||
|
|||
How to get intermediate values from smooth graph in Excel ?
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/ _______ "Tushar" wrote in message ... 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 ? |
#3
|
|||
|
|||
How to get intermediate values from smooth graph in Excel ?
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/E...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 -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Tushar" wrote in message ... 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 ? |
#4
|
|||
|
|||
How to get intermediate values from smooth graph in Excel ?
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[i], y[i] are the values at the point of the interpolation) Starting with the interpolation formula: y[i] - y[1] x[i] - x[1] ----------- = ------------ y[2] - y [1] y[i] - y[1] From which we get: y[i] = (x[i] - 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[i] = ----------------------- + 5.5 = 7.18 2 - 1 "Tushar" wrote: 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 ? |
#5
|
|||
|
|||
How to get intermediate values from smooth graph in Excel ?
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 "Bernard Liengme" wrote: 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/E...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 -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Tushar" wrote in message ... 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 ? |
#6
|
|||
|
|||
How to get intermediate values from smooth graph in Excel ?
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/micro...2966520eccdb1f 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 "Jerry W. Lewis" wrote: 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_...ample_file.zip to get 7.248992 as the value interpolated by the chart smoother at 1.56 Jerry "Tushar" wrote: 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 ? |
#7
|
|||
|
|||
How to get intermediate values from smooth graph in Excel ?
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_...ample_file.zip to get 7.248992 as the value interpolated by the chart smoother at 1.56 Jerry "Tushar" wrote: 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 ? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Help with an excel graph problem | Jckksk | Charts and Charting | 1 | November 11th, 2005 07:58 PM |
Excel Range Value issue (Excel 97 Vs Excel 2003) | Keeno | General Discussion | 2 | June 13th, 2005 02:01 PM |
Copying an Excel Graph to PowerPoint using VBA Code | Sandy | Powerpoint | 0 | February 7th, 2005 06:41 AM |
WHY | General Discussion | 9 | December 16th, 2004 12:49 AM | |
Excel Graph displays Upside Down within Power Point | Alex.Dawson | Powerpoint | 1 | October 13th, 2004 05:02 PM |