RSQ in Zoho Sheet: R-Squared for Linear Relationships
Calculate squared Pearson correlation in Zoho Sheet and interpret simple-linear-regression R-squared without claiming causation.
Verification status: Official documentation referenced; example outcomes independently reasoned or arithmetically checked where stated. Not executed in Zoho Sheet. How formula examples are checked
Quick answer: RSQ computes the square of Pearson’s linear correlation coefficient for two datasets. A result close to 1 indicates a strong absolute linear association, while 0 indicates little linear association; neither proves causation. In the six-week example, =RSQ(A2:A7;B2:B7) has the independently calculated expected value 0.830485304. The Zoho article calls the arguments x and y in that order—an easily overlooked difference from the Y-first naming in Microsoft Excel and Google Sheets. Because Pearson correlation is symmetric, swapping these two arrays does not change the squared numeric result for the same valid numeric pairs. Zoho RSQ reference, checked October 12, 2026.
Official Zoho syntax and arguments
=RSQ(x; y)
| Argument | Required? | Meaning |
|---|---|---|
x |
Yes | First dataset as a range or array, such as A2:A7. The article labels it x. |
y |
Yes | Second dataset as a matching range or array, such as B2:B7. The article labels it y. |
Zoho’s RSQ article explicitly says text, logical values and empty cells are ignored, and its error table identifies #N/A! for unequal counts. It does not spell out how every unmatched nonnumeric cell is paired or how all zero-variance inputs are reported by the native calculation engine. Use numeric paired rows in the examples, and do not treat another spreadsheet’s documented omission behaviour as proof of Zoho behaviour. Source: Zoho RSQ syntax, remarks and errors; checked 2026-10-12.
Reproduce the six-week example
- Create a blank sheet, then enter the following values in A1:B7.
- Confirm both data columns contain actual numeric cells and the two ranges are the same height.
- Enter the formula in a separate unused cell such as D2; do not place a formula inside its own source ranges.
- Display enough decimal places to compare with the expected result. Cell formatting can round the display without changing underlying values.
| Row | A — Week (x) |
B — Orders (y) |
|---|---|---|
| 1 | Week | Orders |
| 2 | 1 | 3 |
| 3 | 2 | 5 |
| 4 | 3 | 4 |
| 5 | 4 | 8 |
| 6 | 5 | 7 |
| 7 | 6 | 10 |
All six rows represent paired observations: week 1 corresponds to 3 orders, week 2 to 5 orders, and so on. Keep the header row outside the input ranges. Do not sort only one column; doing so destroys the pairings. Check that A2:B7 are numeric, with no text-number import problems.
Worked example 1 — Square the six-week correlation
In D4, enter:
=RSQ(A2:A7;B2:B7)
Expected result: 0.830485304169515. A percentage-format result cell would display approximately 83.05%, depending on chosen decimals. In the narrow context of an ordinary least-squares line with a fitted intercept and the same six paired observations, this figure is the training-data coefficient of determination: about 83% of the sample Y variation about its mean is accounted for by the fitted straight-line relationship to X. It is not a statement that 83% of individual orders are caused by time or that future predictions are 83% accurate.
Independent math and an interpretation check
For a simple least-squares line with a free intercept, let x̄ and ȳ be the numeric means of the paired observations. Then
x̄ = 3.5,ȳ = 37/6 = 6.166666667.Sxx = Σ(x − x̄)² = 17.5.Sxy = Σ(x − x̄)(y − ȳ) = 22.5.Syy = Σ(y − ȳ)² = 209/6 ≈ 34.833333333.- Least-squares slope
b = Sxy/Sxx = 9/7 ≈ 1.285714286. - Fitted y-axis intercept
a = ȳ − b×x̄ = 5/3 ≈ 1.666666667. - Correlation squared
r² = Sxy²/(Sxx×Syy) = 1215/1463 ≈ 0.830485304.
These figures were computed independently from the listed numbers. They are expected mathematical values, not output obtained from a native Zoho Sheet session. The agreement between the formulas is a useful cross-check only when every formula uses exactly the same paired numeric rows.
For the shared sample, the Pearson correlation is positive, approximately +0.911309664, and its square is 0.830485304. RSQ does not display that sign; if you need the direction of the association, inspect SLOPE or signed CORREL rather than RSQ alone.
Worked example 2 — A perfect straight-line relationship
Enter X 1, 2, 3, 4 in F2:F5 and matching Y 3, 5, 7, 9 in G2:G5. Use:
=RSQ(F2:F5;G2:G5)
Expected result: 1, because all points lie exactly on the straight line y=2x+1. Here the separately calculated SLOPE is positive 2. The result says the sample points are exactly collinear, but does not rule out other influences or prove forecasts will stay perfect.
Worked example 3 — A negative slope can still give RSQ = 1
In J2:J5, enter X 1, 2, 3, 4; in K2:K5, enter Y 9, 7, 5, 3. These points follow y = 11 − 2x. Formula:
=RSQ(J2:J5;K2:K5)
Expected result: 1 again. The matching trendline slope is -2 and the signed correlation is -1, yet its square is positive 1. This is why an RSQ figure must not be interpreted as increasing-versus-decreasing direction.
Advanced application — Add a readable model-quality indicator
For a dashboard based on the six observed rows, convert the squared correlation to a percentage number for a labelled metric:
=ROUND(RSQ(A2:A7;B2:B7)*100;1)
Expected result: 83.0 (meaning 83.0%, without automatically adding a percent-format multiplier). If you prefer an actual cell percentage display, use the unscaled =RSQ(A2:A7;B2:B7) and format its cell as a percentage instead. Do not multiply by 100 and then also apply percentage formatting, which could display around 8,305%.
For independent consistency review, use:
=CORREL(A2:A7;B2:B7)^2
Expected mathematical result: 0.830485304169515 on the same clean numeric dataset, because RSQ is defined as correlation squared. Zoho documents CORREL(x; y) separately. This is a proposed cross-check, not a verified native formula execution. Zoho CORREL reference, checked 2026-10-12.
For decision-making, show RSQ alongside slope, a scatterplot, residual plots and sample size. RSQ computed from just a few observations can be unstable, and a high in-sample value does not guarantee good future forecasts. Outliers can move both the regression and the coefficient; visually examine your data instead of assuming every high RSQ means a useful predictive model. A curved relationship may have a low linear RSQ while still being predictable with a different model.
Errors, edge cases, and what to check
| Situation | Supported conclusion and next step |
|---|---|
#N/A! |
Zoho explicitly lists unequal dataset counts; use ranges of identical length, e.g. A2:A7 and B2:B7. |
#NAME!, #REF!, #VALUE! |
Zoho’s general error table lists invalid names, broken references and incorrect types. Check function spelling, range addresses and numeric imports. |
| Only one numeric pair | Pearson correlation needs at least two observations with variation; its mathematical denominator cannot be evaluated with a single pair. Native Zoho error display not tested. |
| Every X or every Y identical | One variance term is zero, so Pearson’s coefficient is mathematically undefined. Never interpret a blank/error or substitute R²=0; test actual Zoho error behaviour before documenting a code. |
| Text or empty cells | Zoho says they are ignored, but exact pairing when only one column has a blank remains unresolved here. Clean or filter two columns by the same row criteria and validate on Zoho. |
| RSQ looks positive while slope is negative | Expected: squaring loses the sign. Inspect SLOPE or signed CORREL for direction. |
| High RSQ with a bad-looking forecast | In-sample fit is not causal evidence or external validation; audit outliers, seasonality, extrapolation and the number of rows. |
0 is present as a measured observation |
Zero is a legitimate number, not automatically missing; do not delete it merely because it is small. |
Related guides: AVERAGE, COUNT and STDEV. Additional official relevant functions: CORREL, SLOPE, INTERCEPT and LINEST.
Compatibility: RSQ argument names differ in the manuals
- Zoho Sheet: Official English syntax is
RSQ(x; y). - Microsoft Excel: Official English syntax is
RSQ(known_y's,known_x's). - Google Sheets: Official syntax is
RSQ(data_y, data_x).
For the exact same paired observations, Pearson correlation is symmetric: changing only argument order gives the same numeric RSQ. Nevertheless, the published argument labels differ, and delimiters depend on spreadsheet locale. Import/export behaviour and handling of mixed text/logical/empty values must be tested rather than presumed equivalent. Microsoft documents specific handling of directly typed logical values, whereas Zoho’s article simply says text and logical values are ignored, so a blanket compatibility statement would be misleading. Sources checked October 12, 2026: Zoho RSQ, Microsoft RSQ and Google RSQ.
Availability, settings, and validation scope
These instructions reflect Zoho’s English public Zoho Sheet help as checked on October 12, 2026. Its examples use semicolons (;) between arguments; a different locale or imported workbook can display a different separator. Use the formula separator accepted by your own sheet without altering the argument order. The checked function article does not give a first-supported version, a universal account/plan entitlement, or a device-by-device compatibility matrix; therefore this guide makes no such claims. Feature behaviour in older releases, mobile clients, disconnected spreadsheets and third-party imports remains unverified.
Validation ledger: Official-reference research = checked; independent mathematical model = checked; native Zoho Sheet execution = NOT RUN; actual returned error codes for edge cases = NOT RUN.
References and claim dates
- Zoho Sheet — RSQ, syntax
RSQ(x; y), ignored values, worked reference outputs and errors. Checked 2026-10-12: https://help.zoho.com/portal/en/kb/sheet/functions/statistical/articles/rsq - Zoho Sheet — CORREL, definition used for mathematical cross-check. Checked 2026-10-12: https://help.zoho.com/portal/en/kb/sheet/functions/statistical/articles/correl
- Zoho Sheet — SLOPE, sign/direction interpretation. Checked 2026-10-12: https://help.zoho.com/portal/en/kb/sheet/functions/statistical/articles/slope
- Microsoft Excel — RSQ, different published argument labels; Excel-specific type remarks. Checked 2026-10-12: https://support.microsoft.com/en-us/excel/functions/rsq-function
- Google Sheets — RSQ, different published argument labels. Checked 2026-10-12: https://support.google.com/docs/answer/3094099?hl=en