Getting values from a trend line

[Guest]

I've got a line with 52 data points for which I've put in a Polynomial Trend
Line degree 6 to provide a smooth profile.

The next stage that I want to do is to work out what the values are of the
Trend Line. Currently I display the equation on the Chart, copy it, paste
into a cell and adjust the format accordingly. Very long-winded, and the
process needs to be re-done every time the data is updated.

There must be an easier way. Something that involves a direct link and no
copy and pasting. Any ideas?

 

[Guest]

Hi Andrew,

Let's suppose that the x- and y- ranges are in A2:A53 and B2:B53 respectively.
Select a 7 column x 1 row area, enter the following formula, and confirm
with CTRL-SHIFT-ENTER.

=LINEST(B2:B53,A2:A53^{0,1,2,3,4,5,6},0,)

The formula will return the 7 coefficients, starting from the zeroth order
and ending with the 6th order.

If you want to have the coefficients shown starting from the 6th order and
ending in the zeroth order, modify the formula as,

=LINEST(B2:B53,A2:A53^{6,5,4,3,2,1,0},0,)

Regards,
B. R. Ramachandran

 

[Guest]

That's excellent - will save me loads of time. This has annoyed me for years
and finally I have a fix! Much appreciated!!

 

[Guest]

While it is certainly more convenient, it is potentially much less accurate
unless you are using Excel 2003. Prior to 2003, LINEST used a mathematically
exact, but numerically poor implementation. The potential for numerical
inaccuracy increases with the degree of the polynomial.

If you are using a pre-2003 version, a wise practice would be to compare the
LINEST coefficients to those of the chart trendline (from an "XY (Scatter)"
chart). If there is any meaningful difference, the chart coefficients whould
be more accurate.

If you are using 2003 (Windows) or 2004 (Mac), then do the same test if
LINEST produces any zero coefficients.

Jerry