How to Calculate P Value for Spearman Correlation in Excel

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.

Download Practice Workbook

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

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(r_s) and p value. Spearman correlation is often denoted as ρ or r_s like the following form.

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

Spearman correlation coefficient(r_s) :

The Spearman correlation coefficient (r_s) 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.

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:

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.

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

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.

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

Read More: How to Find Correlation between Two Variables 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:

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

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

Read More: How to Calculate Correlation Coefficient in Excel (3 Methods)

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:

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

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

Read More: How to Do Correlation in Excel (3 Easy Methods)

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:

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.

Read More: How to Find Spearman Rank Correlation Coefficient in Excel (2 Ways)

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:

Here,  I5 is the number of pairs.

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

Read More: How to Calculate Pearson Correlation Coefficient in Excel (4 Methods)

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:

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.

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.

Read More: How to Calculate Cross Correlation in Excel (2 Quick Ways)

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.

Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions. Keep learning new methods and keep growing!

Related Articles

• Calculate Autocorrelation in Excel (2 Ways)
• How to Make a Correlation Scatter Plot in Excel (2 Quick Methods)
• Interpret Correlation Table in Excel (A Complete Guideline)
• How to Make a Correlation Matrix in Excel (2 Handy Approaches)
• Make Correlation Graph in Excel (with Easy Steps)
• How to Calculate Intraclass Correlation Coefficient in Excel