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

## 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

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

## Applies to:

↑ 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 : 439