LINEST

What it does

LINEST(known_ys, [known_xs], [const], [stats])

LINEST(known_y's, [known_x's], [const], [stats]) fits the multiple linear model

y = m₁x₁ + m₂x₂ + … + m_k x_k + b

to the data by ordinary least squares, and returns the fitted coefficients — optionally together with a block of regression statistics.

In matrix form, with y the n-vector of responses and X the n × p design matrix (its columns the predictors, plus a column of ones when const is TRUE), least squares chooses

β̂ = argmin_β ‖ y − Xβ ‖₂²

whose stationarity condition is the normal equations

XᵀX β̂ = Xᵀy

with the unique solution β̂ = (XᵀX)⁻¹Xᵀy whenever X has full column rank. When X is rank deficient — two identical predictors, or fewer observations than parameters — the minimiser is not unique and the problem as posed has no answer.

The residual vector e = y − Xβ̂ is orthogonal to the column space of X; that orthogonality is the geometric content of least squares and the source of the sums-of-squares decomposition reported in the statistics block:

SST = SSR + SSE,   SST = Σ(yᵢ − ȳ)²,  SSR = Σ(ŷᵢ − ȳ)²,  SSE = Σ(yᵢ − ŷᵢ)²

(with SST = Σyᵢ² when const is FALSE, because the model is then not required to reproduce the mean).

Domain. Needs n ≥ p observations, X of full column rank, and — for the statistics block — n > p so the residual degrees of freedom df = n − p are positive. Range: the coefficients are real numbers of unbounded magnitude.

Arguments

The omitted known_x's default is a real behaviour, not a convenience. Leaving it out regresses y against the sequence 1, 2, …, n, which is a time index. That is often what a user wants and is occasionally a silent disaster when the observations are not equally spaced.

Orientation matters. known_y's and known_x's must have the same shape when there is one predictor. With several predictors, each predictor occupies one column if known_y's is a column, and one row if known_y's is a row. There is no argument that says which; the shapes decide.

const = FALSE changes the statistics, not just the coefficient. Forcing the line through the origin changes the definition of SST and of df, so r² computed under const = FALSE is not comparable with the ordinary one and can behave in ways that surprise — it is not bounded the same way. This is a genuine and much-litigated statistical point, not an implementation quirk.

Try it

Quirks

The returned array

This is where most LINEST confusion lives, and it has two distinct parts.

Coefficient order is reversed. With k predictors the first row is

{ m_k , m_{k−1} , … , m₂ , m₁ , b }

The slope for the last predictor column comes first, and the constant is last. A reader who lays the predictors out left to right and reads the result left to right gets them backwards. For simple regression (k = 1) the row is {m, b} — slope then intercept — which is easy to remember and gives no warning about the general rule.

stats = TRUE adds four rows. The full block is a 5 × (k+1) array:

Rows 3, 4 and 5 have only two meaningful entries each; the remaining cells of those rows are documented as #N/A. The layout is documented by Microsoft on the LINEST page.

Note the arithmetic relations that must hold internally, and which make excellent metamorphic probes: r² = SSR/(SSR+SSE), se(y) = √(SSE/df), F = (SSR/(p−1))/(SSE/df) when const is TRUE, and df = n − p.

Evidence record EV-MISC-0010 records one structural comparison against Excel that belongs here rather than in a statistics table: with const FALSE the returned row still carries a trailing constant cell set to zero rather than being shortened by one column, and multivariate coefficients come back in reverse predictor order. That record is explicit that these observations are structural and uncounted.

Result and edge cases

Returns an Array — a spilled dynamic array in modern Excel, and historically a Ctrl+Shift+Enter formula whose selection size decided how much of the block you saw. Selecting too small a region truncated the block silently, which is the reason so many workbooks contain a LINEST that reports only its first cell.

• Exactly determined system (n = p) — the fit is exact, SSE = 0, and df = 0. The statistics block then divides by zero for se(y) and F; the documented result is #N/A in those cells. The one such fit read in Excel, three observations on three x columns with const FALSE, returns instead standard errors and se(y) of 0, r² of 1, F of #NUM!, df of 0, SSE of exactly 0 and SSR the sum of y² (FN-LINEST-002).
• Rank-deficient X — duplicated or linearly dependent predictor columns. The least-squares solution is not unique. Microsoft's page documents that LINEST may drop collinear columns and return zero coefficients for them; the Handbook has not verified what the rank tolerance is, and the tolerance is the whole question, because "collinear" in floating point is a threshold decision rather than a fact.
• Error values propagate.

Errors

How to implement

Numerical notes

LINEST is the function on which the accuracy of a spreadsheet's statistical lane has historically been judged, and the reason is conditioning.

The normal equations square the condition number. Solving XᵀXβ = Xᵀy by forming XᵀX and factorising it costs

κ₂(XᵀX) = κ₂(X)²

so a design matrix with condition number 10⁸ — entirely ordinary for a cubic polynomial fit on unscaled data — produces a Gram matrix with condition number 10¹⁶, which is the reciprocal of double precision. At that point the computed coefficients have no correct digits, and the failure is silent: the routine returns numbers, not an error. This is the single most important fact on this page.

The stable alternatives. The standard remedy, described in every numerical linear algebra text, is to avoid forming XᵀX at all:

• Householder QR on X directly: X = QR, solve Rβ = Qᵀy by back substitution. The achieved accuracy scales with κ₂(X), not its square. This is the default in LAPACK's dgels and in essentially every serious statistics package.
• Modified Gram–Schmidt with reorthogonalisation, cheaper and nearly as good.
• The singular value decomposition, X = UΣVᵀ, which additionally exposes rank deficiency as small singular values and lets the caller truncate them deliberately. The SVD is the right tool when collinearity is expected rather than exceptional.

The canonical references are Golub and Van Loan, Matrix Computations, chapter 5; Björck, Numerical Methods for Least Squares Problems; Lawson and Hanson, Solving Least Squares Problems; and Higham, Accuracy and Stability of Numerical Algorithms, chapters 19–20 for the error analysis. Numerical Recipes chapter 15 covers the applied side including the SVD route.

Centring and scaling. Even with a stable factorisation, an unscaled design matrix wastes precision. Subtracting the column means (which const = TRUE does implicitly for the constant term, but not for the predictors) and scaling columns to comparable norms can reduce κ₂(X) by many orders of magnitude at no statistical cost, because the fit is equivariant under those transformations. A polynomial fit on years 1990…2020 is hopeless; the same fit on year − 2005 is easy. LINEST gives the caller no way to request this, so the caller must do it in the worksheet.

The published critique record. The NIST Statistical Reference Datasets (StRD) include linear regression problems of graded difficulty — Norris (easy), Longley (the classic ill-conditioned economic dataset from Longley 1967), Wampler, and Filip, a tenth-degree polynomial fit that is deliberately near the edge of double precision. McCullough and Wilson's sequence of papers On the accuracy of statistical procedures in Microsoft Excel (1999, and follow-ups covering later versions) reported LINEST failing to return any correct digits on Filip in the versions they tested, and tracked what changed across releases. Morten Welinder's work on Gnumeric's statistical functions covers the same territory from the reimplementer's side. The Handbook names these as the published literature on the family; of what Excel does internally it asserts only the reflector steps of FN-LINEST-001, and it has not re-run the StRD datasets itself.

A note on the statistics block. SSE computed as SST − SSR is a cancellation waiting to happen when r² is close to 1 — precisely the case where users care. Accumulating the residuals directly, Σ(yᵢ − ŷᵢ)², costs one extra pass and is unconditionally better. The same applies to r² itself: computing it as SSR/SST when SSE is tiny loses the digits that distinguish 0.999999 from 0.9999999.

Rust code

57 linest "LINEST" (1, 4) [R "known_ys", R "known_xs", V "const", V "stats"] |s, a, sv| {
    regression(s, a, sv, k::regression_forecast_family::eval_linest_surface, RegressionNumbers::Standard)
}

fn linest_kernel(
    known_y: &NumericVector,
    known_x: &PreparedPredictorInput,
    use_const: bool,
    stats: bool,
) -> Result<CalcValue, WorksheetErrorCode> {
    let fit = linear_fit_from_data(known_y, known_x, use_const)?;
    let model = &fit.model;
    let mut row = model.coefficients.iter().rev().copied().collect::<Vec<_>>();
    row.push(if use_const { model.intercept } else { 0.0 });
    if stats {
        return Ok(statistics_block(&fit, use_const, row));
    }
    Ok(row_array(&row))
}

pub(crate) fn eval_linest_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 use_const =
        bool_flag_from_eval_or_default(optional_arg_value(args.get(2))?, true)?;
    let stats = bool_flag_from_eval_or_default(optional_arg_value(args.get(3))?, false)?;
    linest_kernel(&known_y, &known_x, use_const, stats)
}

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

Version differences

No comparison between specific Excel builds is recorded here yet. This does not establish that all builds behave alike.

Alternatives

Relationships

• LOGEST — LINEST applied to ln y, reported multiplicatively. See that page for why its statistics describe the log-scale fit.
• GROWTH — LOGEST as TREND is to LINEST.
• SLOPE and INTERCEPT — the scalar single-predictor special cases. LINEST(y, x) returns exactly those two numbers, in that order.
• RSQ, STEYX, PEARSON, FORECAST.LINEAR — each reproduces one cell of the LINEST block for the single-predictor case. Whether the dedicated surfaces and the LINEST cell return identical bits is exactly the kind of question a metamorphic suite exists to answer, and the Handbook has no such suite.
• The Analysis ToolPak Regression tool — a separate implementation of the same statistics, historically a separate code path. Comparing the two on the same data is a classic self-check.
• Confused with: chart trendlines, which fit on the plotted series and use their own display rounding; and with LINEST polynomial fits, which come from passing x, x², x³, … as separate predictor columns — the construction that makes the conditioning problem below unavoidable.

Named variants

No named variants are recorded yet.