Polynomial regression - how does Excel do it? (under "Trendline")

[Guest]

Question on polynomial regression under "trendline" option in charts:

I drew a line chart with my data, and then I used the following option:

1. On the line chart, click the right button of the mouse and select "Add
Trendline".
2. Choose "Polynomial" in "Trend/Regression Type".

This gave me a polynomial regression line using my data set and the exact
equation applied.

What I would like to know is how Excel created that equation. None of my
other statistics software gave me better fit than this, so I'm trying to
understand what the logic behind it is so that I can apply it to the language.

Any advice will be appreciated.
Thanks!!

Best,
JH

 

[Bernard Liengme]

See also my website for how to use LINEST on a polynomial
best wishes

 

[Martin Brown]

Question on polynomial regression under "trendline" option in charts:

I drew a line chart with my data, and then I used the following option:

1. On the line chart, click the right button of the mouse and select "Add
Trendline".
2. Choose "Polynomial" in "Trend/Regression Type".

This gave me a polynomial regression line using my data set and the exact
equation applied.

Trendline does surprisingly well. I have seen it get good answers when
other fairly reputable statistical packages get the same wrong answer as
LINEST.

What I would like to know is how Excel created that equation. None of my
other statistics software gave me better fit than this, so I'm trying to
understand what the logic behind it is so that I can apply it to the language.

Any advice will be appreciated.

I suspect that they basically precondition the x variable to improve the
condition number of the matrix they invert to fit the polynomial.

If you have datasets {x0...xn} with values {y0...yn}

Naievely you could try to fit 1, x, x^2 ... x^6

but if x >> 1 x^6 may be very large and lead to an unstable
illconditioned matrix problem.

So instead make a linear transformation to [-1...+1]

xi' = (2xi - x0 - xn)/(xn-x0)

x0 -> -1 and xn -> 1

Fit using this and then recompute the polynomial coefficients in the
original variable after solving this linear transformed version.

ISTR this was good enough to closely approximate the trendline answers -
but I don't know of anywhere that documents their algorithm.

If this still isn't good enough then the next stage is to fit the model
using orthogonal Chebyshev polynomials rather than powers of x^n

Where Tn(x) = cos( n.acos(x) )

Then you have to work a lot harder to compute the polynomial in terms of
the original variables but the problem matrix is almost diagonalised.

Regards,
Martin Brown