You are currently offline, waiting for your internet to reconnect

`INTERCEPT(known_y's,known_x's)`

y-values | x-values | ||

1 | = 3 + 10^$D$3 | Power of 10 to add to data | |

2 | =4 + 10^$D$3 | 0 | |

3 | =2 + 10^$D$3 | ||

4 | =5 + 10^$D$3 | ||

5 | =4+10^$D$3 | ||

6 | =7+10^$D$3 | Excel 2002 and earlier | |

when D3 = 7.5 | |||

=SLOPE(A2:A7,B2:B7) | -23717082.0762629 | ||

=INTERCEPT(A2:A7,B2:B7) | -24516534.4029667 | ||

= AVERAGE(A2:A7) - A9*AVERAGE(B2:B7) | when D3 = 8 | ||

=AVERAGE(A2:A7) - 0.775280899*AVERAGE(B2:B7) | #DIV/0! | ||

-77528089.6303371 |

- In Microsoft Office Excel 2007, click the
**Home**tab, click**Format**in the**Cells**group, and then click**AutoFit Column Widths**. - In Excel 2003, point to
**Column**on the**Format**menu, and then click**AutoFit Selection**.

Cells A2:A7 and B2:B7 contain the

In versions of Excel that are earlier than Excel 2003, INTERCEPT can exhibit round off errors. Excel 2003 and later versions of Excel improve the behavior of INTERCEPT. INTERCEPT(

If you have an earlier version of Excel, you can use the worksheet you created earlier to run an experiment to discover when round off errors occur. Adding a positive constant to each of the observations in B2:B7 should not affect the value of SLOPE. If you plot

With the default value of 0 in D3, SLOPE in A9 is 0.775280899. Cell A10 shows the value of INTERCEPT, and cell A11 shows the value of the expression that is evaluated when calculating INTERCEPT:

AVERAGE(`known_y's`) – SLOPE(`known_y's`, `known_x's`) * AVERAGE(`known_x's`)

Values in cells A9 and A10 always agree because the value in A10 is exactly what INTERCEPT returns. SLOPE should not vary as you add different positive constants to the If you increase the value in D3, you add a larger constant to B2:B7. If D3 <= 7, then there are no round off errors that appear in the first 6 decimal places of SLOPE. But if you try 7.25, 7.5, 7.75, and 8, the SLOPE in A9 changes. As a result, the values in cells A11 (that agree with A10) and A12 differ. However, values in A11 (or A10) and A12 should be the same because adding a constant to the

D7:D13 show the values that INTERCEPT returns and the values that INTERCEPT should have returned if SLOPE had not changed. These pairs of values appear for the cases where D3 = 7.5 and 8 respectively. Round off errors have become so severe that division by 0 occurs when D3 = 8.

Earlier versions of Excel give wrong answers in these cases because the effects of round-off errors are greater with the computational formula that these versions use. Still, this experiment shows that the cases where the errors occur are extreme.

If you have Excel 2003 or a later version of Excel, there is little or no difference between the common values in A10 and A11 and the value in A12 if you try the experiment. However, cells D7:D13 show the round-off errors that you obtain with the earlier versions of Excel.

For more information about the improvements in SLOPE for Excel 2003 and for later versions of Excel, click the following article number to view the article in the Microsoft Knowledge Base:

828142 Excel statistical functions: SLOPE

Typically, there is not a difference between the results in Excel 2003 and in later versions of Excel and the results in earlier versions of Excel because data does not frequently behave in the unusual way that this experiment illustrates. Numeric instability is most likely to appear in earlier versions of Excel when the data contains many significant digits and little variation between the data values.

The following procedure finds the sum of the squared deviations about a sample mean:

- Find the sample mean.
- Calculate each squared deviation.
- Sum the squared deviations.

- Find the sum of the squares of all the observations, the sample size, and the sum of all the observations.
- Calculate the sum of the squares of all the observations minus ((
`sum of all observations`)^2)/`sample size`).

XL2003 XL2007

Properties

Article ID: 828234 - Last Review: 01/13/2007 00:39:45 - Revision: 2.2

Microsoft Office Excel 2007, Microsoft Office Excel 2003

- kbexpertisebeginner kbinfo KB828234