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/community/en-us/default.mspx?mid=9cc27fb5-15fe-4983-a861-241f2ade5f88&dg=microsoft.public.excel.crashesgpfs
|