How are Trend Lines calculated in Excel

[Guest]

It appears that the coefficients calculated in Excel's charts and its TREND()
function are not least squares. In other words, the coefficients are not the
traditional solution to the least squares equation:

INV(X'X)(X'Y)

You can see this by calculating a regression using the analysis toolpack.

Can someone explain what excel is calcuating when you ask for a linear trend
line?

best

 

[Stephane Rodriguez]

You can show the equation that Excel is using by simply selecting the
"Display Equation" flag in the Trendline options.
If you take a standard linear regression, you should be able to see the
difference between your y=ax+b model and Excel's.

Hope this helps,

xlsgen - native Excel generator http://xlsgen.arstdesign.com
xlsgen RSS feed : http://www.arstdesign.com/BBS/rssxlsgen.php