Mrinmoy Roy
In this article, you will learn 2 different ways to find the Spearman rank correlation coefficient in Excel.
What Is Spearman Rank Correlation Coefficient?
The Spearman rank correlation coefficient is a measure of how closely two variables are related. It’s utilized when dealing with ordinal data, or data that can be sorted from least to greatest. By squaring the difference between the ranks of the two variables, the Spearman rank correlation coefficient is computed. It is a measure of how strong the two variables’ link is.
It is generally represented by the symbol, ρ (“rho”). Here’s the traditional formula of the Spearman rank correlation coefficient.
Here,
- ρ is the Spearman rank correlation coefficient
- dᵢ is the subtraction between the ranks of the two variables
- n is the total number of cases
1. Using CORREL Function to Find Spearman Rank Correlation Coefficient in Excel
We can use the CORREL function to determine the relation between two variables. This is exactly what we need to find the Spearman rank correlation coefficient in Excel.
❶ Insert the following formula using the RANK.AVG function in cell D5.
=RANK.AVG(B5,$B$5:$B$11,1)❷ Then press the ENTER button.
Formula Breakdown
- B5 is the first cell of the column Exercise Period.
- $B$5:$B$11 is the range of the column Exercise Period.
- 1 is used to rank in ascending order.
❸ Drag the Fill Handle icon from cell D5 to D11.
So we got the relative ranking of the data in the Exercise Period column.
❹ Again insert the following formula in cell E5.
=RANK.AVG(C5,$C$5:$C$11,1)❺ Press ENTER.
Formula Breakdown
- C5 is the first cell of the column Heart Rate.
- $C$5:$C$11 is the range of the column Heart Rate.
- 1 is used to rank in ascending order.
❻ Drag the Fill Handle icon from cell E5 to E11.
Now we got the relative ranking of the data in the Heart Rate column.
Now it’s time to find the Spearman rank correlation coefficient using the CORREL function.
❼ Insert the following formula in cell E13.
=CORREL(D5:D11,E5:E11)
Formula Breakdown
- D5:D11 is the range of the column Exercise Period.
- E5:E11 is the range of the column Heart Rate.
❽ Now press the ENTER button.
So you will get the Spearman rank correlation coefficient in cell E13.
Read More: How to Calculate P Value for Spearman Correlation in Excel
Similar Readings
- How to Do Correlation in Excel (3 Easy Methods)
- Make Correlation Graph in Excel (with Easy Steps)
- How to Calculate Intraclass Correlation Coefficient in Excel
- Calculate Cross Correlation in Excel (2 Quick Ways)
- How to Calculate Autocorrelation in Excel (2 Ways)
2. Finding Spearman Rank Correlation Coefficient with Traditional Excel Formula
The traditional formula to determine the Spearman rank correlation coefficient is,
Now I will show you how to use this formula to find the Spearman rank correlation coefficient in Excel.
❶ At first, insert the following formula in cell F5.
=(D5-E5)^2This formula calculates the square of the difference between the ranking of two variables.
To learn how to get the ranking, click_here.
❷ Then press the ENTER button.
❸ Drag the Fill Handle icon from cell F5 to F11.
The result will be like this:
❹ Now insert the following formula in cell E13.
=1-(6*SUM(F5:F11)/(7*(7^2-1)))
Formula Breakdown
- SUM(F5:F11) returns the summation of all values from cells F5 to F11.
- 1-(6*SUM(F5:F11) is the numerator of the traditional formula of Spearman rank correlation coefficient.
- 7 is the total number of cases
- (7*(7^2-1))) is the denominator of the traditional formula of Spearman rank correlation coefficient.
❺ Finally, press ENTER.
Now you will get the Spearman rank correlation coefficient in cell E13.
Read More: How to Calculate Spearman Correlation in Excel (3 Easy Methods)
Download Practice Workbook
You can download the Excel file from the following link and practice along with it.
Conclusion
To sum up, we have discussed 2 ways to calculate Spearman’s rank correlation coefficient in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website Exceldemy to explore more.
Related Articles
- How to Make a Correlation Scatter Plot in Excel (2 Quick Methods)
- Calculate Correlation Coefficient in Excel (3 Methods)
- How to Interpret Correlation Table in Excel (A Complete Guideline)
- Calculate Pearson Correlation Coefficient in Excel (4 Methods)
- How to Make a Correlation Matrix in Excel (2 Handy Approaches)
- Find Correlation between Two Variables in Excel
