FORECAST

What it does

FORECAST(x, known_ys, known_xs)

FORECAST(x, known_ys, known_xs) fits a straight line to the paired data by ordinary least squares and evaluates that line at x. Microsoft gives the model in exactly this form: the equation is a + bx, where a is the intercept and b the slope, and where the sample means are AVERAGE(known_x's) and AVERAGE(known_y's).

Written out, with n paired observations (xᵢ, yᵢ) and means x̄, ȳ:

b  =  Σ (xᵢ − x̄)(yᵢ − ȳ)  /  Σ (xᵢ − x̄)²
a  =  ȳ − b·x̄
FORECAST(x, …)  =  a + b·x

Three things are worth reading off that definition rather than assuming them.

It is a fit, not an interpolation. FORECAST does not pass through the data points and has no notion of "nearest observation". If the requested x coincides with an observed xᵢ, the answer is the fitted value at that abscissa, not yᵢ.

There is no extrapolation guard. The formula is evaluated at whatever x is given, inside or outside the observed range. The word "forecast" in the name is a statement about intent, not about a check the function performs.

The denominator is the sum of squared deviations of x. It vanishes exactly when every xᵢ is identical — a vertical scatter with no leverage — and that is the case Microsoft documents as #DIV/0!. It does not vanish when the ys are constant: a constant y with varying x is a perfectly well-posed fit with slope zero.

The quantity b is also, up to scaling, the covariance over the variance: b = cov(x, y)/var(x) with matching denominators, which is why SLOPE, COVARIANCE.P/COVARIANCE.S, and CORREL all compute pieces of the same sum and why an implementation that shares those pieces is tempting and dangerous — see Numerical notes.

Arguments

x is a scalar numeric slot; the two data arguments are vector-shaped and consumed by scanning rather than by elementwise lifting — this is not a lift kernel. Which values a scan admits and which it skips is family policy, not engine law; see Coercion and lifting.

The pairing is positional. Nothing checks that the two vectors are meaningfully paired; the only check documented is that they contain the same number of data points.

Try it

Quirks

Result and edge cases

Returns Number — a scalar, never an array.

• Two points is the smallest fit with a defined slope, and it reproduces the line through them exactly (subject to rounding).
• One point, or any input where all xᵢ coincide, has a zero denominator. Documented: #DIV/0!.
• Constant y, varying x is fine: slope 0, intercept ȳ.
• Length mismatch between the two vectors is documented as #N/A.
• Empty inputs are documented as #N/A too — and here the documentation and the reference engine part company. See Errors.
• Large common offsets in x (dates are the everyday example: five-digit serial numbers with a one-digit spread) are the classic accuracy hazard for this function and are discussed under Numerical notes.
• Arrays are consumed as data, not lifted. FORECAST never spills.

Errors

As documented by Microsoft on the VBA reference page for this method:

Errors inside the scanned ranges propagate under the shared coercion discipline.

How to implement

Numerical notes

Centered versus raw sums. The slope can be computed two ways that are algebraically identical and numerically nothing alike:

centered:  Σ(xᵢ − x̄)(yᵢ − ȳ) / Σ(xᵢ − x̄)²
raw:       (nΣxᵢyᵢ − ΣxᵢΣyᵢ) / (nΣxᵢ² − (Σxᵢ)²)

The raw form is the one in most textbooks and most first implementations, and it is a catastrophic-cancellation machine: when the xᵢ share a large offset — Excel date serials, timestamps, anything with five significant digits before the interesting ones start — nΣxᵢ² and (Σxᵢ)² are enormous and nearly equal, and their difference retains only the low-order digits that survived rounding. The relative error grows like the square of the ratio of the mean to the spread. This is the standard result and it is why the centered form, or a Welford-style updating formula, is the right choice. Björck's treatment of the normal equations is the reference; the same lesson governs VAR.S and STDEV.S.

The two-pass centered form needs the mean before the deviations, so it reads the data twice. That is a real cost only for very long ranges, and it buys a conditioning improvement that no amount of care in the raw form can recover.

What upstream identified for Excel. The evidence record attached to this page states a structural conclusion: Excel's FORECAST is not the LINEST pipeline but a centered plain-double kernel — forward sums to means, one fused loop accumulating both the covariance and the variance, and publication in the intercept form a + b·x. That identification is upstream's, it names its own corpus, and the corpus was built alongside the hypothesis rather than held out — the record says so in as many words. It is a strong structural claim with a weak-blindness caveat, and both halves travel together.

Why the fused loop matters. Accumulating Σ(xᵢ − x̄)(yᵢ − ȳ) and Σ(xᵢ − x̄)² in the same pass, in the same order, with the same intermediate precision, is a different rounding sequence from computing them in two loops — even though it is the same mathematics. For a compatibility-oriented implementation the loop structure is part of the specification, not an optimisation detail.

The evaluation step. Publishing as a + b·x rather than as ȳ + b·(x − x̄) is another choice that changes the last bits, and the two are not interchangeable for compatibility even though they agree mathematically. The centered evaluation form is more accurate; the intercept form is what upstream identifies.

Rust code

44 forecast_legacy "FORECAST" (3, 3) [V "x", R "known_ys", R "known_xs"] |s, a, sv| {
    let x = num(s, a, 0)?;
    forecast(s, x, &pairs(s, a, 2, 1, PairScalars::Coerce, sv)?)
}

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

• TREND — the array-valued generalisation: multiple predictors, multiple evaluation points, one call. FORECAST is TREND restricted to one predictor and one point, as mathematics. Upstream's record is explicit that it is not the same code path in Excel; see What has not been checked.
• SLOPE and INTERCEPT — the two halves of the same fit, exposed separately. SLOPE(y,x)*x0 + INTERCEPT(y,x) is FORECAST(x0,y,x) mathematically, and is a different arithmetic expression, so agreement in the last bit is not to be assumed.
• LINEST — the full regression report. Excel's own FORECAST is documented by upstream as not running the LINEST pipeline.
• FORECAST.ETS family — exponential-smoothing time-series forecasting. Same first word, entirely different method; a reader looking for seasonality wants those, not this.
• GROWTH — the exponential analogue, fitted in log-space.

Named variants

No named variants are recorded yet.