REGRESSION: R Squared with "Constant is Zero"

**DreamWorks** · 05-31-2013, 10:03 AM

The "R Square" in Tools > Data Analysis > Regression with "Constant is Zero" ticked gives a very different answer to the creation of an XY (Scatter) chart with an added linear trendline displaying the R squared and with the "intercept at zero" selected -- which is correct? If the "constant is zero" and "intercept at zero" are both unchecked, then both approaches give the same answer as would be expected.

**shg** · 05-31-2013, 10:15 AM

It doesn't surprise me that they are different. There's an interesting paper on methods for regression through the origin ("RTO") at http://web.ist.utl.pt/~ist11038/comp...oughorigin.pdf

**MrShorty** · 05-31-2013, 10:30 AM

I don't know all of the details, but I know that one of the criticisms of Excel (especially prior to 2003) involved the algorithms for the LINEST()/regression functions. One of the big problems is that it calculated r^2 incorrectly, especially for regressions where the constant is forced to be 0. It's long and detailed, but this is one of MS's discussions of the issue http://support.microsoft.com/kb/828533

I know that a lot of hardcore statisticians say, "Friends don't let friends use Excel for statistics" and I believe this is part of the reason. Microsoft has not always been careful or thorough in how they program some of these statistical functions (maybe they are too busy designing "ribbons" and other fluff for Excel).

As noted in the link, MS made changes for 2003 (your profile indicates 2003), so the algorithms may be better than what I have. If the two regression tools are giving different results, then there is obviously still some kind of problem in Excel. I would invite further research into this. Perhaps further search of the kowledgebase will yield additional discussion that either validates the algorithms MS uses, and/or shows where they are still weak. If you need your calculation of r2 to be "rigorously defensible" (for a publication maybe), then I might suggest cross checking Excel's calculation with a dedicated statistics package.