Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

The LINEST function returns incorrect results in Excel


View products that this article applies to.

Symptoms

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

↑ Back to the top


Cause

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

↑ Back to the top


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

↑ Back to the top


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

↑ Back to the top


Keywords: KB277585, kbprb, kbpending

↑ Back to the top

Article Info
Article ID : 277585
Revision : 5
Created on : 1/29/2007
Published on : 1/29/2007
Exists online : False
Views : 38