How to calculate the spearman correlation in excel?

Asked By Pia Gidget 20 points N/A Posted on - 05/27/2020

Hi, can anyone tell me whether I can calculate spearman correlation in excel? If yes, then please let me know how to calculate.

• Status:
• Question Views: 57
• Answer Count: 1
• Vote Up 0 Vote Down

• Answer Accepted: Yes
• Question Category: MS Excel

Best Answer by

Go To Solution

Answered By Lloyd Feld 0 points N/A #328720

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.

About Lloyd Feld

Questions
1

Answers
11

Best Answers
5

• Vote Up 0 Vote Down

• Posted on - 05/27/2020
• Question Category: MS Excel