To answer “how the event of load shedding is related to the occurrence of a thunderstorm”, we can use many statistical methods to give an observation, For this, we need heaps of data collected from different places and analyze them to find any relation that can come handy in later use. **Spearman Correlation **is a method that helps us to reach a conclusion as to whether two events are related to each other or not efficiently. How we calculate the **Spearman Correlation** of two data arrays in Excel is explained here with adequate examples.

## What Is Spearman Correlation?

The **Spearman Correlation** is a derivative of the **Pearson Correlation Coefficient** in nonparametric form.

This value actually determines the linear correlation between two sets of data, often denoted by ** r_{s}** or

*ⲣ**.*

The **Pearson** **Product Moment Correlation** determines the linear relationship between continuous variables. The general expression of **Pearson Correlation** is:

Where **R _{X}** and

**R**are the values that are actually ranked already. And are the standard deviation of the datasets.

_{Y}**Spearman Correlation** actually evaluates the monotonic relationship between the values.

The complete form of **the Spearman Coefficient** is

This version is a slightly modified version of **Pearson’s** equation. Here,

**R**and_{x}**R**denote the rank of the x and y variables._{y}**R̅(x)**and**R̅(Y)**are the mean ranks.

In reality, the **Pearson coefficient** and **Spearman Correlation** are pretty close, if there is an instance of an outlier then you may need to use the **Spearman Correlation**.

**Use of Spearman correlation:**

- If your data has outliers and you are certain that they can influence the result. Then using the
**Spearman Correlation**is the wise decision. Because outliers can’t affect the**Spearman Correlation**as it does to**Pearson**correlation. because Spearman uses the rank of the values instead of actual values. - If the data are in a non-linear relationship or not fully distributed. Then spearman coefficient is better than the
**Pearson**coefficient. - If one of the variables is ordinal, then you better use the
**Spearman Correlation**than the**Pearson**coefficient.

Range of **Spearman Correlation** coefficient value ranges from +1 to 1.

- 1 indicates a perfect correlation with data. This means both datasets are matched.
- -1 indicates perfectly negative correlated data
- 0 denotes no existence of a correlation between data.

For the demonstration purpose, we are going to use the below dataset. In this dataset, two sets of data arrays containing column headers of Math and Economics are given. These two column values will be analyzed and the correlation between them will be computed.

## 1. Using Excel Formula to Calculate Spearman Correlation

One of the simple approximations of **Spearman Correlation** is the following:

where **d _{i}** is the difference between a pair of ranks

And **n **is the number of observations.

This formulation won’t work if there is tied value in ranking. we need to observe the ranking to see if this method is suitable for our dataset.

We first rank the values using the** RANK.AVG function **and use those ranks to calculate the **Spearman Correlation Coefficient**.

**Steps**

- At first, we need to rank the value of the columns
**Math**and**Economics**. - To do that, enter the following formula at cell
**E5**and press enter:

`=RANK.AVG(C5,$C$5:$C$14,0)`

- Then drag the
**Fill Handle**to cell**E14**. - Now you will notice that the values in the range of cells
**E5:E14**are currently ranked.

- Next, to rank the range of cells
**D5:D14**, enter the following formula at cell**F5**and press enter:

`=RANK.AVG(D5,$D$5:$D$14,0)`

- Then drag the
**Fill Handle**to cell**F14**. - Now you will notice that the values in the range of cells
**F5:F14**are currently ranked. - Now, if we closely monitor, the rank of both the Math and Economics column values does not contain any tied value, which means there is no value with the same rank.
- So we can proceed with our method without any issue to calculate the Spearman correlation in the Excel worksheet.

- Now we need to find the difference between the ranked value in each row.
- To do this, enter the following formula in cell
**G5**and press enter:

`=E5-F5`

** **

- Then drag the
**Fill Handle**to cell**G14**. - Now you notice the difference between all of the ranked values in each row showing in the range of cells
**G5:G14.**

** **

- Now we need to find the square of the difference between the ranked value in each row, calculated in the range of cells
**D5:D14.** - To do this, enter the following formula in cell
**H5**and press enter:

`=G5*G5`

- Then drag the
**Fill Handle**to cell**H14**. - Now you notice the square of the difference between all of the ranked values in each row showing in the range of cells
**H5:H14.**

- To get the sum of the range of cells
**H5:H15**, enter the following formula in cell**H15**:

`=SUM(H5:H14)`

- Now we have all of the necessary parameters needed for the calculation of Spearman correlation.
- Enter the number of entries in cell
**E16**, in this case, it is 10. - Enter the following formula in the cell
**E17:**

`=1-(6*H15)/(E16^3-E16)`

- You will get the
**Spearman Correlation**instantly. - Notice that the output is a negative value, which indicates a negative correlation between the two ranked data columns.

From the end result, it is evident that the value we got is negative. It indicates that the values in the **Math** column are negatively related to the **Economics** column. This means if the value of one column increases, another column will not increase, and vice versa.

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

## 2. Inserting CORREL Function to Compute Spearman Correlation

**The CORREL function** returns the correlation between two ranges of the cell value. With these values, you can determine the correlation between the two variables or ranges. The value ranges between -1 and +1. If the value is positive, then it indicates that if the value of the dataset increases, then the value of another dataset also increases, and vice versa. We also use the **RANK.AVG function** to rank the entries.

**Steps**

- At first, we need to rank the value of the columns
**Math**and**Economics**. - To do that, enter the following formula at cell
**E5**and press enter:

`=RANK.AVG(C5,$C$5:$C$14,0)`

- Then drag the
**Fill Handle**to cell**E14**. - Now you will notice that the values in the range of cells
**E5:E14**are currently ranked.

- Next, to rank the range of cells
**F5:F14**, enter the following formula at cell**F5**and press enter:

`=RANK.AVG(D5,$D$5:$D$14,0)`

- Then drag the
**Fill Handle**to cell**F14**. - Now you will notice that the values in the range of cells
**F5:F14**are now ranked.

- Then select the cell
**D17**and enter the following formula:

`=CORREL(E5:E14,F5:F14)`

- After entering the formula you will see that the Spearman correlation is now present in cell
**D17.**

**Read More:** How to Calculate P Value for Spearman Correlation in Excel

## 3. Calculating Spearman Correlation Using Graph in Excel

Using a scatter plot you can easily calculate the R squared value, then just square rooting the entire value can give you the **Spearman Correlation** value. But still, you may need to adjust the value sign slightly, depending on the slope of the **Trendline**. Before doing that, we need to rank our data using the** RANK.AVG function**. In this method, we are also using **the SQRT function**.

**Steps**

- We need to calculate the rank of the columns
**Math**and**Economics**. - To do this, we need to rank the value of the columns
**Math**and**Economics**. - To do that, enter the following formula at cell
**E5**and press enter:

`=RANK.AVG(C5,$C$5:$C$14,0)`

- Then drag the
**Fill Handle**to cell**E14**. - Now you will notice that the values in the range of cells
**E5:E14**are now ranked.

- Next, to rank the range of cells
**F5:F14**, enter the following formula at cell**F5**and press enter:

`=RANK.AVG(D5,$D$5:$D$14,0)`

- Then drag the
**Fill Handle**to cell**F14**. - Now you will notice that the values in the range of cells
**F5:F14**are now ranked.

- Now we need to create a scatter plot with the ranked two columns, in order to do this select both of the columns
**R**and_{Math}**R**._{Economics} - Then from the
**Insert**tab, go to**Scatter**from the**Charts**group and click on the**Scatter plot.**

- A new chart window will open and in that chart, you will see that
**R**column values along with the X-axis and_{Math }**R**values along with the Y-axis._{Economics}

- Now, click on the
**Chart Elements**icon on the side of the chart. - Then tick the
**Trendline**box to include a**Trendline**in the chart. - After ticking the box you will notice a downward-facing
**Trendline**in the chart.

- Then double-click on the chart, this will open a new format chart side window.
- In that window, click on the
**Trendline**options arrow, and a dropdown menu will appear. - From the dropdown menu, select the
**Trendline**which we just created.

- After that from the trendline option menu, click on the histogram-shaped icon.
- Then tick the box of
**Display R squared value on chart.**

- Now you are going to notice that the R-value is now displayed on the chart.
- Note down this value.

- Select cell
**E16**and enter the value of**R**.^{2} - Now we need to square root this
**R**value to get the value of^{2}**Spearman Correlation**. - On cell
**E18**enter the following formula:

`=SQRT(E16)`

- Then you need a slight adjustment in cell
**E18**to get the**Spearman Correlation**’s final value. To do this, you first notice the slope of the trendline. If it is downward, then alter the sign of cell**E18**. And if the slope is upward, then there is no need for any change in the sign. - In this case, the trendline is downward direction. so we need to alter the sign of the
**E18**cell value from 0.41821 to -0.4821. - And this is the final value of the
**Spearman Correlation**of the given dataset.

This negative value of the final value denotes the negative correlation between the data columns.

**Read More: **How to Find Correlation Coefficient in Excel Scatter Plot

**Download Practice Workbook**

Download this practice workbook below.

## Conclusion

To sum it up, the question “How to calculate Spearman correlation in Excel” is answered here in 3 different ways. If there is no tied value, then we can use a simplified version of the Spearman correlation function. **CORREL** function also can directly return the value of the **Spearman correlation coefficient. ** Another way is to make a scatter plot and calculate the coefficient value from that.

For this problem, a workbook is attached where you can practice these methods.

Feel free to ask any questions or feedback through the comment section.

## Related Articles

- How to Show Relationship Between Two Variables in Excel Graph
- How to Calculate Correlation Coefficient in Excel
- How to Find Coefficient of Determination in Excel
- How to Calculate Intraclass Correlation Coefficient in Excel

**<< Go Back to Excel Correlation | Excel for Statistics**** | Learn Excel**

Thanks for the tutorial. my question is how i can find or calculate the critical values to assess its significance?

Thanks for your question. Actually, you can approach this problem in two separate ways. One is directly calculating the significant value or using a chart. For the chart, you need to calculate the

T valuefirst, and then you will calculate thep-value. Using them, you can calculate the critical value from a chart available online.1. The formula for calculating the T value is,

t=r_s×√((n-2)/(1-r_s^2 ))Where

r_sis the Spearman correlation value.nis the no of entryThe Excel formula would be in our case

=E17*SQRT((E16-2)/(1-E17^2))2. The formula for significant value, p

=T.DIST.2T(ABS(calculated t value),n-2)3. To calculate the critical value, you need to have a critical value chart. Using the

p-valueand then(Number of entries), from the chart, you need to get the critical value.4. You may be needed to interpolate the critical values as you may not have the exact

pornvalues. If yourcorrelation value>critical value, then there is a significant correlation between the values. In other words, the correlation result is significant.