SLOPE

What it does

SLOPE(known_ys, known_xs)

SLOPE(known_y's, known_x's) returns the slope b of the ordinary least squares line fitted through the paired data — the unique b minimising the sum of squared vertical residuals.

For pairs (x_1, y_1), …, (x_n, y_n), write the means xbar and ybar and the centred sums

Sxx  =  sum (x_i - xbar)^2
Sxy  =  sum (x_i - xbar)(y_i - ybar)

Then

SLOPE  =  b  =  Sxy / Sxx

This is the stationary point of sum (y_i - a - b*x_i)^2 in b, and it is the entire content of the function. Three equivalent readings are worth carrying, because each explains a different edge case:

1. b = Sxy / Sxx — a ratio of second moments. Undefined precisely when Sxx = 0, that is, when every x_i is the same. A vertical scatter has no least-squares slope, which is why the documented failure there is #DIV/0! rather than a large number.
2. b = r * (s_y / s_x) — the correlation scaled by the ratio of standard deviations. This is the reading that shows SLOPE carries units: b is in units of y per unit of x, unlike r and RSQ, which are dimensionless.
3. b = COVARIANCE.P(y, x) / VAR.P(x) — and equally with the sample forms, since the n and n-1 factors cancel between numerator and denominator. SLOPE is a ratio in which the population/sample distinction is invisible.

SLOPE is not symmetric in its arguments. The least-squares line of y on x minimises vertical distances; the line of x on y minimises horizontal ones, and they are different lines unless the fit is perfect. The relation between them is SLOPE(y,x) * SLOPE(x,y) = r^2, which is RSQ — so swapping the arguments does not give the reciprocal, it gives the reciprocal shrunk by r^2. This is regression to the mean, stated as an identity.

Together with INTERCEPT, which returns a = ybar - b*xbar, the pair determines the fitted line. The line always passes through (xbar, ybar).

Arguments

The y argument comes first. This is the single most common error at the call site, because it reads backwards against (x, y) habit and against the way charts are labelled. Getting it wrong does not produce an error — it produces 1/b shrunk by r^2, a plausible number in the wrong units.

Both arguments are consumed by scanning. Pairing is positional, in row-major order over each argument; the two need not have the same shape, only the same number of elements.

Try it

Quirks

Result and edge cases

Returns Number.

• All x equal. Sxx = 0 and the answer is #DIV/0!. Note this is checked on the computed Sxx, not on a distinctness test of the x values, so it also triggers when the x values differ but their centred squares underflow to zero.
• All y equal. Sxy = 0 and the slope is exactly 0 — a valid answer, not an error.
• Unequal element counts. #N/A, documented and implemented.
• Errors in either argument propagate as themselves.
• Two-dimensional arguments are flattened row-major before pairing.

How to implement

Numerical notes

SLOPE is the function where Excel's statistical accuracy was fought over in public, and the argument is entirely about which algebraically-equal formula you evaluate.

Two forms, one answer, very different error. The textbook one-pass form is

b  =  ( n*sum(x*y) - sum(x)*sum(y) )  /  ( n*sum(x^2) - sum(x)^2 )

It needs one pass and no stored data, which is why every statistics textbook of the punched-card era printed it. It is also catastrophically unstable: when the x values are large relative to their spread, n*sum(x^2) and sum(x)^2 are two nearly-equal large numbers whose difference is the small quantity Sxx. Subtracting them destroys leading digits in proportion to (xbar/s_x)^2. For x values around a million with a spread of one, the denominator can lose every significant digit it had, and the computed Sxx can even come out negative — which is impossible for a sum of squares and is the signature by which this bug is recognised.

xbar  =  sum(x)/n ;   Sxx = sum (x - xbar)^2 ;   Sxy = sum (x - xbar)(y - ybar)

computes each deviation before squaring, so no large cancellation ever occurs. Its error grows with the condition number of the problem rather than with its square. Chan, Golub and LeVeque (1983) give the error analysis for exactly this comparison, and their recommendation — use the two-pass form, or Welford-style updating if one pass is required — is the settled answer.

The published record on Excel. McCullough and Wilson's assessments of Excel's statistical procedures, running from 1999 through the 2000s in Computational Statistics & Data Analysis, and Knüsel's work on the same subject, documented failures of exactly this kind in Excel's regression and variance functions, and recorded that the algorithms were changed at Excel 2003. Welinder's work on Gnumeric's statistical functions is the other standing reference on Excel's statistical numerics and is legitimate to consult. What none of that licenses is a statement on this page about what Excel's SLOPE does internally today. The Handbook does not assert Excel's algorithm. What it asserts is that the two forms are distinguishable by measurement, and that the measurement has not been made here.

Residual hazards in the good form. The two-pass form is not immune, only far better:

• The mean is accumulated by naive left-to-right summation, so xbar itself carries an error growing with n. Compensated or pairwise summation would bound it by a constant.
• Sxx and Sxy are accumulated naively too. Sxx has non-negative terms and is therefore well-conditioned; Sxy has mixed signs and can cancel badly when the relationship is weak — which is exactly when the slope is near zero and its relative error is worst. A near-zero slope from a large noisy sample is the least trustworthy answer this function gives.
• The final division is a single correctly-rounded operation and contributes at most half an ulp.
• The Sxx == 0 test is an exact comparison against zero, not a tolerance. Data whose x spread is real but tiny passes the test and yields an enormous slope rather than an error. There is no right answer here; there is a choice, and the choice is exactness.

Reproducibility. Summation order is part of the answer. Two implementations that both use the centred form, and both are correct, will disagree in the last bits if they traverse the data in different orders or vectorise differently. Nothing about the mathematics fixes those bits.

Rust code

41 slope "SLOPE" (2, 2) [R "known_ys", R "known_xs"] |s, a, sv| {
    out(s, paired::slope_from_pairs(&pairs(s, a, 1, 0, PairScalars::SkipNonnumeric, 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

• RSQ is the goodness of fit of the same line, and satisfies RSQ(y,x) = SLOPE(y,x) * SLOPE(x,y). A large slope with a small RSQ is a strong-looking relationship that explains nothing; the two functions are meant to be read together.
• CORREL and PEARSON give r, from which b = r * s_y / s_x.
• STEYX is the standard error of the same regression — the scale of the residuals SLOPE leaves behind.
• LINEST returns the same slope as part of a full regression, with standard errors and diagnostics, and generalises to multiple predictors. SLOPE is the one-predictor special case with everything else discarded. TREND and FORECAST.LINEAR evaluate the same line at new x values.
• FORECAST is the legacy name of FORECAST.LINEAR and uses the same fit.
• Readers confuse SLOPE with the chart trendline coefficient. They are the same quantity mathematically; whether Excel computes them by the same code path is not something this page claims.

Named variants

No named variants are recorded yet.