Trend line calculations

[Edge]

Hi there,

i have created a simple x,y scatter graph with a trend line. The trend
line has a formula attached to it, some thing in the form of y=100x-50
What Im trying to do is to take that formula and use it in the work
sheet to enter different values of x to get the y values.

It cant just be typed in to a box as the data in the chart will change
and therefore the trend line formula will change accordingly.

Does any one know how to have a copy of the current trend line formula
to use in the worksheet ???

Thanks,

michael

 

[Jerry W. Lewis]

I'm not sure that I understand your question. If you add new data to
the range that you are plotting it will change the chart and therefore
potentially change the fitted equation displayed on the chart. However
you can add predicted values in cells that are not selected to plot on
the chart without impacting the chart.

When copying the fitted trendline equation from a chart, you have to be
careful. By default Excel displays heavily rounded coefficients to make
a relatively simple looking formula. Usually these rounded coefficients
are useless for actual calculation. Right click on the equation and
format in scientific notation with 14 decimal places.

Alternately, David Braden has posted VBA code to extract the
coefficients directly from the chart into cells

http://groups.google.com/[email protected]

Also you could directly calculate the trendline coefficients in the
worksheet using LINEST(), or get predicted values using TREND().

The advantage of using the chart coefficients (either manually or via
Braden's code) is that LINEST and TREND (prior to Excel 2003) use a
numerically poor algorithm that can give inaccurate results with some
data sets. The chart trendline (extracted by Braden's code) is much
better numerically.

Jerry

 

[Jon Peltier]

Michael -

Add the new points in a new series, so the trendline in the first series
is unaffected.

- Jon