TREND

What it does

TREND(known_ys, [known_xs], [new_xs], [const])

TREND(known_y's, [known_x's], [new_x's], [const]) fits a linear model to the known data by the method of least squares — Microsoft's page says so in those words — and returns the fitted values of that model at the new x positions.

With one predictor the model is the familiar y = m·x + b that the documentation names. With several predictors it is

y = m₁x₁ + m₂x₂ + … + m_kx_k + b

and TREND returns ŷ* = X* β̂ where β̂ minimizes ‖y − Xβ‖₂ over the known data. In matrix terms, with X the n × (k+1) design matrix whose first column is all ones (when const is TRUE or omitted):

β̂ = argmin ‖y − Xβ‖₂     ⟺     XᵀX β̂ = Xᵀy       (the normal equations)

The normal-equations characterization is mathematically correct and numerically the worst way to obtain β̂; that distinction is the entire subject of the Numerical notes section, and it is the subject of the evidence record attached to this page.

For the single-predictor case the solution is explicit:

b_slope = Sxy / Sxx,     b_intercept = ȳ − b_slope·x̄

with Sxx = Σ(x − x̄)² and Sxy = Σ(x − x̄)(y − ȳ). Two things follow that matter more than they look:

1. The centred form is a different computation from the uncentred one. Sxx written as Σ(x − x̄)² and Sxx written as Σx² − (Σx)²/n are the same real number and wildly different floating-point numbers when the data has a large mean relative to its spread.
2. const = FALSE removes the option to centre. With the intercept forced to zero the model is y = m·x and the estimator is Σxy / Σx² — an uncentred quantity by necessity. The two settings of const are therefore not one algorithm with a flag; they are two.

TREND is a dynamic array surface: it returns one fitted value per row of new_x's, so its result is generally an array rather than a scalar.

Arguments

Documented details worth stating precisely, because each one is a place readers get surprised:

• Omitting known_x's substitutes the index sequence {1,2,3,…}. TREND(A1:A12) is a regression against time-as-row-number, and it is a real model, not a degenerate one.
• Omitting new_x's re-uses known_x's, which makes TREND(y, x) the vector of fitted values — the in-sample predictions — rather than a forecast.
• const = FALSE sets b = 0 and adjusts the m values so that y = mx. It does not simply drop the intercept from a fit computed with one; the whole fit changes.
• Several predictors are supported, and when there is more than one, known_y's must be a vector.
• Polynomial fitting is documented as a use: regress against the same variable raised to different powers, supplying x, x², x³ as separate predictor columns. This is worth flagging as the numerically hardest thing TREND is documented to do — the Vandermonde design matrix it produces is the standard example of an ill-conditioned least-squares problem.
• Array entry: results that are arrays must be entered as array formulas with Ctrl+Shift+Enter unless the build supports dynamic arrays, in which case Enter suffices.

Try it

Quirks

Result and edge cases

Returns Number values, generally as an array shaped by new_x's.

• Shape. One fitted value per new observation. The orientation of the result follows the orientation of new_x's, which is one of the two most common sources of #REF! and #VALUE! from this function; the other is a design matrix whose predictor columns run the wrong way.
• Rank deficiency. Collinear predictors — including the exactly-duplicated column that a careless polynomial expansion produces — make XᵀX singular. What Excel does then is not documented on this page.
• Fewer observations than parameters is the same problem in a different dress and is equally undocumented.
• Large offsets. Data whose x values sit far from zero relative to their spread (times as serial dates, sensor readings around a large baseline) is the regime where the uncentred and centred arrangements of the same formula give visibly different answers. It is also the regime the evidence record attached to this page found most diagnostic.
• Text, logicals and empty cells in the data ranges follow the family's coercion policy. This surface's module carries the open upstream defect stream BUG-FUNC-028, whose title names an unswept conversion, text/date, array-lift and coercion gap; the Handbook cites it by name and does not restate its contents.

Errors

Microsoft's TREND page carries no error table. That is itself a finding: a function with a documented rank-deficiency exposure, a documented orientation requirement, and a documented optional-argument default chain publishes no documented error conditions at all.

What can be said honestly:

Nothing in this table is a documented Microsoft statement, and the Handbook has not verified any of it against Excel.

How to implement

Numerical notes

Least squares is the best-studied problem in numerical linear algebra, and the whole literature converges on one sentence: do not form XᵀX.

Why. The normal equations square the condition number: κ₂(XᵀX) = κ₂(X)². A design matrix with a modest condition number of 10⁶ — easily produced by a quadratic fit on data that does not straddle zero — yields a normal-equations system with condition number 10¹², and in double precision that consumes most of the available accuracy before the solve begins. A Vandermonde matrix from a cubic or higher polynomial fit is far worse; this is why the NIST StRD Filip dataset (a tenth-degree polynomial fit) is the standard executioner of regression implementations.

The alternatives. A Householder QR factorization X = QR reduces the problem to the triangular system Rβ̂ = Qᵀy and works with κ₂(X), not its square; a singular value decomposition additionally handles rank deficiency gracefully and yields the minimum-norm solution. These are the recommendations of Lawson & Hanson, Björck, Golub & Van Loan, and Higham's Accuracy and Stability of Numerical Algorithms chapter 20, and they are what LAPACK's gels/gelsd do. Solving normal equations with a Gauss–Jordan inverse — forming (XᵀX)⁻¹ explicitly and multiplying — is the worst option available on both accuracy and stability grounds, and it is the one textbooks use to state the model.

Centring. For a model with an intercept, subtracting the column means before the solve and recovering b = ȳ − Σ mⱼx̄ⱼ afterwards is mathematically equivalent and numerically decisive: it removes the offset that otherwise dominates every entry of XᵀX. With const = FALSE this route is closed, because there is no intercept to absorb the shift — which is exactly why the two const settings deserve separate treatment and separate testing.

• A centred candidate kernel — the arrangement promoted for FORECAST, generalized to the design matrix — does substantially better on the same witnesses. Its score is a candidate score, not the shipping surface's; the record's reader warning says so, and this page repeats it.
• The record's ruled_out list is the interesting part, because each entry is a conclusion about Excel, reached by elimination: any one-pass computational sum-of-squares formula is refuted, because it explodes on the huge-offset dataset where Excel does not — so Excel centres. Extended-precision-throughout arrangements are refuted as strictly worse. And every natural centred variant raced — forward and reverse accumulation, extended precision, centred QR by Householder, centred normal equations, fused-multiply-add dot products, symmetric versus asymmetric centring, one- versus two-pass — reproduces FORECAST's slope and never TREND's.
• The record's open question states the limit honestly: the exact operation graph producing TREND's slope on the residual datasets is under-determined by the available witnesses, and the multivariate regime is entirely unprobed.

The figures behind all of that belong to the evidence layer rendered beside this page and are not restated in this prose. The record also carries a warning this page must repeat in its own voice: the catalogue's observation that the prediction step reproduces Excel given the coefficients is a decomposition of the problem, not a pass rate, and it must never be read as "TREND matches Excel".

The accuracy of Excel's regression outputs is also the subject of a published literature — Morten Welinder's work on Gnumeric's statistical functions, and the NIST StRD assessments by Knüsel and by McCullough & Wilson. The Handbook names those as the right reading and does not assert from them what any current Excel build does.

Rust code

55 trend "TREND" (1, 4) [R "known_ys", R "known_xs", R "new_xs", V "const"] |s, a, sv| {
    regression(s, a, sv, k::regression_forecast_family::eval_trend_surface, RegressionNumbers::Standard)
}

fn linear_model_from_data(
    known_y: &NumericVector,
    known_x: &PreparedPredictorInput,
    use_const: bool,
) -> Result<LinearRegressionModel, WorksheetErrorCode> {
    linear_fit_from_data(known_y, known_x, use_const).map(|fit| fit.model)
}

/// A TREND prediction: `b_1*x_1 + b_2*x_2 + ...`, added in predictor order, then the intercept.
fn linear_prediction(model: &LinearRegressionModel, x: &[f64]) -> f64 {
    let mut terms = model.coefficients.iter().zip(x.iter()).map(|(b, x)| b * x);
    let first = terms.next().unwrap_or(0.0);
    terms.fold(first, |sum, term| sum + term) + model.intercept
}

fn trend_kernel(
    known_y: &NumericVector,
    known_x: &PreparedPredictorInput,
    new_x: &PreparedPredictorInput,
    use_const: bool,
) -> Result<CalcValue, WorksheetErrorCode> {
    let model = linear_model_from_data(known_y, known_x, use_const)?;
    if new_x.predictors.cols != model.coefficients.len() {
        return Err(WorksheetErrorCode::Ref);
    }
    let predicted = (0..new_x.predictors.rows)
        .map(|row_idx| linear_prediction(&model, new_x.predictors.row_values(row_idx)))
        .collect::<Vec<_>>();
    eval_value_from_output_shape(&predicted, new_x.output_shape)
}

pub(crate) fn eval_trend_surface(
    args: &[CalcValue]
) -> Result<CalcValue, WorksheetErrorCode> {
    let known_y = collect_numeric_vector_arg(&args[0])?;
    let known_x_raw = args
        .get(1)
        .filter(|arg| !matches!(arg.core_ref(), CoreRef::Missing))
        .map(|arg| collect_numeric_matrix_arg(arg))
        .transpose()?;
    let known_x = predictor_input_from_optional(known_x_raw, &known_y)?;
    let new_x_raw = args
        .get(2)
        .filter(|arg| !matches!(arg.core_ref(), CoreRef::Missing))
        .map(|arg| collect_numeric_matrix_arg(arg))
        .transpose()?;
    let new_x = prediction_input_from_optional(new_x_raw, &known_y, &known_x)?;
    let use_const =
        bool_flag_from_eval_or_default(optional_arg_value(args.get(3))?, true)?;
    trend_kernel(&known_y, &known_x, &new_x, use_const)
}

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

• FORECAST / FORECAST.LINEAR are the single-predictor, single-point special case. This relationship is not decorative: the record attached to this page found that every natural centred arrangement it raced reproduced FORECAST's slope and never TREND's, on the datasets where the two disagree. Two Excel surfaces that ought to compute the same slope apparently do not compute it the same way — and that is a finding about Excel's internals, arrived at by elimination.
• GROWTH is TREND on ln y: the exponential model y = b·m^x, fitted by least squares in the log domain. Same module, same machinery, different link.
• SLOPE, INTERCEPT, RSQ, STEYX are the scalar readouts of the same single-predictor fit; STEYX tabulates the correspondence.
• Confused with: FORECAST.ETS (exponential smoothing, an entirely different model), charting trendlines (which fit the same model but display coefficients rounded for the label), and SLOPE×x + INTERCEPT written out by hand, which is a third route to the same number and need not agree with either of the first two in the last bits.

Named variants

No named variants are recorded yet.