interpolating non-linear curves in excel graphs

[chris]

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?

 

[Bernard Liengme]

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

 

[MartinW]

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

 

[chris]

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

 

[chris]

Thanks Bernard, but the problem is that my data points are not in a
line. That's what I meant by "non-linear" data.

 

[Mike Middleton]

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

 

[chris]

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

 

[chris]

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

 

[Guest]

The chart smoother appears to fit Bezier curves

http://www.xlrotor.com/Smooth_curve_bezier_example_file.zip

which in most instances is not greatly different than cubic splines

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

Jerry

 

[Mike Middleton]

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/tips/trendline_coefficients.htm

For interpolation of the Smoothed Line option, see the links provided by
Jerry W. Lewis.

- Mike
www.mikemiddleton.com