Microsoft KB Archive/277585

= The LINEST function returns incorrect results in Excel =

Article ID: 277585

Article Last Modified on 1/29/2007

-

APPLIES TO


 * Microsoft Office Excel 2003
 * Microsoft Excel 2002 Standard Edition
 * Microsoft Excel 2000 Standard Edition
 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel 2004 for Mac
 * Microsoft Excel X for Mac
 * Microsoft Excel 2001 for Mac
 * Microsoft Excel 98 for Macintosh

-



This article was previously published under Q277585



SYMPTOMS
When you use the LINEST statistical worksheet function in Microsoft Excel, the formula may return an incorrect result.



CAUSE
This behavior can occur if the formula refers to very large numbers that exceed 15 digits in length when summed, multiplied, or squared.



WORKAROUND
If you are seeking the slope of the line, one method to obtain a more accurate estimate of the slope is to subtract a constant from either the known x or the known y values.

For example, the following table contains very large y values and yields a line estimate of y = 0.4999998x + 3,000,000,000. In fact, the slope should actually be 0.50, yielding a line estimate of y = 0.50x - 3,000,000,000. You can double-check the slope value by computing the slope of the line with the following equation:

(y2 - y1) / (x2 - x1)

By using this method with the data in the following table, the equation is as follows:

(3000000002 - 3000000001) / (2 - 4) = 0.50

  X values                        Y values -      -      A1: 2                           B1: 3,000,000,001 A2: 4                          B2: 3,000,000,002 A3: 6                          B3: 3,000,000,003 A4: 8                          B4: 3,000,000,004 A5: 10                         B5: 3,000,000,005 A6: =LINEST(B1:B5, A1:A5)      B6: =LINEST(B1:B5, A1:A5) NOTE: The formula in cells A6:B6 is a single formula, entered as an array by pressing CTRL+SHIFT+ENTER.

By subtracting 3,000,000,001 from the numbers in B1:B5, you obtain a more accurate slope result: y = 0.50x -1   A1: 2                           B1: 0 A2: 4                          B2: 1 A3: 6                          B3: 2 A4: 8                          B4: 3 A5: 10                         B5: 4 A6: =LINEST(B1:B5, A1:A5)      B6: =LINEST(B1:B5, A1:A5) However, the Y-intercept must be adjusted (added back) by the same amount that was subtracted from the y values (column B), changing the final line equation to the following:

y = 0.50x + 300000000



MORE INFORMATION
For more information about Excel and statistical functions with large numbers, click the following article number to view the article in the Microsoft Knowledge Base:

158071 Problems with statistical functions and large numbers

Additional query words: XL2002 XL2000 XL2003 XL2001 XL98 XL97 XLX XL2004 prb

Keywords: kbpending kbprb KB277585

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.