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.
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.
- 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.
- As a consequence, we will be able to rank the Math Score like the following.
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).
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).
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.
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.
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!
