INTERCEPT

What it does

INTERCEPT(known_ys, known_xs)

INTERCEPT(known_y's, known_x's) returns b in the ordinary least-squares line

y = a·x + b

fitted to the paired sample (x₁, y₁) … (xₙ, yₙ).

The least-squares problem is to minimise the sum of squared vertical residuals

S(a, b) = Σᵢ ( yᵢ − a·xᵢ − b )²

Setting ∂S/∂a = ∂S/∂b = 0 gives the normal equations, whose solution is

a = Σᵢ (xᵢ − x̄)(yᵢ − ȳ) / Σᵢ (xᵢ − x̄)²
b = ȳ − a·x̄

with x̄ and ȳ the arithmetic means. INTERCEPT returns b; SLOPE returns a.

Two structural facts follow directly from that pair of formulas and are worth stating, because both are load-bearing for the rest of the page:

1. The fitted line passes through the centroid (x̄, ȳ). The second formula is that statement rearranged. b is therefore not an independent quantity: it is ȳ shifted by the slope times the horizontal distance from the origin to the centroid.
2. b is an extrapolation to x = 0. When the x data sit far from zero — years like 2024, temperatures in kelvin, prices in cents — the intercept is the value of the fitted line at a point that is nowhere near the data. That is a statement about the estimand, not about any implementation, and it is the root of the conditioning problem discussed under Numerical notes.

Domain. Defined for n ≥ 2 paired points with Σ(xᵢ − x̄)² > 0, i.e. the x values are not all equal. The vertical-line case has no least-squares line of the form y = ax + b and the denominator above is exactly zero. Range: any real number.

The regression is asymmetric in its arguments: INTERCEPT(y, x) and INTERCEPT(x, y) fit different lines, because the criterion minimises vertical, not perpendicular, distance. The first argument is the response.

Arguments

The two arguments must contain the same number of values; pairing is positional. Both slots are array/reference slots rather than scalar slots, so the shared range-scan policy applies to values reached inside them — see Coercion and lifting. Whether a value that is skipped in one argument causes the corresponding value in the other to be skipped as well — pairwise deletion — is a real question for any regression surface and is on the probe list below.

Try it

Quirks

Result and edge cases

Returns Number.

• Two points. With n = 2 and distinct x, the fit is exact: the line through the two points, and the residual sum of squares is zero.
• A single point. Degenerate for the same reason: one point has zero x-variance.
• Error values in either argument propagate.
• Perfectly collinear data is not an edge case at all; it is the easy case.

Errors

Anyone relying on the exact error code for the mismatched-length case should read Microsoft's page directly. Nobody has checked any of these against Excel within the Handbook's record.

How to implement

Numerical notes

INTERCEPT is the arithmetically trivial member of a numerically delicate family. There are two distinct hazards and they are usually confused with each other.

1. Cancellation in the centred sums. The textbook "computational" formulas

Sxy = Σ xᵢyᵢ − n·x̄·ȳ        Sxx = Σ xᵢ² − n·x̄²

are one-pass and catastrophically unstable: when the data are far from the origin, both terms are large and nearly equal, and the difference loses most of its significant digits. The classical remedy is the two-pass centred form Σ(xᵢ − x̄)(yᵢ − ȳ), or a numerically stable updating formula. The reference analysis is Chan, Golub and LeVeque, Algorithms for Computing the Sample Variance: Analysis and Recommendations (1983), and the error bounds are worked in Higham, Accuracy and Stability of Numerical Algorithms, chapter 1. The relevant data-dependent quantity is the coefficient of variation of the x sample: the naive formula loses roughly log₁₀(1 + x̄²/Sxx) decimal digits.

2. Leverage in the intercept itself. Even with perfectly computed centred sums, b is recovered as ȳ − a·x̄. If x̄ is large, an error δ in the slope becomes an error x̄·δ in the intercept, and the subtraction of two large nearly-equal quantities cancels again. This is why the intercept of a regression on calendar years is a far worse-conditioned number than the slope of the same regression, and why standard practice is to centre the predictor before fitting and add the shift back afterwards. INTERCEPT offers no way to do that; the caller must subtract a reference year from the x column themselves.

A careful implementation therefore: accumulates the centred sums in one stable pass or two passes rather than the one-pass difference form; forms the slope first and the intercept from the centroid identity; and — if it wants the last bits — accumulates the centred products in extended precision or with a compensated (Kahan/Neumaier) sum, since the sums are the only place error can enter. Beyond that there is nothing to get wrong: two divisions and a multiply-subtract.

The published critique literature on Excel's regression surfaces is worth naming here even though it targets LINEST rather than INTERCEPT: McCullough and Wilson's series On the accuracy of statistical procedures in Microsoft Excel used the NIST Statistical Reference Datasets (Longley, Filip, Wampler) precisely to expose the normal-equations conditioning problem, and Morten Welinder's work on Gnumeric's statistical functions documents the same class of defect from the reimplementer's side. Neither says anything about INTERCEPT specifically, and neither is a Handbook measurement.

Rust code

42 intercept "INTERCEPT" (2, 2) [R "known_ys", R "known_xs"] |s, a, sv| {
    out(s, paired::intercept_from_pairs(&pairs(s, a, 1, 0, PairScalars::SkipNonnumeric, sv)?))
}

pub fn intercept_from_pairs(pairs: &[(f64, f64)]) -> Result<f64, WorksheetErrorCode> {
    let slope = slope_from_pairs(pairs)?;
    let (mean_x, mean_y) = means(pairs)?;
    Ok(mean_y - slope * mean_x)
}

No implementation has been published with a Handbook test suite for this function.

Version differences

The recorded environments below distinguish a reading from a same-input comparison. Counts describe captured inputs in each environment; overlapping captures and comparisons are not totals of unique worksheet calls.

The record contains no same-input comparison between these environments. Reading different inputs on different builds does not establish either a version change or cross-build agreement.

Recorded environments and comparison details.

Alternatives

Relationships

• SLOPE — the other half of the same fit. The two are computed from the same three centred sums, and evidence record EV-MISC-0008 names them together for exactly that reason.
• FORECAST.LINEAR and the legacy FORECAST — the fitted line evaluated at a chosen x. The identity INTERCEPT(y, x) = FORECAST(0, y, x) holds mathematically; whether the two surfaces return identical bits is a separate question that requires evidence, and the Handbook has none.
• LINEST — the general form. For a single predictor, LINEST returns the slope and the intercept together as a two-cell array, with the intercept in the rightmost cell. INTERCEPT is the scalar convenience wrapper.
• RSQ, PEARSON, STEYX — the goodness-of-fit companions built from the same centred sums.
• Confused with: TREND, which returns fitted values rather than coefficients, and with the intercept of a chart trendline, which Excel computes on the chart's own plotted series and can therefore disagree with a worksheet INTERCEPT over a different range.

Named variants

No named variants are recorded yet.