RSQ in WPS Spreadsheet: Squared Correlation with a Complete Paired-Data Check

• Introduced: documented by 2019-06-17 in the official WPS lesson; the first release is not established.
• Availability: the lesson identifies WPS Office for Windows Personal, without a numeric release range.
• Scope: exact current supported builds and other WPS clients remain unverified. Historical naming does not establish retirement.
• Checked: October 14, 2026. Documentation and independent calculations; no native WPS test was performed.

=RSQ(known_y, known_x)

Both arrays or ranges are required. Keep each X and Y observation paired in the same row. The source documents #N/A for empty or unequal-length datasets.

It also says text, logical values and blank cells in references are ignored while numeric zero participates. That statement does not fully specify how differently placed ignored values are re-paired across two ranges. The primary examples therefore use complete numeric pairs; mixed-position alignment remains unverified.

The source’s prose incorrectly describes R-squared as the ratio of Y variance to X variance. Its own equation defines squared normalized correlation instead. The examples below follow that equation and make the distinction explicit.

Enter this table in A1:B5:

=RSQ(B2:B5,A2:A5)

The means are X=2.5 and Y=4. The independent centered calculation is:

Products total 11; X squares total 5; Y squares total 26. The correlation is 11/SQRT(130), approximately 0.964763821. Squaring it gives expected RSQ 121/130, approximately 0.930769231.

For the same four observations, population variances are 5/4 = 1.25 for X and 26/4 = 6.5 for Y. Their ratio is 5.2, which is not the RSQ value 121/130.

Multiplying every Y by ten multiplies its variance by 100, making that raw ratio 520. Squared correlation remains 121/130, because the scale factor cancels between the centered-product numerator and the normalization denominator.

This unit-invariance check exposes the source sentence’s error without borrowing another spreadsheet’s function implementation.

For the primary fixture, the independent least-squares line with an intercept is Y = -1.5 + 2.2X. Its fitted values are 0.7, 2.9, 5.1 and 7.3.

Observed-minus-fitted residuals are 0.3, 0.1, -1.1 and 0.7, whose squared total is 1.8. The total centered Y-square sum is 26, so:

1 - 1.8/26 = 121/130.

This explains the variance-explained interpretation in this ordinary one-predictor, intercept-containing model. It is not a statement that RSQ is an arbitrary ratio of the two raw data variances.

Replace every primary Y by its negative. Correlation changes from approximately 0.964763821 to approximately -0.964763821, but the expected RSQ remains 121/130.

A high RSQ alone therefore cannot tell you whether Y tends to rise or fall with X. Inspect the paired plot or a separately justified slope calculation when direction matters.

For an exact increasing line such as X=1,2,3 and Y=3,5,7, the independent squared correlation is 1. An exact decreasing line Y=7,5,3 also gives 1. Neither result establishes causation or reliable extrapolation beyond the observed data.

Reversing both columns together preserves the pairs and the modeled result. Sorting only one column can change the relationships; it is not a harmless display operation.

If either variable is constant, the correlation equation has a zero denominator. The cited source does not establish the exact native constant-input error, so this guide does not assign one. Error propagation, singleton behavior and native precision limits also remain unverified.

Use AVERAGE and SUMSQ on explicitly constructed helper values to audit the arithmetic. STEYX describes a different fitted-model quantity in Y units rather than squared correlation.

• WPS Academy: RSQ, 2019-06-17 (Chinese).
• Editorial testing standards.

Worked outputs are independently calculated expectations, not captured native results. Function-specific source gaps and product/client boundaries remain explicitly scoped.