Polynomial regression - how does Excel do it? (under "Trendline")
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 |
Polynomial regression - how does Excel do it? (under "Trendline")
See also my website for how to use LINEST on a polynomial
best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "JH" wrote in message ... 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 |
Polynomial regression - how does Excel do it? (under "Trendline")
JH wrote:
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 |
All times are GMT +1. The time now is 04:13 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com