How to calculate the spearman correlation in excel?

How to calculate the spearman correlation in excel?

There is no direct way of calculating spearman correlation in excel. But there is a process using which you can easily do that. Follow the steps –

Consider the above data.

1 – Type “=RANK.AVG(B2,$B$2:$B$11,0)” in D2 and drag it up to D11 to rank the                      physical activity variable.

2 – Type “=RANK.AVG(C2,$C$2:$C$11,0)” in E2 and drag it up to E11 to rank the                      blood pressure variable.

RANK.AVG ranks your source data.

Following will be the result –

3. Now you need to use the CORREL functionto calculate spearman correlation in excel.

4 .Type “=CORREL(D2: D11, E2: E11)” in any cell and hit enter. You will get the                         desired results.