How to Calculate Spearman Rank Correlation in Google Sheets

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 Google Sheets.

Example: Spearman Rank Correlation in Google Sheets

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:

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:

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

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

Next, highlight the remaining cells to be filled in:

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

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 in Google Sheets

The Spearman rank correlation turns out to be -0.41818.

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.

Related: How to Calculate Spearman Rank Correlation in Excel

3 Replies to “How to Calculate Spearman Rank Correlation in Google Sheets”

  1. How would you calculate the P-Value when your N is a lot larger?

    I understand that for large n you could use the Pearson P-value. So in Google sheets you would use T.Test. But do you use the original data columns or the ranked data columns for this?

  2. Great writeup and explanation here. There is something I am wanting to do with it but wanted to run this by you first.
    I am trying to compare PGA events, where every year there is a PGA event, and a listing of players who rank 1st to 150th ish. The difference in what you posted is that I am trying to find correlation between the PGA event itself and not necessarily the players. So if the top 20 names at one PGA event have many similar names in the top 20 at a second event, they would be correlated.
    Would this method be useful for this scenario?

Leave a Reply

Your email address will not be published. Required fields are marked *