Statistical Analysis with Excel For Dummies
Book image
Explore Book Buy On Amazon
Spearman’s correlation coefficient, rS, was the earliest non-parametric test based on ranks. For a sample of individuals each measured on two variables in Excel , the idea is to rank each score within its own variable. Then, for each individual subtract one rank from the other. If correlation is perfect (in the positive direction), all the differences are zero.

The image below shows an example. An industrial psychologist rated the sociability of 20 employees of the FarDrate Timepiece Corporation. The scale ranged from 1 (least sociable) to 100 (most sociable). Each FarDrate employee also rated his or her job satisfaction on a scale of 1 (least satisfaction) to 80 (most satisfaction). The null hypothesis is that sociability is not correlated with job satisfaction. The alternative hypothesis is that these two variables are correlated.

The data are in columns B and C, and the ranks are in columns E and F. The differences between each pair of ranks are in column G.

Spearman’s rS.

The formula is


where d is an interpair difference. As is the case with the regular correlation coefficient, if the null hypothesis is true, the value of rS should be around zero.

To calculate the ranks in column E, type


into E2 and autofilled. For the ranks in column E, type


into F2 and autofilled.

You don’t have to type a complicated Excel formula into cell J4 to calculate the correlation coefficient. Why? Because Excel and mathematical statistics team up for a swell surprise: All you have to do is type


into J4. That’s all there is to it. Using CORREL on the ranks gives the same answer as the formula above. (So it isn’t really necessary to calculate the interpair rank differences in column G.)



N is the number of pairs, and the test has N-2 degrees of freedom.

You can define Number_of_pairs as the name for the value in cell I2. So type


into J6 and


into J7. You can use the two-tailed t distribution function if you don’t know the correlation’s direction in advance. And once again, the low p-value tells you to reject the null hypothesis.

About This Article

This article is from the book:

About the book author:

Joseph Schmuller works on the Digital & Enterprise Architecture Team at Availity. He has taught statistics at the undergraduate and graduate levels. He has created and delivered courses for LinkedIn Learning, and he is the author of all previous editions of Statistical Analysis with Excel For Dummies.

This article can be found in the category: