RSQ

What it does

RSQ(known_ys, known_xs)

RSQ(known_y's, known_x's) returns r^2, the coefficient of determination of the simple linear regression of y on x — equivalently, the square of the Pearson product-moment correlation coefficient.

For pairs (x_i, y_i) with means xbar, ybar and centred sums

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

the correlation and its square are

r     =  Sxy / sqrt( Sxx * Syy )
RSQ   =  r^2  =  Sxy^2 / ( Sxx * Syy )

Its range is [0, 1], and the two ends have plain meanings: 1 exactly when the points are collinear (with nonzero spread in both coordinates), 0 exactly when Sxy = 0, meaning the least-squares line is flat and knowing x tells you nothing about y in a linear sense.

Three readings are worth carrying:

1. Variance explained. RSQ = 1 - SSE/SST, where SST = Syy is the total sum of squares of y about its mean and SSE = Syy - Sxy^2/Sxx is what the fitted line leaves. RSQ is the fraction of the variation in y that the line accounts for. This is the reading that makes it a goodness of fit rather than a strength of association.
2. A product of two slopes. RSQ(y,x) = SLOPE(y,x) * SLOPE(x,y). The regression of y on x and the regression of x on y are different lines; r^2 is exactly the amount by which their slopes fail to be reciprocals. This identity is why RSQ is symmetric in its arguments while SLOPE is not — swapping known_y's and known_x's changes nothing about the answer.
3. A squared cosine. r is the cosine of the angle between the centred x and y vectors, so RSQ is that cosine squared. This reading explains why the value is dimensionless and why it is invariant under any positive affine rescaling of either variable.

Two things RSQ is emphatically not. It is not a measure of whether a linear model is appropriate — Anscombe's quartet is four data sets with the same RSQ and four completely different pictures. And it is not a measure of slope magnitude: a tiny slope measured precisely gives RSQ near 1, and a steep slope measured noisily gives RSQ near 0. Read RSQ next to SLOPE and STEYX, never alone.

Arguments

The y argument comes first, matching SLOPE, INTERCEPT and STEYX. Unlike those three, getting the order wrong here is harmless: RSQ is symmetric, so the answer is the same either way. (It is not harmless in CORREL's cousin functions when the sign of r matters, and RSQ discards the sign entirely — which is its principal weakness as a reported statistic.)

Both arguments are consumed by scanning, and pairing is positional, row-major over each argument; only the element counts must match, not the shapes.

Try it

Quirks

Result and edge cases

Returns Number in [0, 1].

• Fewer than two complete pairs. #DIV/0!.
• Sxx = 0 or Syy = 0 — no spread in one of the variables. #DIV/0!. There is no defensible value: with Syy = 0 there is nothing to explain, and with Sxx = 0 there is nothing to explain it with. Note the guard is an exact comparison against zero, so data with a real but vanishing spread produces a number rather than an error.
• Perfect collinearity. Mathematically 1. In floating point the returned value can fall a few units in the last place short of 1; see Numerical notes. Code that tests RSQ(...) = 1 will fail on data that is exactly collinear.
• Unequal element counts. #N/A, documented and implemented.
• Errors in either argument propagate as themselves.

How to implement

Numerical notes

Written out, the implemented route is

r    =  ( Sxy/(n-1) )  /  ( sqrt(Sxx/(n-1)) * sqrt(Syy/(n-1)) )
RSQ  =  r * r

against the direct route

RSQ  =  (Sxy * Sxy) / (Sxx * Syy)

They are algebraically identical and numerically are not. The implemented route performs two square roots, a multiplication of the roots, a division and finally a squaring — five rounded operations where the direct route needs three, and two of them are the square roots that the direct route does not need at all. The direct route is both faster and shorter in its error chain.

The visible consequence is at the top of the range. For exactly collinear data the true value is 1, and the implemented route reaches it through r, which is itself a rounded quotient slightly below 1; squaring a value slightly below 1 moves it further below. The reference engine's own witness assertions for this kernel record a returned value strictly less than 1 on perfectly collinear input, with the correlation and its square each landing a small number of units in the last place short. The Handbook does not restate those figures — they belong to the evidence layer — but the shape of the result is a mathematical consequence and worth stating: RSQ on collinear data is not exactly 1, and should not be tested for equality with 1. The direct closed form would not automatically fix this either; a genuinely exact answer at the top of the range requires either a fused multiply-add formulation or an explicit clamp, and both are decisions to record rather than defaults.

The same argument applies to the bottom of the range with the opposite sign: Sxy near zero is a cancelling sum of mixed-sign terms, so RSQ near zero has poor relative accuracy. Its absolute accuracy stays good, which is usually what a reader of an RSQ near zero cares about.

The centred versus raw forms. Everything said on SLOPE about the two-pass centred accumulation applies here and applies twice, because RSQ needs Sxx and Syy and Sxy. The one-pass form n*sum(x^2) - sum(x)^2 loses digits in proportion to the square of the mean-to-spread ratio and can return a negative sum of squares; the centred form the reference engine uses does not. Chan, Golub and LeVeque (1983) is the analysis. This page does not assert what Excel does internally — only that the two are distinguishable by measurement on ill-conditioned data, and that the measurement has not been made here.

Residual hazards in the good form. The means and all three centred sums are accumulated by naive left-to-right summation, so the error grows with n where compensated or pairwise summation would bound it. Sxx and Syy have non-negative terms and are well-conditioned; Sxy does not and is not. The Sxx == 0 and Syy == 0 guards are exact comparisons, not tolerances, so data with real but tiny spread yields a number rather than a diagnostic.

Rust code

40 rsq "RSQ" (2, 2) [R "known_ys", R "known_xs"] |s, a, sv| {
    out(s, paired::rsq_from_pairs(&pairs(s, a, 0, 1, PairScalars::SkipNonnumeric, sv)?))
}

pub fn rsq_from_pairs(pairs: &[(f64, f64)]) -> Result<f64, WorksheetErrorCode> {
    correlation_from_pairs(pairs).map(|corr| corr * corr)
}

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

• CORREL and PEARSON return r itself; RSQ is r^2. RSQ therefore discards the sign of the relationship, which is usually the first thing a reader wants. CORREL and PEARSON are documented as the same quantity and are two separate published entries; whether they are one computation is a question for their own pages.
• SLOPE satisfies RSQ(y,x) = SLOPE(y,x) * SLOPE(x,y). The two functions answer different questions about the same line — how steep, and how tight.
• STEYX is the standard error of the regression, sqrt(SSE/(n-2)), and is the absolute-scale companion to RSQ's relative one. RSQ near 1 with a large STEYX means the data spans a large range, not that the fit is tight in absolute terms.
• LINEST returns RSQ as one cell of its extended statistics block, along with the standard errors and the F statistic that RSQ alone cannot supply.
• INTERCEPT, TREND, FORECAST.LINEAR round out the simple-regression set.
• Readers confuse RSQ with the adjusted R^2 reported by statistics packages, which penalises for the number of predictors. With one predictor the two differ by a factor in n; Excel publishes no adjusted form.

Named variants

No named variants are recorded yet.