How Do You Calculate R-Squared in Excel?

What Is R-Squared?

In the financial world, R-squared is a statistical measure that represents the percentage of a fund's or a security's movements that can be explained by movements in a benchmark index. In this field, R-squared typically ranges from 1% to 100%.

Where correlation explains the strength of the relationship between an independent and dependent variable, R-squared explains to what extent the variance of one variable explains the variance of the second variable. 

Continue reading to learn more about R-squared, including how to automate its calculation in Excel.

Key Takeaways

  • R-squared, or the coefficient of determination, is a statistical measure that uses the variance of one variable to explain the variance of another.
  • Further testing is required to determine if R-squared approaching +/- 1 is statistically significant.
  • Variables must be independent and their relationship linear for correlation to exist.
  • When calculating a correlation, it is important to normalize data into a common unit.
  • To correlate stocks, normalize their data into percent return.

Formula for R-Squared

R 2 = 1 R S S T S S where: R 2 = Coefficient of determination R S S = Sum of squares of residuals T S S = Total sum of squares \begin{aligned}&R^2=1-\frac{RSS}{TSS}\\&\textbf{where:}\\&R^2=\text{Coefficient of determination}\\&RSS=\text{Sum of squares of residuals}\\&TSS=\text{Total sum of squares}\end{aligned} R2=1TSSRSSwhere:R2=Coefficient of determinationRSS=Sum of squares of residualsTSS=Total sum of squares

Common Mistakes With R-Squared

The first most common mistake is assuming an R-squared approaching +/- 1 is statistically significant. A reading approaching +/- 1 increases the chances of actual statistical significance, but without further testing, it's impossible to know based on the result alone.

Statistical testing is not completely straightforward; it can get complicated for several reasons. To touch on this briefly, a critical assumption of correlation (and thus R-squared) is that the variables are independent and that the relationship between them is linear. In theory, you would test these claims to determine if a correlation calculation is appropriate.

Investors often use R-squared with beta to more accurately assess asset managers' performance.

The second most common mistake is forgetting to normalize the data into a common unit. If you are calculating a correlation (or R-squared) on two betas, then the units are already normalized: The unit is beta.

If you want to correlate stocks, it's critical you normalize them into percent return and not share price changes. This happens frequently, even among investment professionals. 

For stock price correlation (or R-squared), you are essentially asking two questions: What is the return over a certain number of periods, and how does that variance relate to another securities variance over the same period? Two securities might have a high correlation (or R-squared) if the return is daily percent changes over the past 52 weeks but a low correlation if the return is monthly changes over the past 52 weeks.

Which one is "better"? There is no perfect answer, and it depends on the purpose of the test.

How to Calculate R-Squared in Excel

There are several methods for calculating R-squared in Excel.

The simplest way is to get two data sets and use the built-in R-squared formula. The other alternative is to find a correlation and then square it. Both are shown below:

Frequently Asked Questions

How Do I Find R-Squared in Excel?

To find R-squared in Excel, enter the following formula into an empty cell:=RSQ([Data set 1], [Data set 2]). Data sets are ranges of data, most often arranged in a column or row. To select a group or set of data, select a cell and drag the cursor to highlight the other cells.

How Can You Find Adjusted R-Squared in Excel?

When a variable is added, R-squared will increase. However, the adjusted R-squared may increase or decrease depending on the explanatory power of the added variable. To calculate adjusted R-squared in Excel, enter the following formula into an empty cell: = 1 - (1 - R^2)(n-1/n-k-1), where k is the number of variables, and n is the number of data points.

How Can I Add R-Squared Value in Excel?

Adding an R-squared value in Excel can be done by using the formula to find the correlation of variables and then squaring the result, or by using the R-squared formula. The Excel formula for finding the correlation is "= CORREL([Data set 1], [Data set 2]).

To find R-squared, select the cell with the correlation formula and square the result (=[correlation cell] ^2). To find R-squared using a single formula, enter the following in an empty cell: =RSQ([Data set 1],[Data set 2]).