How can I get the average of curves on a plot?

[jessie joe]

Hello!

I would like to get the average curve from several curves on a plot. Is
there a way to do this in Excel?

(Background infromation: I have, say, 10 sets of XY points and I want
to take the average of these to obtain a set of average X and Y points
and plot them. However, each of these 10 sets have a different amount
of XY points and I can't simply take the average across the row. Any
solutions?)

Thanks!

 

[Guest]

(A) If the X-values are the same for each series the Y-value of the average
is simply the average of the respective y-values.
(B) So, I assume yr X-values are not equal, though within a comparable
scale. You cld try to interpolate / extrapolate the xy-pairs to obtain a
kind of best-fit curve, with Add Trendline you can choose a polynomial fit
which gives you say 10 estimated curves. Now the one y'r looking for is the
average of Y-values belonging to that fit, identical as in (A).
HTH,
Henk

 

[jessie joe]

I am not sure I understand. How do I get the set of average X and
values from the 10 estimated curves

 

[MrShorty]

1) Fit each of the 10 data sets to a suitable curve (polynomial or
other). Use LINEST or chart trendlines.
2) You can then take an average of the 10 curves by selecting a value
for x, calculating the value for y at each of the 10 curves, then
average that set of y's. This will give you an average value for y at
a given x. Repeat for as many x's as needed.

 

[Guest]

If your data is noisy, then fitting a polynomial (as others suggested) can
smooth it before you average. However, if the data is not noisy and not from
a low order polynomial, you should be aware that a least squares fit need not
pass through any observed points.

If you really want to average what the chart displays, then 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

(local polynomial fits that do pass through the data points, as opposed to a
global polynomial fit that may not).

Jerry