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