XL2000: LINEST() Worksheet Function Returns Invalid Values

When you use the LINEST() worksheet function in a formula, the formula may return invalid, mathematically impossible numbers, such as a negative sum of squares.

This issue can occur if some of the values in the Y and X ranges are zero. For an example of how this issue can occur, see the "More Information" section of this article.

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

Example of How the Issue Can Occur

1. You start Excel.
2. In cell A1, you type 0.
3. Select cell A1, you grab the fill handle, press CTRL, and then fill to cell A20.
4. In cell B1, you type 1.
5. Select cell B1, you grab the fill handle, press CTRL, and then fill to cell B20.
6. You select cells D1:E5.
7. In the formula bar, you type the following formula:
   =LINEST(A1:A20,B1:B20,FALSE,TRUE)
8. You press CTRL+SHIFT+ENTER to enter the formula as an array.
   
   The formula returns the following valid results:

      D1: 0.926829    E1: 0
      D2: 0.009219    E2: #N/A
      D3: 0.993031    E3: 0.493855
      D4: 2707.5      E4: 19
      D5: 660.3659    E5: 4.634146
   					

9. You change the value in cell A17 to 0 (zero), the value in cell A20 to 0 (zero), and the value in cell B19 to 0 (zero).
   
   The formula now returns the following results:

      D1: 0.66401     E1: 0
      D2: 0.125159    E2: #N/A
      D3: -0.14578    E3: 6.269223
      D4: -2.41736    E4: 19
      D5: -95.0101    E5: 746.7601
   					

   By definition the R^2 value in cell D3 must be between zero and 1, so the returned value is invalid. D4 is the F value, which must be a positive number, and D5 is a sum of squares, which must also be a positive value. The returned values in D3:D5 are all invalid numbers.

For more information about the LINEST() worksheet function, click Microsoft Excel Help on the Help menu, type linest in the Office Assistant or the Answer Wizard, and then click Search to view the topic.