Excel: LINEST() Returns Negative r^2 Value

Last reviewed: November 30, 1994
Article ID: Q89472

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

Where:

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
KBSubcategory:

Additional reference words: 5.00 4.00 4.00a 5.00


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 30, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.