When performing linear regression, financial forecasting, and scientific data analysis, calculating the line of best fit through SLOPE and INTERCEPT is only half the journey. Analysts must also determine how reliably that trendline models the underlying observations. In statistical modeling, this goodness-of-fit metric is measured by the coefficient of determination, widely known as R-squared (R²). Rather than constructing scatter plots, displaying chart trendlines, and copying values manually, Microsoft Excel includes a dedicated worksheet function for this exact metric: RSQ.
The RSQ function calculates the square of the Pearson product-moment correlation coefficient between two numeric ranges, returning a normalised value between 0.0 and 1.0 that quantifies the proportion of variance in the dependent variable explained by the independent variable. In this tutorial, you will explore the syntax, practical applications, financial use cases, and interpretation guidelines for the RSQ function in Microsoft Excel.
Understanding the RSQ Function and R-Squared Values
In linear modeling, R-squared provides a statistical gauge of predictive explanatory power:
- R² = 1.0 (100%): Perfect correlation. All data points lie directly on the linear regression trendline; the independent variable explains all variance in the outcome.
- R² = 0.70 to 0.90: Strong explanatory power. Common in physics, engineering calibrations, and established economic pricing models.
- R² = 0.30 to 0.50: Moderate relationship. Frequently observed in social sciences, marketing attribution, and consumer behavioural datasets.
- R² = 0.0 (0%): No linear relationship exists between the datasets.
Syntax and Parameters of RSQ
The syntax for the RSQ function in Excel is straightforward:
=RSQ(known_y's, known_x's)- known_y’s: Required. An array, cell range, or list of numbers representing the dependent variables (the outcome or response metrics you are attempting to explain).
- known_x’s: Required. An array, cell range, or list of numbers representing the independent variables (the explanatory predictors or inputs).
Note on Parameter Order: Similar to Excel’s SLOPE, INTERCEPT, and FORECAST functions, the dependent Y values must be passed as the first parameter, followed by the independent X values as the second parameter.
Step 1: Calculating R-Squared in Excel Step-by-Step
Consider a retail marketing dataset where Column A contains Advertising Budget in thousands of pounds (Independent Variable X), and Column B contains Total Units Sold (Dependent Variable Y):
| Row | Column A (Ad Spend £k) | Column B (Units Sold) |
|---|---|---|
| 2 | 10 | 120 |
| 3 | 15 | 165 |
| 4 | 20 | 210 |
| 5 | 25 | 245 |
| 6 | 30 | 305 |
To calculate the coefficient of determination in cell D2, enter:
=RSQ(B2:B6, A2:A6)Excel computes the variance and returns approximately 0.995. Format cell D2 as a percentage (Home > Percentage) to read 99.5%. This indicates that 99.5% of the variance in retail sales is directly explained by changes in the advertising budget.
Step 2: Comparing RSQ with CORREL and PEARSON
Because R-squared is mathematically the square of the Pearson correlation coefficient (r), you can verify the output of RSQ using CORREL:
=CORREL(B2:B6, A2:A6)^2Both formulas yield identical numerical answers. However, using =RSQ() is cleaner, reduces formula keystrokes, and communicates analytical intent clearly to team members reviewing your financial workbook.
Step 3: Calculating Portfolio Beta Quality in Financial Modeling
In investment analysis, Beta indicates how a specific stock moves relative to the broader benchmark index (such as the FTSE 100 or S&P 500). However, Beta is only statistically valid if R-squared is sufficiently high:
- Enter monthly benchmark index returns in range
B2:B36(known X’s). - Enter monthly stock returns in range
C2:C36(known Y’s). - Calculate R-squared:
=RSQ(C2:C36, B2:B36).
If R² is below 0.50, the calculated Beta is weak and cannot be relied upon to measure systemic market risk, as individual company factors dominate performance.
Troubleshooting Common RSQ Formula Errors
- #N/A Error: Occurs when
known_y'sandknown_x'shave unequal numbers of data points or mismatched dimensions (e.g. comparing 10 Y rows with 12 X rows). - #DIV/0! Error: Occurs if the variance of the supplied arguments is zero (e.g. all X values are identical), or if either range contains fewer than three numeric observations.
- Text Cells Ignored:
RSQautomatically ignores empty cells, boolean values, and text strings within ranges, evaluating only numerical pairs.