When constructing linear regression models or forecasting trends in Google Sheets, generating a trendline or calculating slope and intercept is only the initial phase of analysis. To determine whether your regression model possesses genuine predictive power, you must evaluate how well the independent variable explains variance in the dependent variable and quantify the expected margin of forecasting error. Google Sheets provides two essential statistical functions for this purpose: RSQ and STEYX.
The RSQ function calculates the coefficient of determination (r-squared), while STEYX calculates the standard error of the predicted y-value for each x in the regression. Together, they provide the necessary statistical foundation to validate forecasts in financial modelling, sales projections, and scientific research.
Understanding RSQ vs STEYX
Both functions evaluate the relationship between known dependent observations (known_data_y) and known independent explanatory variables (known_data_x):
- Coefficient of Determination (
RSQ): Returns the square of the Pearson product-moment correlation coefficient (r²). It quantifies the proportion of variance in the dependent variable that is predictable from the independent variable, scaled between 0.0 (0%) and 1.0 (100%). - Standard Error of the Estimate (
STEYX): Measures the dispersion of observed values around the linear regression line. It represents the standard deviation of regression residuals, expressed in the same physical units as your dependent variable (y). Lower values denote tighter clustering and higher forecasting precision.
Syntax and Argument Specifications
The syntax for both functions requires identical parameter sequencing:
=RSQ(known_data_y, known_data_x)
=STEYX(known_data_y, known_data_x)Adhere to the following data handling rules when defining ranges:
- Argument Sequencing: Always supply the dependent variable range (y-values) first, followed by the independent variable range (x-values). Swapping this order will alter the
STEYXcalculation, as error residuals are measured along the vertical y-axis. - Dimension Matching: Both ranges must contain the exact same number of data points. If the ranges differ in dimensions, Google Sheets returns an
#N/Aerror. - Collinear Data: If the variance of either dataset is zero (all values in a column are identical), the standard deviation cannot be calculated, returning a
#DIV/0!error. - Minimum Sample Size: At least three distinct coordinate pairs are recommended to establish meaningful degrees of freedom. Fewer than two valid pairs will fail mathematically.
Statistical Benchmark Matrix
Use the following table as a benchmark guide when reviewing calculated metrics:
| Metric | Numerical Result | Model Evaluation | Analytical Action |
|---|---|---|---|
| RSQ (r²) | 0.80 to 1.00 | Strong Goodness-of-Fit | 80%–100% of variance explained; highly reliable linear predictive capacity. |
| RSQ (r²) | 0.50 to 0.79 | Moderate Explanatory Power | Noticeable correlation exists; consider introducing secondary variables in multivariate models. |
| RSQ (r²) | < 0.30 | Weak / Inadequate Fit | Independent variable fails to explain outcomes; linear model is unsuited for forecasting. |
| STEYX | Near 0 relative to Y mean | High Precision | Predicted values closely track observed data points; narrow confidence intervals. |
| STEYX | Large relative to Y mean | High Variance Residuals | Forecasts will exhibit broad prediction bands; model subject to substantial uncertainty. |
Practical Implementation Example
Suppose you are modelling monthly advertising spend (Column A, A2:A13) against revenue outcomes (Column B, B2:B13):
- Select an output cell (e.g.,
D2) to compute the coefficient of determination:=RSQ(B2:B13, A2:A13) - Format cell
D2as a percentage (Format > Number > Percent) to display the metric as variance explained (e.g.,87.4%). - In cell
D3, compute the standard prediction error:=STEYX(B2:B13, A2:A13) - Format cell
D3as currency to match the revenue dimension. If the result is £1,250, approximately 68% of actual revenue observations fall within ±£1,250 of the regression prediction line.
Frequently Asked Questions
What is the difference between CORREL squared and RSQ?
Calculating =CORREL(B2:B13, A2:A13)^2 produces the identical numerical value as =RSQ(B2:B13, A2:A13). The RSQ function is optimised for readability and standard econometric documentation.
Can STEYX be used to construct prediction intervals?
Yes. In standard linear regression, an approximate 95% prediction interval for an individual forecast can be estimated by taking the predicted value ± (1.96 × STEYX), assuming normally distributed residuals and a sufficiently large sample size.