Determining error associated with polynomial trend lines.

[BJ Richter]

I have a plot that requires a polynomial trend line to the fourth power that
has error associated with the y-axis values only. From this point on, I'm
relying on the equation displayed by excel to further manipulate that data,
which I have done and double checked with excel and it's corrrect. I'm
having difficulty, however, in continuing the calculations of associated
error. Is Excel capable of outputting the error associated with each
coefficient of the generated trend line equation? If it is possible, how do
you do it?

- BJR

 

[Jerry W. Lewis]

See Help for the LINEST function. You might also find
http://www.stfx.ca/people/bliengme/ExcelTips/Polynomial.htm
to be helpful.

For Excel versions prior to 2003, if the LINEST coefficients do not agree
with the coefficients from an "XY (Scatter)" chart trendline, then the
calculations are too difficult for the old LINEST. For Excel 2003, LINEST
and and an "XY (Scatter)" chart trendline coefficients should agree.

In 2003, LINEST can incorretly return a zero for some coefficients, but I
don't believe that can occur with polynomials. When it does occur, the
standard errors output by LINEST are still correct, even though the
coefficient is incorrectly zeroed.

Jerry