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.

**Table of Contents**hide

## 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:**

`=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 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:**

`=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(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:**

`=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 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:**

`=(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.

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

`=I5-2`

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

`=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.

**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**