3.1 C
London
Friday, December 20, 2024
HomeExcelDescriptive Statistics in ExcelHow to Calculate Spearman Rank Correlation in Excel

How to Calculate Spearman Rank Correlation in Excel

Related stories

Learn About Opening an Automobile Repair Shop in India

Starting a car repair shop is quite a good...

Unlocking the Power: Embracing the Benefits of Tax-Free Investing

  Unlocking the Power: Embracing the Benefits of Tax-Free Investing For...

Income Splitting in Canada for 2023

  Income Splitting in Canada for 2023 The federal government’s expanded...

Can I Deduct Home Office Expenses on my Tax Return 2023?

Can I Deduct Home Office Expenses on my Tax...

Canadian Tax – Personal Tax Deadline 2022

  Canadian Tax – Personal Tax Deadline 2022 Resources and Tools...

In statistics, correlation refers to the strength and direction of a relationship between two variables. The value of a correlation coefficient can range from -1 to 1, with the following interpretations:

  • -1: a perfect negative relationship between two variables
  • 0: no relationship between two variables
  • 1: a perfect positive relationship between two variables

One special type of correlation is called Spearman Rank Correlation, which is used to measure the correlation between two ranked variables. (e.g. rank of a student’s math exam score vs. rank of their science exam score in a class).

This tutorial explains how to calculate the Spearman rank correlation between two variables in Excel.

Example: Spearman Rank Correlation in Excel

Perform the following steps to calculate the Spearman rank correlation between the math exam score and science exam score of 10 students in a particular class.

Step 1: Enter the data.

Enter the exam scores for each student in two separate columns:

Raw data in Excel in two columns

Step 2: Calculate the ranks for each exam score.

Next, we will calculate the rank for each exam score. Use the following formulas in cells D2 and E2 to calculate the Math and Science ranks for the first student, Austin:

Cell D2: =RANK.AVG(B2, $B$2:$B$11, 0)

Cell E2: =RANK.AVG(C2, $C$2:$C$11, 0)

Spearman rank correlation in Excel

Next, highlight the remaining cells to be filled in:

Spearman rank correlation calculation in Excel

Then click Ctrl+D to fill in the ranks for each student:

Spearman rank correlation in Excel example

Step 3: Calculate the Spearman Rank Correlation Coefficient.

Lastly, we will calculate the Spearman Rank Correlation Coefficient between Math scores and Science scores by using the CORREL() function:

Spearman rank correlation calculation in Excel output

The Spearman rank correlation turns out to be -0.41818.

Spearman rank correlation in Excel

Step 4 (Optional): Determine if the Spearman rank correlation is statistically significant.

In the previous step, we found the Spearman rank correlation between the Math and Science exam scores to be -0.41818, which indicates a negative correlation between the two variables.

However, to determine if this correlation is statistically significant, we would need to refer to a Spearman rank correlation table of critical values, which shows the critical values associated with various sample sizes (n) and significance levels (α).

If the absolute value of our correlation coefficient is greater than the critical value in the table, then the correlation between the two variables is statistically significant.

Spearman rank correlation table of critical values

In our example, our sample size was n = 10 students. Using a significance level of 0.05, we find that the critical value is 0.564.

Because the absolute value of the Spearman rank correlation coefficient that we calculated (0.41818) is not larger than this critical value, it means the correlation between Math and Science scores is not statistically significant.

Subscribe

- Never miss a story with notifications

- Gain full access to our premium content

- Browse free from up to 5 devices at once

Latest stories