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
|
|||
|
|||
interpolating non-linear curves in excel graphs
I'm generally quite happy with how excel graphs the moderately
non-linear x-y data I supply it - the curves pass through all the data points with a pleasing fit. I'd just like to be able to get excel to interpolate on the graph for me. For instance, the curve passes through the points (x1, y1) and (x2, y2) which I supplied. How do I get excel to calculate and supply the value y3 from the point (x3, y3) on the curve it generated when I specify the value of x3? |
#2
|
|||
|
|||
interpolating non-linear curves in excel graphs
Assuming that the points (all three of them!) lie on a straight line y= mx +
c Let's say your know x's are in A1:A2 and known y's in B1:B2 m= SLOPE(y-values, x-values) so m =SLOPE(B1:B2,A1:A2) (put this in C1) c = INTERCEPT(y-values, x-values) so c = INTERCEPT(B1:B2,C1:C2) (put this in C2) With x3 in A3, in B3 use =C1*A3+C2 or: y1 = mx1 + c y2 = mx2 + c m = (y2-y1)/(x2-x1) c = ((y2-y1)-(x2+x1)(y2-y1)/y2-y1))/2 y3 = x3(y2-y1)/(x2-x1) + ((y2+y1)-(x2+x1)(y2-y1)/(y2-y1))/2 Remember all that great Grade 10 algebra? best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "chris" wrote in message oups.com... I'm generally quite happy with how excel graphs the moderately non-linear x-y data I supply it - the curves pass through all the data points with a pleasing fit. I'd just like to be able to get excel to interpolate on the graph for me. For instance, the curve passes through the points (x1, y1) and (x2, y2) which I supplied. How do I get excel to calculate and supply the value y3 from the point (x3, y3) on the curve it generated when I specify the value of x3? |
#3
|
|||
|
|||
interpolating non-linear curves in excel graphs
Hi Chris,
From your post it sounds like you are talking about the line that excel provides to link your data in an XY scatter chart. You can add a trendline to your data series that offers a lot more functionality. Namely better fit to your data, the ability to forecast values forward and backwards, the ability to calculate values of y for a given x or vice versa, etc. etc. Is this more along the lines you are thinking? If so post again with some more detail. Regards Martin |
#4
|
|||
|
|||
interpolating non-linear curves in excel graphs
Bernard Liengme wrote: Assuming that the points (all three of them!) lie on a straight line y= mx + c Let's say your know x's are in A1:A2 and known y's in B1:B2 m= SLOPE(y-values, x-values) so m =SLOPE(B1:B2,A1:A2) (put this in C1) c = INTERCEPT(y-values, x-values) so c = INTERCEPT(B1:B2,C1:C2) (put this in C2) With x3 in A3, in B3 use =C1*A3+C2 or: y1 = mx1 + c y2 = mx2 + c m = (y2-y1)/(x2-x1) c = ((y2-y1)-(x2+x1)(y2-y1)/y2-y1))/2 y3 = x3(y2-y1)/(x2-x1) + ((y2+y1)-(x2+x1)(y2-y1)/(y2-y1))/2 Remember all that great Grade 10 algebra? best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "chris" wrote in message oups.com... I'm generally quite happy with how excel graphs the moderately non-linear x-y data I supply it - the curves pass through all the data points with a pleasing fit. I'd just like to be able to get excel to interpolate on the graph for me. For instance, the curve passes through the points (x1, y1) and (x2, y2) which I supplied. How do I get excel to calculate and supply the value y3 from the point (x3, y3) on the curve it generated when I specify the value of x3? |
#5
|
|||
|
|||
interpolating non-linear curves in excel graphs
Thanks Bernard, but the problem is that my data points are not in a
line. That's what I meant by "non-linear" data. |
#6
|
|||
|
|||
interpolating non-linear curves in excel graphs
chris -
Please describe the method you are currently using so that "the curves pass through all the data points with a pleasing fit." The appropriate interpolation depends on whether you are using the Smoothed Line option (Format Data Series | Pattern | Line) or an Add Trendline type (Log, Polynomial, Power, or Exponential). - Mike www.mikemiddleton.com "" wrote in message oups.com... I'm generally quite happy with how excel graphs the moderately non-linear x-y data I supply it - the curves pass through all the data points with a pleasing fit. I'd just like to be able to get excel to interpolate on the graph for me. For instance, the curve passes through the points (x1, y1) and (x2, y2) which I supplied. How do I get excel to calculate and supply the value y3 from the point (x3, y3) on the curve it generated when I specify the value of x3? |
#7
|
|||
|
|||
interpolating non-linear curves in excel graphs
Hello Martin,
Thanks for your reply. Yes, I'm talking about the curve that excel draws to pass through the x,y points in my scatter plot. My problem is that I can't use any linear techniques like TREND because my curve is just that - a curve, not a straight line. The realtionship between the x,y pairs is exponential ( y = x^n), but the value of n varies (smoothly) based on the value of x. It might be something like y = x ^ (1.4 + .2x). I would like to be able to input a set of x,y pairs, gain access to to the curve that excel so expertly draws through these points, submit a new value of x to the curve and read off the associated y. Any ideas? Best Regards, Chris |
#8
|
|||
|
|||
interpolating non-linear curves in excel graphs
Hello Mike,
I'm not using any of the methods you mention. I enter the x's in one column and in the next column, the y's associated with each x. I invoke a simple scatter plot using a standard chart, and grab the data range I just entered. Then I hit "finish". Excel draws a smooth curve that passes thorugh each point. I was unable to find the "Smoothed Line option (Format Data Series | Pattern | Line)". How do I access it? I'm using excel 2003. Thank you for pointing out the that trendlines can be power law or polynomial, though. And wow! Excel will even print the equation it uses to match the curve. Except... It's wrong! I used y = x^1.5 and selected a "power" trendline. The trendline was drawn perfectly, but the printed equation was y = x^2. Doesn't seem to like decimal points??? If I could only call the curve it generates (either directly from the data or as a trendline) as a function! Regards, Chris |
#9
|
|||
|
|||
interpolating non-linear curves in excel graphs
The chart smoother appears to fit Bezier curves
http://www.xlrotor.com/Smooth_curve_...ample_file.zip which in most instances is not greatly different than cubic splines http://groups.google.com/group/micro...2966520eccdb1f Jerry "chris" wrote: I'm generally quite happy with how excel graphs the moderately non-linear x-y data I supply it - the curves pass through all the data points with a pleasing fit. I'd just like to be able to get excel to interpolate on the graph for me. For instance, the curve passes through the points (x1, y1) and (x2, y2) which I supplied. How do I get excel to calculate and supply the value y3 from the point (x3, y3) on the curve it generated when I specify the value of x3? |
#10
|
|||
|
|||
interpolating non-linear curves in excel graphs
Chris -
In general, lines connecting points of an XY (Scatter) chart will appear smooth only if you use a very large number of points or if you choose the Smoothed Line option or if you use Add Trendline. To get a smoothed line, after you create the chart, select the data series by clicking on one of the points, and choose Format | Selected Data Series | Patterns | Line. To show more significant digits after you add a trendline, select the text-box-like object containing the fitted equation, and click the Increase Decimal button repeatedly. There are worksheet-function equivalents for each of the Add Trendline functions. Tushar Mehta has some explanations at http://www.tushar-mehta.com/excel/ti...efficients.htm For interpolation of the Smoothed Line option, see the links provided by Jerry W. Lewis. - Mike www.mikemiddleton.com "chris" wrote in message oups.com... Hello Mike, I'm not using any of the methods you mention. I enter the x's in one column and in the next column, the y's associated with each x. I invoke a simple scatter plot using a standard chart, and grab the data range I just entered. Then I hit "finish". Excel draws a smooth curve that passes thorugh each point. I was unable to find the "Smoothed Line option (Format Data Series | Pattern | Line)". How do I access it? I'm using excel 2003. Thank you for pointing out the that trendlines can be power law or polynomial, though. And wow! Excel will even print the equation it uses to match the curve. Except... It's wrong! I used y = x^1.5 and selected a "power" trendline. The trendline was drawn perfectly, but the printed equation was y = x^2. Doesn't seem to like decimal points??? If I could only call the curve it generates (either directly from the data or as a trendline) as a function! Regards, Chris |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Are there better GRAPHS within EXCEL or Add-on graphs? | Deb | Charts and Charting | 1 | February 1st, 2006 01:19 PM |
Decimal pt changes on linked graphs | Greg A | Powerpoint | 4 | January 4th, 2006 04:06 PM |
Changing Imported Excel Graphs to Pictures in PPT | Spyder | Powerpoint | 2 | December 22nd, 2005 01:24 AM |
Excel Range Value issue (Excel 97 Vs Excel 2003) | Keeno | General Discussion | 2 | June 13th, 2005 02:01 PM |
WHY | General Discussion | 9 | December 16th, 2004 12:49 AM |