RSQ in Excel: Squared Correlation, Lost Direction and Residual Checks

• Introduced: first introduction version is not established by the reviewed official history; the current product list is not a launch record.
• Availability: official help lists Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel 2024, Excel 2024 for Mac, Excel 2021, Excel 2021 for Mac, Excel 2019, Excel 2016.
• Checked: October 14, 2026; official RSQ reference. Documentation and independent models; no native Excel execution or wider platform certification.

=RSQ(known_ys,known_xs)

Both series are required. Microsoft includes zeros and ignores text, logical values and empty cells in arrays or references. Its direct-argument rules separately allow logical values and numeric text; error values and unconvertible text cause errors. These distinctions do not justify independently filtering the columns and destroying pairs.

The reference assigns #N/A to empty series or unequal data-point counts and #DIV/0! when the series contain only one point. Its constant-series error display is not separately established here.

The worked data are complete numeric pairs. The fitted-line comparisons use ordinary simple regression with an intercept, not an arbitrary model forced through zero.

Enter X 1,2,3,4 in A2:A5 and Y 2,1,4,3 in B2:B5:

=RSQ(B2:B5,A2:A5)

Expected result: 0.36. Both means are 2.5. Centered paired products total 3; centered X squares and Y squares each total 5. The Pearson coefficient is 3/SQRT(5*5)=0.6, whose square is 0.36.

The companion =PEARSON(B2:B5,A2:A5)^2 gives the same independently calculated value. This is a mathematical cross-check, not a claim that every invalid-input or floating-point boundary is identical across functions.

The fitted line for these data is Y=1+0.6*X. Its predicted values are 1.6,2.2,2.8,3.4. The residuals are 0.4,-1.2,1.2,-0.4, whose squared total is 3.2.

The Y squared-deviation total around its mean is 5. For this intercept-containing simple regression, 1-3.2/5=0.36, agreeing with RSQ.

The fit reduces the in-sample squared residual total relative to predicting every Y by its mean. This descriptive identity does not establish that X causes Y or that the same fit will generalize to future data.

Negate each original Y value, producing -2,-1,-4,-3. Pearson correlation becomes -0.6, while RSQ remains 0.36.

For a perfect increasing line Y 5,8,11,14, expected RSQ is 1. For the perfect decreasing line Y 11,8,5,2, it is also 1. Report SLOPE or the signed correlation when direction matters.

Use X -2,-1,0,1,2 and Y 4,1,0,1,4 in separate paired columns. Y equals X squared exactly, but centered paired products sum to zero. Expected Pearson correlation and RSQ are both 0.

A scatter plot reveals the curve that a straight-line association misses. Zero RSQ therefore does not imply that the variables have no relationship of any kind.

Conversely, sorting only the original Y values into 1,2,3,4 changes the pairs and artificially produces RSQ 1. Sort complete rows together, preserving observation identifiers.

Transform original Y using 10*Y+7, giving 27,17,47,37. Expected RSQ stays 0.36. A positive change of scale and origin preserves this normalized linear measure, even though slope, intercept and residual units change.

The measure also stays the same when both columns are reversed together. These controls distinguish harmless reordering of pairs from a changed relationship.

RSQ alone supplies no sample-size assessment, significance test, prediction interval or proof that a model’s assumptions hold. STEYX reports a residual-scale measure with a different interpretation.

Constant inputs make the mathematical correlation denominator zero. Exact native behavior for that boundary, mixed errors, nonaligned missing values and extreme magnitudes remains outside these controls. Preserve the data and diagnose the cause before replacing an undefined result with zero.

• Microsoft RSQ reference.
• Editorial testing standards.

Expected outputs are independent mathematical calculations or explicitly documented rules, not captured native Excel results. Examples use fictional data, English function names and comma separators. Native floating-point boundaries remain untested.