How to Find Spearman Rank Correlation Coefficient in Excel (2 Ways)

In this article, you will learn 2 different ways to find the Spearman rank correlation coefficient in Excel.

Download Practice Workbook

You can download the Excel file from the following link and practice along with it.


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.

Formula of 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

2 Ways to Find Spearman Rank Correlation Coefficient in Excel

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.

Here are the steps to follow:

❶ 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.

RANK.AVG Function: Find Spearman Rank Correlation Coefficient in Excel

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.

Fill Handle: Find Spearman Rank Correlation Coefficient in Excel

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.

Heart Rate Rank: Find Spearman Rank Correlation Coefficient in Excel

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)

CORREL Function: Find Spearman Rank Correlation Coefficient in Excel

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.

Get the Spearman rank correlation coefficient in cell E13.

Read More: How to Calculate P Value for Spearman Correlation in Excel


Similar Readings


2. Using Traditional Formula to Find Spearman Rank Correlation Coefficient in Excel

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)^2

This 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.

Using Traditional Formula to Find Spearman Rank Correlation Coefficient in Excel

❸ 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)))

Using Traditional Formula to Find Spearman Rank Correlation Coefficient in Excel

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)


Practice Section

You will get an Excel sheet like the following screenshot, at the end of the provided Excel file. Where you can practice all the methods discussed in this article.


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

Mrinmoy

Mrinmoy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo