The LINEST function returns incorrect results in Excel (277585)


The information in this article applies to:

  • Microsoft Office Excel 2003
  • Microsoft Excel 2002
  • Microsoft Excel 2000
  • Microsoft Excel 97 for Windows
  • Microsoft Excel 2004 for Mac
  • Microsoft Excel X for Mac
  • Microsoft Excel 2001 for Mac
  • Microsoft Excel 98 Macintosh Edition
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

Modification Type:MinorLast Reviewed:7/27/2006
Keywords:kbpending kbprb KB277585
©2004 Microsoft Corporation. All rights reserved.