INTERCEPT in Excel: Fitted Values at Zero and Changed Measurement Origins

• Introduced: first introduction version is not established by the reviewed official history; the current product list is not a launch record.
• Availability: official help lists Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel 2024, Excel 2024 for Mac, Excel 2021, Excel 2021 for Mac, Excel 2019, Excel 2016.
• Checked: October 14, 2026; official INTERCEPT reference. Documentation and independent models; no native Excel execution or wider platform certification.

=INTERCEPT(known_ys,known_xs)

Both series are required, with Y first. Microsoft’s equation is a=mean_y-b*mean_x, where b is the fitted SLOPE.

The reference ignores text, logical values and empty cells inside arrays or references and includes zero. It documents #N/A for no data or unequal data-point counts. It also documents #DIV/0! for its underdetermined example with all X values 1 and all Y values 0, distinguishing the algorithm from LINEST.

The worked tables below contain complete numeric pairs. They do not establish all mixed-type pairing or native numerical-stability rules.

Enter X 1,2,3,4 in A2:A5 and Y 2,2,5,7 in B2:B5:

=INTERCEPT(B2:B5,A2:A5)

Expected result: -0.5. The means are 2.5 and 4. Centered products total 9 and centered X squares total 5, giving slope 1.8. Therefore the intercept is 4-1.8*2.5=-0.5.

No input Y value is negative, yet the fitted line crosses the Y axis below zero. This is not automatically a calculation error: X=0 lies outside the observed interval 1 through 4. Whether the extrapolated point makes sense depends on the quantity being modeled.

Put this in C2 and fill through C5:

=INTERCEPT($B$2:$B$5,$A$2:$A$5)+SLOPE($B$2:$B$5,$A$2:$A$5)*A2

Expected fitted Y values: 1.3,3.1,4.9,6.7. In D2 enter =B2-C2 and fill down. Expected residuals: 0.7,-1.1,0.1,0.3.

Their sum is 0 in the exact independent model, while the squared-residual total is 1.8. Zero residual sum does not mean every observation is predicted perfectly; positive and negative residuals can cancel.

Create X-prime values with =A2-2.5, filled down. They are -1.5,-0.5,0.5,1.5, with mean zero.

=INTERCEPT(B2:B5,E2:E5)

Assuming the transformed X values are in E2:E5, expected intercept is now 4, while the slope remains 1.8. In the transformed coordinates, X-prime=0 corresponds to the original X=2.5, the mean of the observed X data.

The fitted points are unchanged. The intercept changed because the meaning of zero changed. This is particularly important when comparing models that encode time or another scale from different starting points.

Adding 100 to every original Y produces 102,102,105,107. Its intercept becomes 99.5 and its slope stays 1.8.

Multiplying original Y by ten instead produces 20,20,50,70. Both coefficients scale: intercept -5 and slope 18. Label the units and origin before comparing coefficients across reports.

A separate perfect-line control, X 1,2,3,4 with Y 5,8,11,14, gives intercept 2 and slope 3. It provides a simple way to check that the Y and X arguments have not been reversed.

FORECAST.LINEAR evaluates the fitted line at an explicitly chosen X; its output at X=0 matches this intercept in the numeric model. Neither function supplies a prediction interval or confirms that extrapolation is appropriate.

A constant-X dataset does not provide enough independent X variation to determine a unique slope and intercept. Do not hide that condition behind a fallback coefficient of zero. The source’s documented errors and algorithm warning should remain visible when diagnosing a workbook, while unverified error combinations should be tested in the actual target build.

• Microsoft INTERCEPT reference.
• Editorial testing standards.

Expected outputs are independent mathematical calculations or explicitly documented rules, not captured native Excel results. Examples use fictional data, English function names and comma separators. Native floating-point boundaries remain untested.