Table of Contents
R-Squared is a statistical measure that reflects the correlation between a dependent variable and an independent variable. It is calculated by taking the square of the correlation coefficient between both variables. To calculate R-Squared in Excel, you need to use the RSQ function. This function takes two parameters, the observed range and the predicted range. It then calculates the R-Squared value for the two ranges. To better understand how to calculate R-Squared in Excel, you can refer to the examples available online.
R-squared, often written as r2, is a measure of how well a fits a dataset.
In technical terms, it is the proportion of the variance in the response variable that can be explained by the predictor variable.
The value for r2 can range from 0 to 1:
- A value of 0 indicates that the response variable cannot be explained by the predictor variable at all.
- A value of 1 indicates that the response variable can be perfectly explained without error by the predictor variable.
Related:
This tutorial explains how to calculate r2 for two variables in Excel.
Example: Calculating R-Squared in Excel
Suppose we have the following data for the number of hours studied and the exam score received for 20 students:
Now suppose we are interested in fitting a simple linear regression model to this data, using “hours” as the predictor variable and “score” as the response variable.
To find the r2 for this data, we can use the RSQ() function in Excel, which uses the following syntax:
=RSQ(known_ys, known_xs)
where:
- known_ys: the values for the response variable
- known_xs: the values for the predictor variable
Here’s what that formula looks like in our example:
In this example, 72.73% of the variation in the exam scores can be explained by the number of hours studied.
Notice that the R Square value in the first table is 0.7273, which matches the result that we got using the RSQ() function.