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 rs or ⲣ.
The Pearson Product Moment Correlation determines the linear relationship between continuous variables. The general expression of Pearson Correlation is:
Where RX and RY are the values that are actually ranked already. And are the standard deviation of the datasets.
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,
- Rx and Ry denote the rank of the x and y variables.
- 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 di 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 RMath and REconomics.
- 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 RMath column values along with the X-axis and REconomics values along with the Y-axis.
- 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 R2.
- Now we need to square root this R2 value to get the value of 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
Get FREE Advanced Excel Exercises with Solutions!
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 value first, and then you will calculate the p-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_s is the Spearman correlation value.
n is the no of entry
The 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-value and the n (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 p or n values. If your correlation value>critical value, then there is a significant correlation between the values. In other words, the correlation result is significant.