How to Calculate Spearman Rank Correlation in Excel?

Before following the procedure to calculate the spearman coefficient, we need to understand two functions in excel, which will be helpful in calculating the coefficient.

Rank Function

=RANK.AVG(number, ref, order)

The rank specifies the rank of a given number in a dataset; one can also select the order in which rank has to appear. =RANK.AVG() takes three arguments: number, ref, and order.

Argument 1: Number is the first argument in the rank function, which specifies for which number rank has to be estimated.

Argument 2: Reference is the second argument in the rank function. One needs to provide the absolute range of the data set.

Argument 3: Order is the third argument in the rank function. The order can be either ascending(1) or descending(0).

Correl function

=CORREL(array1, array2)

Similar to the spearman rank correlation coefficient, we also have the Pearson correlation coefficient. Pearson correlation coefficient is a parametric test to calculate the correlation value of two variables. Both the test is nearly the same. Just the difference lies in that spearman works on ranks of the data, and Pearson works on the actual data. The =CORREL() function calculates the Pearson correlation coefficient. This could be very useful in finding the spearman correlation coefficient, which we will talk about in the later stage of the article. =CORREL() function takes two arguments, array1 and array2.

Argument 1: Array1 is the first argument in the correlation function. It takes the entire data set of the variable1.

Argument 2: Array2 is the second argument in the correlation function. It takes the entire data set of the variable2.

We have noticed a general trend that with an increase in the height of a person, its weight also increases. This happens because there is a positive correlation between height and weight. As one variable increases, the other one also increases, but with this, we only get the quality measure of the data and not quantity, that by how much they are related. To solve this problem, we have a Spearman Rank Correlation coefficient whose value will tell by how two variables are related. In this article, we will learn how to calculate Spearman Rank Correlation Coefficient in excel.