RSQ Function (LibreOffice Calc)
The RSQ function in LibreOffice Calc returns the coefficient of determination (R²) between two datasets. This guide explains syntax, interpretation, examples, errors, and best practices.
Compatibility
▾| Excel | ✔ |
| Gnumeric | ✔ |
| Google_sheets | ✔ |
| Libreoffice | ✔ |
| Numbers | ✖ |
| Onlyoffice | ✔ |
| Openoffice | ✔ |
| Wps | ✔ |
| Zoho | ✔ |
What the RSQ Function Does ▾
- Calculates R², the coefficient of determination
- Measures how well X explains Y in a linear model
- Works with numeric X/Y pairs
- Useful for regression diagnostics and model evaluation
- Works across sheets
R² is always between 0 and 1.
Syntax ▾
RSQ(known_y; known_x)
Where:
known_y— dependent variable (Y values)known_x— independent variable (X values)
Interpretation of R² ▾
| R² Value | Meaning |
|---|---|
| 1.0 | Perfect fit |
| 0.9–1.0 | Excellent fit |
| 0.7–0.9 | Strong fit |
| 0.5–0.7 | Moderate fit |
| 0.3–0.5 | Weak fit |
| 0–0.3 | Very weak fit |
R² measures how much of the variance in Y is explained by X.
Basic Examples ▾
R² for a simple regression
=RSQ(B1:B10; A1:A10)
R² across sheets
=RSQ(Sheet1.B1:B50; Sheet2.A1:A50)
R² using named ranges
=RSQ(Sales; MarketingSpend)
R² with dates as X-values
=RSQ(B1:B100; A1:A100)
(Calc converts dates to serial numbers.)
Advanced Examples ▾
R² ignoring errors
=RSQ(IF(ISNUMBER(B1:B100); B1:B100); IF(ISNUMBER(A1:A100); A1:A100))
(Confirm with Ctrl+Shift+Enter in older Calc.)
R² using filtered (visible) data only
Use SUBTOTAL helper column to filter X/Y before passing to RSQ.
R² after removing outliers
=RSQ(FILTER(B1:B100; B1:B100<1000); FILTER(A1:A100; B1:B100<1000))
R² for log-transformed data
=RSQ(LN(B1:B10); LN(A1:A10))
R² for exponential regression (manual)
Exponential model:
y = b * m^x
Equivalent linearized R²:
=RSQ(LN(Y1:Y20); X1:X20)
R² for multi-variable regression (via LINEST)
=INDEX(LINEST(Y1:Y20; X1:Z20; TRUE; TRUE); 3; 1)
How RSQ Calculates the Coefficient of Determination ▾
RSQ is simply:
[ R^2 = r^2 ]
Where:
- ( r ) = Pearson correlation coefficient
Expanded:
[ R^2 = \frac{\left[\sum (x_i - \bar{x})(y_i - \bar{y})\right]^2}{\left[\sum (x_i - \bar{x})^2\right]\left[\sum (y_i - \bar{y})^2\right]} ]
R² measures the proportion of variance in Y explained by X.
Common Errors and Fixes ▾
Err:502 — Invalid argument
Occurs when:
- X and Y ranges have different sizes
- One or both arrays contain no numeric values
- Arrays contain only one data point
Err:504 — Parameter error
Occurs when:
- Semicolons are incorrect
- Range references malformed
RSQ returns unexpected value
Possible causes:
- Relationship is non-linear
- Outliers distort regression
- X-values or Y-values contain hidden text
- Data contains zeros that should be excluded
RSQ differs from LINEST R²
They are identical — LINEST simply returns more statistics.
Best Practices ▾
- Use RSQ to evaluate regression model quality
- Use CORREL or PEARSON to measure raw correlation
- Use LINEST for full regression diagnostics
- Remove outliers before modeling
- Plot your data to confirm linearity
- Use named ranges for cleaner formulas