Understanding R-Squared and How to Calculate It in Excel (2024)

Download the workbook 👉 HERE and follow along.

What is R-Squared?

R-Squared, also called the coefficient of determination, shows how well the data fit a regression model. It tells us what percentage of the variation in the dependent variable (what you’re trying to predict) is explained by the independent variable(s) (the predictors).

Why is R-Squared Important?

R-Squared values range from 0 to 1:

• 0 means the model explains none of the variability.
• 1 means the model explains all the variability.

A higher R Squared indicates a better fit, but it’s not always perfect. Sometimes, a high R Squared can mislead if the model is overfitting the data.

How to Interpret R-Squared

• High R-Squared: Your model fits the data well. For example, an R-Squared of 0.85 means 85% of the variance in your data is explained by the model.
• Low R-Squared: Your model doesn’t fit well. However, don’t rely solely on R Squared. Context and other diagnostic measures are crucial.

R-Squared Formula

Here’s the formula for R Squared:

• SS_{regression}: Sum of squares due to regression (explained variance).
• SS_{total}: Total sum of squares (total variance).

How to Calculate R-Squared in Excel

Let’s use an example to understand how to find R Squared in Excel.

Suppose we have data on car prices based on their mileage and age. Here’s the sample dataset:

In this example, we want to calculate the R-Squared value to see how well the mileage explains the car price variation.

Understanding X and Y Variables

In regression analysis, the variables are typically categorized as:

• Dependent Variable (Y): This is the variable you are trying to predict or explain. For example, in our car price example, the car price is the dependent variable.
• Independent Variable (X): This is the variable you use to predict the dependent variable. In our example, car mileage is the independent variable.

Using the RSQ Function

The RSQ function in Excel is straightforward and easy to use. It provides the proportion of variance in the dependent variable that can be explained by the independent variable.

Syntax:

=RSQ(known_y's, known_x's)

• known_y’s: The Y values (dependent variable, Car Price) are in cells C2:C11
• known_x’s: The X values (independent variable, Car Mileage) are in cells A2:A11

Therefore the formula is:

=RSQ(C2:C11, A2:A11)

For the given example, the RSQ function returns a R-Squared value of 0.8357. It means that about 84% of the variability in car prices is explained by car mileage, suggesting that mileage is a good predictor of price.

Using the LINEST Function

The LINEST function in Excel not only gives the R-Squared value but also other regression statistics like coefficients and standard errors. This makes it useful for a more in-depth analysis.

Syntax:

=LINEST(known_y's, known_x's, const, stats)

• known_y’s: The Y values (dependent variable, Car Price) are in cells C2:C11
• known_x’s: The X values (independent variable, Car Mileage) are in cells A2:A11
• const: A logical value (TRUE or FALSE). TRUE (or omitted) means the intercept should be calculated, and FALSE means the intercept is set to zero.
• stats: A logical value (TRUE or FALSE). TRUE means that additional regression statistics should be returned, and FALSE means only the slope is returned.

Therefore the Excel formula is:

=LINEST(C2:C11, A2:A11, TRUE, TRUE)

The LINEST function will provide a range of outputs, including the R-Squared value of 0.8357.

Additional Results Provided by LINEST

In addition to R squared, the LINEST function also provides:

• Slope: The rate of change in the dependent variable per unit change in the independent variable.
• Intercept: The value of the dependent variable when the independent variable is zero.
• Standard Errors: Indicating the precision of the slope and intercept estimates.
• F-Statistic: Used to determine if the relationship between the dependent and independent variables is statistically significant.
• Degrees of Freedom: The number of independent pieces of information in the data.
• Regression Sum of Squares: The explained variation in the data.
• Residual Sum of Squares: The unexplained variation in the data.

💡 LINEST is an array formula. For Office 365 and Excel 2021, simply pressing Enter will display the results. For older versions of Excel, you need to press Ctrl + Shift + Enter to get the results.

Using a Chart Trendline

An easy way to calculate R Squared in Excel is by adding a linear trendline to a chart.

Insert a Scatter Plot:

• Highlight the data in column A (Car Mileage), hold down Ctrl, and highlight the data in column C (Car Price).
• Go to the Insert tab and select Scatter (X, Y) Chart

Add a Trendline:

• Click on any data point in the scatter plot to select the data series.
• Right-click and choose Add Trendline.

• In the Format Trendline panel, check the box for Display R-squared value on chart.

• The R-Squared value will be displayed on the chart, showing how well the data points fit the regression line.

Using Data Analysis ToolPak

The Data Analysis ToolPak is an Excel add-in that provides data analysis tools for statistical and engineering analysis. When enabled, it offers a range of tools to perform complex data analysis tasks such as regression, ANOVA, descriptive statistics, and more.

Enable Data Analysis ToolPak:

• Go to File > Options > Add-Ins.
• In the drop down next to “Manage:” select Excel Add-ins and click on Go.

• In the Add-Ins window, check Analysis ToolPak and click OK.

Perform Regression Analysis:

• Go to the Data tab and click Data Analysis.

• Select Regression from the list and click OK.

• Set the Input Y Range (dependent variable, Car Price) to your data (C1:C11).
• Set the Input X Range (independent variable, Car Mileage) to your data (A1:A11).
• Check Labels to include headers.
• Choose an Output Range to display the results and click on OK.

• The regression line calculator in Excel will display the regression analysis output which includes the R squared value.

Download the Workbook

Enhance your learning experience by downloading our workbook. Practice the techniques discussed in real-time and master the coefficient of determination (R Squared) calculation in Excel with hands-on examples. Download the workbook here and start applying what you’ve learned directly in Excel.