How to Calculate P Value for Spearman Correlation in Excel

Get FREE Advanced Excel Exercises with Solutions!

If you are looking for a special trick to calculate p value for Spearman correlation in Excel, you’ve come to the right place. In Microsoft Excel, there is one way to calculate p value for Spearman correlation in Excel. In this article, we’ll discuss one method to calculate p value for Spearman correlation in Excel. Let’s follow the complete guide to learn all of this.


What Is Spearman Correlation?

Spearman correlation test measures the strength and direction of a monotonic association between two ranked variables. Through this Spearman correlation test, one can calculate the Spearman correlation coefficient(rs) and p value. Spearman correlation is often denoted as ρ or rs like the following form.

Here, {di​} is the difference between the ranks in i-th sample.

Spearman correlation coefficient(rs) :

The Spearman correlation coefficient (rs) can have a value between +1 and -1

  • +1 means a perfect ranking association
  • 0 means no rank association
  • -1 indicates a perfect negative correlation between two ranks

P Value:

A p value is a probability of obtaining the observed difference(or a larger one) in the outcome measures, given that no difference exists between the data containing paired samples.

Assumptions of a Spearman Correlation Test

  • Random sample
  • A monotonic association exists
  • Variables are at least ordinal
  • Data contains paired samples
  • Independence of observations

Necessary Steps to Calculate P Value for Spearman Correlation in Excel

We will use one effective and tricky method to calculate the p-value for Spearman correlation in Excel. This section provides extensive details on one method. Let’s walk through the steps to calculate the p value for Spearman correlation in Excel.


Step 1: Calculate the Rank of the Variables

Here, we have a dataset containing two variables Biology Score and Math Score. Now we are going to rank the data in ascending order. Here, we will use the RANK.AVG function to calculate the rank of the variables.

How to Calculate P Value for Spearman Correlation in Excel

You have to follow the following steps to calculate the rank of the variables.

📌 Steps:

  • Firstly, we will use the following formula in the cell D5:

=RANK.AVG(C5,$C$5:$C$16,1)

Here, the first data value C5 is what we are aiming to rank, $C$5:$C$16 is the range of the data that is required as a reference. By entering 1, we rank the value in ascending order.

  • Then, press Enter and drag the Fill handle icon.

How to Calculate P Value for Spearman Correlation in Excel

  • As a consequence, we will be able to rank the Biology Score like the following.

  • Next, we will use the following formula in the cell E5:

=RANK.AVG(D5,$D$5:$D$16,1)

Here, the first data value D5 is what we are aiming to rank, $D$5:$D$16 is the range of the data that is required as a reference. By entering 1, we rank the value in ascending order.

  • Then, press Enter and drag the Fill handle icon.

How to Calculate P Value for Spearman Correlation in Excel

  • As a consequence, we will be able to rank the Math Score like the following.

How to Calculate P Value for Spearman Correlation in Excel

Read More: How to Find Spearman Rank Correlation Coefficient in Excel


Step 2: Calculate Correlation Coefficient

Here, we will use the CORREL function to calculate the Spearman correlation coefficient. The CORREL function returns the correlation coefficient between two data sets.

Now, we will use the following formula in the cell I4:

=CORREL(E5:E16,F5:F16)

Here, E5:E16 is the range of the Cells of column Rank (Biology Score) and F5:F16 is the range of the cells of Rank (Math Score).

How to Calculate P Value for Spearman Correlation in Excel

Thus, we get the  Spearman correlation coefficient(rs) is 0.9790 which means an almost perfect ranking association.


Step 3: Calculate N Value

By using the COUNT function we will determine the number of pairs(N). The COUNT function counts the number of cells in a range that contains numbers.

Now, we will use the following formula in the cell I5:

=COUNT(E5:E16)

Here, E5:E16 is the range of the Cells of column Rank (Biology Score).

How to Calculate P Value for Spearman Correlation in Excel

Thus, in the above dataset, we can see that the value of N is 12 which means there are twelve pairs in the dataset.

Read More: How to Calculate Pearson Correlation Coefficient in Excel


Step 4: Estimate T Statistic

Here, we are going to calculate the t statistic by using the Spearman correlation coefficient and the number of pairs. Here we combine ABS and SQRT functions to estimate t statistic.

Now, we will use the following formula in the cell I6:

=(ABS(I4)*SQRT(I5-2))/(SQRT(1-ABS(I4)^2))

Here, the ABS function returns the absolute value of a number, a number without its sign and the ABS function returns the square root of a number. Next, I4 is the Spearman correlation coefficient and  I5 is the number of pairs.

Thus, in the above dataset, we find the t value of 15.190 which enables us to calculate the p value.


Step 5: Calculate Degree of Freedom (DF)

Next, we will calculate the degree of freedom to find out the p value.

Now, we will use the following formula in the cell I7:

=I5-2

Here,  I5 is the number of pairs.

How to Calculate P Value for Spearman Correlation in Excel

Thus, we find the number of degrees of freedom is 10.


Step 6: Calculate P Value

Now, we will calculate the p value using the TDIST function.

We will use the following formula in the cell I7:

=TDIST(I6,I7,2)

Here, I6 is the t statistic value and I7 is the degree of freedom. Then, we enter 2 for the purpose of a two-tailed analysis.

How to Calculate P Value for Spearman Correlation in Excel

Here, in the above calculation, we get p value of 3.0898E-08. We know that if p>0.05 it will indicate the Null hypothesis which means there is no correlation between the Biology and Math scores in the overall population and if p<0.05, it will indicate the alternative hypothesis. This p value indicates the alternative hypothesis that means there is a correlation between the Biology and Math scores in the overall population because p<0.05.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

That’s the end of today’s session. I strongly believe that from now you may be able to calculate p value for Spearman correlation Excel. So, if you have any queries or recommendations, please share them in the comments section below.

Keep learning new methods and keep growing!


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Saquib Ahmad Shuvo
Saquib Ahmad Shuvo

Welcome to my Profile. I am working on and researching Microsoft Excel right now, and I will be posting articles about it here. I received a B.Sc. in Naval Architecture and Marine Engineering from the Bangladesh University of Engineering and Technology (BUET). Having studied naval architecture, I have a strong interest in research and development. Always try to learn from different sources and come up with creative solutions.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo