LINEST vs. Trendline (on graph)

[espevak]

Has anyone seen this before? If so, how do I work around it?

I've been quite happy using both linest and the trendline functions. However, I've found a very odd behavior when forcing the intercept to zero.

I'm currently fitting a second order polynomial (with and without the zero intercept). Here is what I'm currently seeing when using an intercept of zero (FALSE for "const" in LINEST):

Excel 2007:
The second order term in the trendline equation is incorrect -- it's the same coefficient as the non-zero intercept case -- though LINEST appears to come up with the proper coefficient. However, LINEST comes up with the wrong R^2 term (it shows a higher R^2 with a forced zero intercept).

(A side issue with Excel 2007 seems to be when showing the equation for a zero intercept case, the second order coefficient will often disappear which I suppose isn't so bad since that coefficient is incorrect anyway...)

Excel 2003:
The trendline coefficient on the graph are correct but the LINEST R^2 is still incorrect.

Thank you.

[DougJ]

Using XL 2007 I found that the trend line coefficients and Linest values were the same, both with the Y intercept set to 0 and calculated. Could you post some data where you got different results?

I did find that the R^2 value was different in Linest and the trend line when the intercept was set to zero though, and for Linest the R^2 increased when the intercept was 0. I'm not a statistician, and I don't have time at the moment to investigate this further at the moment, but I would be interested to hear comments from others.

[DougJ]

Further to my previous post, checking the R^2 values using the definition given here:

http://en.wikipedia.org/wiki/Coeffic..._determination

it seems that the trend line value is correct (or at least agrees with the Wikipedia definition), and the Linest value is different when the intercept with the Y axis is set to zero.

Any statisticians out there who can comment on what is going on?

[DougJ]

I think I have found the answer.

This site:

http://www.curvefit.com/linear_regression.htm

gives a nice easy to understand explanation of how R^2 is calculated and why.

It has this to say about calculating R^2 for a line constrained to pass through the origin:

"Why Prism doesn't report r2 in constrained linear regression

Prism does not report r2 when you force the line through the origin (or any other point), because the calculations would be ambiguous. There are two ways to compute r2 when the regression line is constrained. As you saw in the previous section, r2 is computed by comparing the sum-of-squares from the regression line with the sum-of-squares from a model defined by the null hypothesis. With constrained regression, there are two possible null hypotheses. One is a horizontal line through the mean of all Y values. But this line doesn't follow the constraint -- it does not go through the origin. The other null hypothesis would be a horizontal line through the origin, far from most of the data.

Because r2 is ambiguous in constrained linear regression, Prism doesn't report it. If you really want to know a value for r2, use nonlinear regression to fit your data to the equation Y=slope*X. Prism will report r2 defined the first way (comparing regression sum-of-squares to the sum-of-squares from a horizontal line at the mean Y value)."

Now it seems that the Excel (and Gnumeric) Linest() function uses the second hypothesis for a constrained regression line, resulting in a higher R^2 value, compared with the unconstrained line. The chart trend line function on the other hand seems to use the first hypothesis.

So both results are valid.

It would have been nice if Microsoft could have explained that.

The remaining question is why espevak was getting incorrect coefficients from the chart trendline. I can't reproduce that behaviour, so I can't comment.

[lorin]

Thanks for posting the observation. I can confirm what DougJ saw; in Excel 2007 I am getting the same equation coefficients between LINEST and Trendline for a 2nd-order polynomial forced through zero, but the correlation coefficients are different.

Microsoft claimed to have fixed some LINEST problems (including incorrect correlation coefficients) in Excel 2003 and later versions: see http://support.microsoft.com/kb/828533
; but perhaps they didn't spot this remaining discrepancy between Trendline and LINEST.

I also don't know what to make of the statement at the Microsoft site that says "In Excel 2003 and in later versions of Excel, these problems have been addressed, except for non-linear regression, caused by an issue with the Solver add-in instead of with the statistical functions or the Analysis ToolPak."

Does this mean that my 2nd-order polynomial fit from Excel may not be correct after all?

[Sandy04]

Help. All my .xlsx workbooks have suddenly become Gnumeric. If I open a file using MS Excel 2007 and re-save it, it is saved as a gnumeric file. Even old files I haven't looked at, have become gnumeric. What can I do?

[DougJ]

Sandy - posting your question in a more appropriate thread would probably be a good start!

Try checking the File Save as option under Office Blob - Excel Options - Save.

If this has been set to Gnumeric, reset it to whichever Excel version you want.

One other thought, have you recently installed Gnumeric? If so it is possible that the file assocoation for XL files has been set to gnumeric, but the files are still Excel files. Have you tried opening them in Excel?