Article ID: 828234  View products that this article applies to. On This PageSUMMARYThis article discusses the INTERCEPT function in Microsoft Excel, illustrates how to use the function, and compares its results for Excel 2003 and for later versions of Excel with its results in earlier versions of Excel. MORE INFORMATIONThe INTERCEPT(known_y's,known_x's) function returns the INTERCEPT of the linear regression line that is used to predict y values from x values. Syntax
Example of usageTo illustrate the INTERCEPT function, create a blank Excel worksheet, copy the following table, select cell A1 in your blank Excel worksheet, and then paste the entries so that the following table fills cells A1:D13 in your worksheet.Collapse this table
Cells A2:A7 and B2:B7 contain the yvalues and xvalues that call INTERCEPT in cell A10. 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(known_y's, known_x's) is the result of evaluating AVERAGE(known_y's) – SLOPE(known_y's, known_x's) * AVERAGE(known_x’s). While the code for INTERCEPT has not been directly changed for Excel 2003 and for later versions of Excel, the behavior of INTERCEPT is improved because of improved code for SLOPE. 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 x,y pairs with x on the horizontal axis and y on the vertical axis, and then add a positive constant to each x value, data just shifts to the right. The bestfit regression line still has the same slope. However, the shifted data has a different intercept. 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 known_x's. Cell A11 shows AVERAGE(known_y's) – 0.775280899 * AVERAGE(known_x's). Because SLOPE should not change, and 0.775280899 is the value of SLOPE when D3 = 0, values of this expression in A11 should also agree with the values in cells A9 and A10.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 known_x's should not affect SLOPE. 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 roundoff 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 roundoff errors that you obtain with the earlier versions of Excel. Results in earlier versions of ExcelThe article about SLOPE describes the less numerically robust formula that earlier versions use. The formula requires only one pass through the data. Only the shortcomings of SLOPE in these versions cause INTERCEPT to give roundoff errors in the extreme cases.Results in Excel 2003 and in later versions of ExcelExcel 2003 and later versions of Excel uses an improved procedure to calculate SLOPE. As a result, INTERCEPT's performance improves. The improved procedure requires two passes through the data. Again, the following article about SLOPE describes the improvement.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
(http://support.microsoft.com/kb/828142/
)
Excel statistical functions: SLOPE
ConclusionsBecause Excel 2003 and later versions of Excel replace a onepass approach with a twopass approach, the numeric performance of SLOPE in Excel 2003 and in later versions of Excel is better than in earlier versions of Excel. Therefore, the numeric performance of INTERCEPT is better. Results in Excel 2003 and in later versions of Excel will never be less accurate than the results in earlier versions of Excel.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:
PropertiesArticle ID: 828234  Last Review: January 13, 2007  Revision: 2.2
