Excel: LINEST() Returns Negative r^2 Value

The information in this article applies to:

• Microsoft Excel for Windows, versions 4.0, 4.0a, 5.0
• Microsoft Excel for the Macintosh, versions 4.0, 5.0

SUMMARY

In 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 INFORMATION

The 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/SST

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