Only the shortcomings of SLOPE in these versions cause INTERCEPT to give round-off errors in the extreme cases. The formula requires only one pass through the data. The article about SLOPE describes the less numerically robust formula that earlier versions use. However, cells D7:D13 show the round-off errors that you obtain with the earlier versions of Excel. 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. Still, this experiment shows that the cases where the errors occur are extreme. Round off errors have become so severe that division by 0 occurs when D3 = 8.Įarlier 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. These pairs of values appear for the cases where D3 = 7.5 and 8 respectively. 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.ĭ7:D13 show the values that INTERCEPT returns and the values that INTERCEPT should have returned if SLOPE had not changed. As a result, the values in cells A11 (that agree with A10) and A12 differ. But if you try 7.25, 7.5, 7.75, and 8, the SLOPE in A9 changes. If D3 <= 7, then there are no round off errors that appear in the first 6 decimal places of SLOPE. If you increase the value in D3, you add a larger constant to B2:B7. 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. SLOPE should not vary as you add different positive constants to the known_x's. Values in cells A9 and A10 always agree because the value in A10 is exactly what INTERCEPT returns. Cell A10 shows the value of INTERCEPT, and cell A11 shows the value of the expression that is evaluated when calculating INTERCEPT:ĪVERAGE( known_y's) – SLOPE( known_y's, known_x's) * AVERAGE( known_x's) With the default value of 0 in D3, SLOPE in A9 is 0.775280899. However, the shifted data has a different intercept. The best-fit regression line still has the same 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. Adding a positive constant to each of the observations in B2:B7 should not affect the value of 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.
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. 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). Excel 2003 and later versions of Excel improve the behavior of INTERCEPT. In versions of Excel that are earlier than Excel 2003, INTERCEPT can exhibit round off errors. You may want to format cells B2:B7 as Number with 0 decimal places and cells A9:D13 as Number with 6 decimal places.Ĭells A2:A7 and B2:B7 contain the y-values and x-values that call INTERCEPT in cell A10.
This 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. For more information about this change, read this blog post. Office 365 ProPlus is being renamed to Microsoft 365 Apps for enterprise.