STEYX

What it does

STEYX(known_ys, known_xs)

STEYX(known_y's, known_x's) returns the standard error of the estimate for a simple linear regression of y on x — the residual standard deviation of the fitted line, and the quantity a textbook writes s_{y·x} or s_e.

Microsoft gives the defining formula on the function's page. Written out, with n the number of paired points and bars denoting means:

STEYX = sqrt(  ( Σ(y − ȳ)²  −  ( Σ(x − x̄)(y − ȳ) )² / Σ(x − x̄)² )  /  (n − 2)  )

The bracketed quantity is the residual sum of squares of the least-squares line. Introducing the usual centred sums

Sxx = Σ(x − x̄)²,    Syy = Σ(y − ȳ)²,    Sxy = Σ(x − x̄)(y − ȳ)

the formula is sqrt( (Syy − Sxy²/Sxx) / (n − 2) ), and every equivalent reading is worth having in view because each one exposes a different implementation route:

Syy − Sxy²/Sxx  =  Syy · (1 − r²)  =  Σ(yᵢ − ŷᵢ)²  =  Syy − b · Sxy

where r is the Pearson correlation (RSQ is r²), b = Sxy/Sxx is the slope (SLOPE), and ŷᵢ = a + b·xᵢ are the fitted values. The last of the four is a sum of squares and therefore non-negative by construction; the first is a difference of two positive numbers and is not. That distinction is the entire numerical content of this function, and it is taken up below.

Domain and range: defined for n ≥ 3 paired finite points with Sxx > 0; the result is a non-negative real in the same units as y. It is exactly 0 when the points are collinear, and it grows without bound as the scatter about the line grows.

The divisor n − 2 is the residual degrees of freedom: two parameters — slope and intercept — have been estimated from the data. This makes STEYX² the unbiased estimator of the error variance σ² in the model y = α + βx + ε, ε ~ N(0, σ²), which is why it is n − 2 and not n or n − 1.

Arguments

The documented coercion rule is the familiar direct-versus-scan split, stated on the function's page in the same words the aggregates use:

• Typed directly into the argument list: logical values and text representations of numbers are counted.
• Inside an array or reference: text, logical values and empty cells are ignored — but cells containing zero are included.

See Coercion and lifting for why those two rules are different rules rather than one rule with an exception.

What the documentation does not say, and this page will not invent: when a value is ignored in one vector, what happens to its partner in the other? A regression over paired data has to answer this — either the pair is dropped together, or the two vectors are compacted independently and the pairing silently shifts. The two readings give different numbers on the same input, and the second one gives an answer that is not a regression of anything. Microsoft's page is silent. It is the first probe below.

Try it

Quirks

Result and edge cases

Returns Number, a non-negative standard error.

• Near-collinear data — real calibration data, for instance — is worse than perfectly collinear data, because the true answer is small and non-zero, so relative accuracy is observable and the cancellation eats it.
• n = 3 is the smallest admissible case (one residual degree of freedom) and is the point where the estimator has the least information; n < 3 is a documented #DIV/0!.
• Constant x makes Sxx = 0 and the regression undefined. The documented error list does not cover it. What Excel does — #DIV/0!, #NUM!, or a number — is unchecked.
• Constant y with varying x is fine: the fitted line is flat and the standard error is 0.
• Very large or very small magnitudes. Because the formula is written in centred deviations it does not have the classic uncentred sum-of-squares overflow problem, but Sxy² squares an already-squared quantity and can overflow for data that Syy and Sxx handle comfortably. A vector of values around 1e160 is enough.

Errors

As documented on Microsoft's STEYX page:

Not documented: the constant-x case (Sxx = 0), and what happens if the cancellation in the documented formula produces a negative radicand. Both would have to resolve to something, and neither is named. The Handbook has not verified any of this against Excel.

How to implement

Numerical notes

The subtraction is the problem. Syy − Sxy²/Sxx is mathematically Syy(1 − r²), and when the fit is good r² is close to 1, so the two terms are nearly equal and their difference loses digits proportionally. For r² = 1 − δ, roughly −log₁₀ δ decimal digits are gone. Real regression data with r² = 0.9999 — an ordinary result in a calibration lab — has already lost four digits before the square root is taken, and the square root then halves the relative error, which is the one mercy in the arrangement. The failure mode at the end of the road is a negative radicand and a domain error on sqrt for data that is merely collinear to within rounding.

The fix is to compute a sum of squares, not a difference. Σ(yᵢ − ŷᵢ)² is non-negative by construction and has no cancellation between terms — only within each residual, where it is benign because the residual is the quantity of interest. It costs a second pass over the data. For the same reason a QR factorization of the design matrix by Householder reflections yields the residual norm directly as the norm of the transformed tail, with no subtraction anywhere; this is the standard recommendation of the least-squares literature (Lawson & Hanson; Björck; Golub & Van Loan; Higham, Accuracy and Stability of Numerical Algorithms, chapter 20). The one-variable analogue of the same argument — never compute a variance as Σx² − (Σx)²/n — is Higham's §1.9 and Chan, Golub & Van Loan's classic note, and it is the same lesson one dimension down.

Centring is already correct here. Microsoft's documented formula is written in deviations from the means, so it is a two-pass centred form and avoids the worst of the uncentred disease. That is worth crediting: the documented formula's weakness is the r² → 1 cancellation, not the large-offset catastrophe that afflicts uncentred computational formulas.

Accumulation order matters. Sxx, Syy and Sxy are sums of products; their accuracy depends on summation order and on whether a compensated (Kahan/Neumaier) or pairwise sum is used. For data spanning many magnitudes the difference is visible in the last digits of the answer.

The accuracy of Excel's own statistical procedures — and specifically its regression outputs — is the subject of a substantial published literature: Morten Welinder's work on Gnumeric's statistical functions documents the historical failure modes concretely, and the assessments by Knüsel and by McCullough & Wilson evaluate Excel's regression against the NIST StRD reference datasets (Norris, Longley, Filip, the Wampler series). The Handbook names those sources because they are the right reading; it does not assert from them what any current Excel build does with STEYX, which is a separate question nobody in this record has measured.

Rust code

43 steyx "STEYX" (2, 2) [R "known_ys", R "known_xs"] |s, a, sv| {
    out(s, k::moment_stats_family::steyx_kernel(&pairs(s, a, 1, 0, PairScalars::SkipNonnumeric, sv)?))
}

pub(crate) fn steyx_kernel(pairs: &[(f64, f64)]) -> Result<f64, WorksheetErrorCode> {
    if pairs.len() < 3 {
        return Err(WorksheetErrorCode::Div0);
    }
    let n = pairs.len() as f64;
    let mean_x = pairs.iter().map(|(x, _)| *x).sum::<f64>() / n;
    let mean_y = pairs.iter().map(|(_, y)| *y).sum::<f64>() / n;
    let mut sum_x2 = 0.0;
    let mut sum_y2 = 0.0;
    let mut sum_xy = 0.0;
    for (x, y) in pairs {
        let dx = *x - mean_x;
        let dy = *y - mean_y;
        sum_x2 += dx * dx;
        sum_y2 += dy * dy;
        sum_xy += dx * dy;
    }
    if sum_x2 == 0.0 {
        return Err(WorksheetErrorCode::Div0);
    }
    let sse = sum_y2 - (sum_xy * sum_xy) / sum_x2;
    let residual = if sse < 0.0 && sse.abs() < 1.0e-12 {
        0.0
    } else {
        sse
    };
    Ok((residual / (n - 2.0)).sqrt())
}

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

STEYX is one output of a single least-squares fit, and Excel spreads that one fit across half a dozen surfaces. Holding them together is the useful thing this page can do:

The identity worth remembering, because it is exact and testable: STEYX² · (n − 2) = Syy · (1 − RSQ), with Syy = DEVSQ(known_y's). That gives a route to check Excel against Excel with no external oracle.

• LINEST is the closest relative and the one that matters most: it computes this quantity as part of its statistics block, so Excel has at least two code paths to the same number. Are they the same path? Nobody has published an answer.
• STDEV.S is the corresponding one-variable object: STEYX is to a fitted line what STDEV.S is to a fitted constant, right down to the degrees-of-freedom bookkeeping (n − 1 for one estimated parameter, n − 2 for two).
• Confused with: the standard error of the slope (which LINEST also reports, and which is STEYX / sqrt(Sxx)), and with the standard error of a prediction at a new point (which is larger, and which no Excel surface returns). STEYX's own English description — "the standard error of the predicted y-value for each x in the regression" — invites exactly this confusion, because the standard error of a predicted value is not what the documented formula computes.

Named variants

No named variants are recorded yet.