The coefficient of determination, also known as R-squared (R2), is a measure used in statistics to tell how well the independent variable predicts or explains the changes in the dependent variable.
For instance, the measure can help you understand how or whether advertising spending (independent variable) impacts a company’s sales revenue (dependent variable).
The value of R2 ranges from 0 to 1. In our example of a company’s advertising spending versus sales revenue, an R2 value of 0 or close to 0 indicates that advertising spending has little to no impact on explaining the changes in sales revenue.
On the other hand, an R2 value of 1 or close to 1 means that advertising spending fully or to a relatively great extent explains the changes in sales revenue.
In this tutorial, I will show you several ways to find the coefficient of determination in Excel.
Method #1: Use the RSQ Function
You can use Excel’s built-in RSQ function to calculate the coefficient of determination. The function assumes a linear relationship between variables.
Syntax of RSQ:
RSQ (known_ys, known_xs)
The ‘known_ys’ argument is required and is the range of dependent variable data (y-values) you want to explain or predict.
The ‘known_xs’ argument is required and is the range of independent variable data (x-values) you want to use to explain the changes in the y-values.
Consider the dataset below with advertising spending in column A and sales revenue in column B. You want to calculate the R2 of the dataset.
You can use the formula below to calculate the dataset’s R2 :
=RSQ(B2:B7,A2:A7)
The RSQ function returns a coefficient of determination of 0.966023, indicating that advertising spending explains the changes in the sales revenue to a great extent.
Also read: Calculate Correlation Coefficient in Excel
Method #2: Use the CORREL Function
Suppose you have an older version of Excel that does not have the RSQ function.
In that case, you can manually calculate a dataset’s R2 (coefficient of determination) using the CORREL function in a two-step process formula. First, use the CORREL function to find the correlation coefficient of the dataset, then square the correlation coefficient to get the R2.
Consider the dataset below with advertising spending in column A and sales revenue in column B. You want to calculate the R2 of the dataset.
You can use the formula below to calculate the dataset’s R2 :
=CORREL(A2:A7,B2:B7)^2
Also read: Calculate the Coefficient of Variation in Excel
Method #3: Use the LINEST Function
You can use the LINEST function to calculate a dataset’s R2. The function can give you several statistics for a line, including the R-squared value.
Syntax of LINEST:
LINEST(known_ys, [known_xs], [const], [stats])
The ‘known_ys’ argument is required and is the range of the dependent y-values, the values you are trying to predict or explain.
The ‘known_xs’ argument is optional and is the range of the independent x-values, the input values, or predictors. If omitted, Excel assumes the x-values to be {1, 2, 3, …}.
The ‘const’ optional argument can be either TRUE or FALSE. If you omit it or give a value of TRUE, Excel calculates the intercept (the constant b in the equation y = mx + b). If you give FALSE, the intercept is set to 0 (i.e., the line is forced to go through the origin).
The ‘stats’ optional argument can be either TRUE or FALSE. TRUE returns additional regression statistics such as R-squared, standard errors, F-statistic, etc. If FALSE or omitted, it only returns the m-coefficients and the constant b in the equation y = mx + b.
Consider the dataset below with advertising spending in column A and sales revenue in column B. You want to calculate the R2 of the dataset.
You can use the formula below to calculate the dataset’s R2 :
=LINEST(B2:B7,A2:A7,TRUE,TRUE)
The formula returns an array of statistics in two columns. The third statistic in the first column is the coefficient of determination.
Method #4: Use the Data Analysis ToolPak Add-in
You can use Excel’s Data Analysis ToolPak add-in to calculate the coefficient of determination in Excel.
Suppose you have the dataset below with advertising spending in column A and sales revenue in column B. You want to calculate the R2 of the dataset.
You can calculate the dataset’s R2 using the steps below:
- Open the Data tab and click Data Analysis on the Analysis group.
Note: If you don’t see Data Analysis on the Ribbon, you can enable it using the steps below:
- Click the File button on the Ribbon to open the Backstage view window.
- Click Option on the left sidebar of the Backstage view window.
- Select the ‘Add-ins’ category on the left sidebar of the Excel Options dialog box, ensure that ‘Excel Add-ins’ is selected on the Manage drop-down list, and click the ‘Go’ button.
- On the ‘Add-ins’ dialog box, select the ‘Analysis ToolPak’ option on the ‘Add-ins available’ list box, and click OK.
The above steps add the Analysis group with the Data Analysis option to the Data tab on the Ribbon.
- On the Data Analysis dialog box, choose ‘Regression’ on the ‘Analysis Tools’ list box and click OK.
- On the ‘Regression’ dialog box, enter the y-values and x-values, and select where you want the output displayed on the current or new worksheet.
- Click OK.
In the output, look for the ‘R Square’ value, the coefficient of determination.
Also read: How to Calculate Covariance in Excel?
Method #5: Use a Scatter Plot’s Trendline
You can display a coefficient of determination on a scatter plot’s trendline.
Suppose you have the dataset below with advertising spending in column A and sales revenue in column B. You want to calculate the R2 of the dataset.
You can use the steps below:
- Select the entire dataset.
- Click the Insert tab, open the ‘Insert Scatter (X, Y) or Bubble Chart’ drop-down menu, and select the ‘Scatter’ chart option.
The above step inserts a scatter plot on the worksheet.
- Right-click any data point on the scatter plot and select ‘Add Trendline’ on the shortcut menu.
The above step opens the ‘Format Trendline’ task pane on the right of the Excel window.
- Select the ‘Display R-squared value on chart’ option on the ‘Format Trendline’ task pane.
The above step displays the coefficient of determination on the scatter plot’s trendline, as shown below.
I have shown you several ways to find the coefficient of determination in Excel. I hope you found the tutorial helpful.
Other Excel articles you may also like:
