Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
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
Posted to microsoft.public.excel.charting
|
|||
|
|||
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
Posted to microsoft.public.excel.charting
|
|||
|
|||
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
Posted to microsoft.public.excel.charting
|
|||
|
|||
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
Posted to microsoft.public.excel.charting
|
|||
|
|||
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
Posted to microsoft.public.excel.charting
|
|||
|
|||
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
Posted to microsoft.public.excel.charting
|
|||
|
|||
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
Posted to microsoft.public.excel.charting
|
|||
|
|||
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
Posted to microsoft.public.excel.charting
|
|||
|
|||
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
Posted to microsoft.public.excel.charting
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple Excel versions. | Excel Discussion (Misc queries) | |||
Excel Linear Regression by set slope (not intercept) | Excel Worksheet Functions | |||
How do I insert standard deviation to my graphs in excel? | Excel Discussion (Misc queries) | |||
TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER | New Users to Excel | |||
Excel graphs with 1,000 or more points should not auto label | Charts and Charting in Excel |