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)
: 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