Excel: LINEST() Returns Negative r^2 ValueLast reviewed: November 30, 1994Article ID: Q89472 |
The information in this article applies to:
SUMMARYIn Microsoft Excel, setting the CONST argument in the LINEST() function to FALSE can result in a negative value for r^2 (coefficient of determination). Setting CONST to FALSE in LINEST() forces the best-fit line through the origin which may result in a much greater margin of error. Using TRUE for the CONST argument results in a best-fit line computed solely on your data.
MORE INFORMATIONThe LINEST() function uses the "least squares" method to calculate a straight line that best fits your data. LINEST() also returns additional regression statistics including a coefficient of determination which indicates how useful the equation is in predicting y-values. The coefficient of determination (r^2) should be a value between 0 and 1 where 0 indicates the equation is not helpful and 1 indicates a perfect correlation between the estimated and actual y- values. If the CONST argument to LINEST() is FALSE then Microsoft Excel assumes a value of zero for b in the equation y=mx+b, that is, the line is forced through the origin. Forcing the line through the origin causes the predictions Microsoft Excel generates to be arbitrarily worse than average which can result in r^2 becoming negative. The coefficient of determination (r^2) is given by the formula:
r^2 = 1 - SSE/SSTWhere: SSE = The error sum of squares. SST = The total sum of squares. Forcing the best-fit line through the origin causes the estimates used in computing SSE to become arbitrarily large. As a result, the value SSE/SST may be greater than 1 causing the formula, 1 - SSE/SST, to become negative. In general, forcing a best-fit line through the origin will likely result in a greater margin of error and, hence, less useful statistics.
REFERENCES"Function Reference," version 4.0, pages 254-258
|
KBCategory: kbusage
© 1998 Microsoft Corporation. All rights reserved. Terms of Use. |