Microsoft KB Archive/215559

= XL2000: LINEST Worksheet Function Returns Invalid Values =

Article ID: 215559

Article Last Modified on 10/8/2003

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q215559



SYMPTOMS
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.



CAUSE
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.



STATUS
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
 You start Excel. In cell A1, you type 0 . Select cell A1, you grab the fill handle, press CTRL, and then fill to cell A20. In cell B1, you type 1 . Select cell B1, you grab the fill handle, press CTRL, and then fill to cell B20. You select cells D1:E5.</li> In the formula bar, you type the following formula:

=LINEST(A1:A20,B1:B20,FALSE,TRUE)

</li>  You press CTRL+SHIFT+ENTER to enter the formula as an array.

The formula returns the following valid results: <pre class="fixed_text">  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 </li>  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: <pre class="fixed_text">  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. </li></ol>

<div class="references_section">