Linest function has an error when constant =0

[Guest]

When LINEST is used with constant set to zero, the SSreg is wrong.
Accordingly, the coefficient of determination (R2) is incorrect. Note that
the sum of SSreg and SSE is the same regardless of model used. To calculate
the correct R2, use the following macro:

Function Coeff_det(yarray As Range, xarray As Range) As Variant
SST =
Application.WorksheetFunction.Index(Application.WorksheetFunction.LinEst(yarray, xarray, 1, 1), 5, 1) - _

Application.WorksheetFunction.Index(Application.WorksheetFunction.LinEst(yarray, xarray, 1, 1), 5, 2)
SSE =
Application.WorksheetFunction.Index(Application.WorksheetFunction.LinEst(yarray, xarray, 0, 1), 5, 2)
Coeff_det = (SST - SSE) / SST
End Function


----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...ade5f88&dg=microsoft.public.excel.crashesgpfs

 

[Guest]

This was fixed in Excel 2003. Your premise is wrong that SSreg+SSE does not
depend on whether an intercept is fit or not. If an intercept is fit, then
SSreg+SSE = DEVSQ(ydata) but if the intercept is forced to zero, then
SSreg+SSe = SUMSQ(ydata). NIST provides test data sets with certified
answers (including both SSreg and R2), consult
http://www.itl.nist.gov/div898/strd/lls/data/LINKS/DATA/NoInt1.dat
http://www.itl.nist.gov/div898/strd/lls/data/LINKS/DATA/NoInt2.dat

Jerry