Rank Correlation
| Assuming two samples of paired data in ranges x and y with no ties: | ||
|---|---|---|
| Rank of ith value in range x | =RANK(INDEX(x, i), x, 1) | |
| Assuming two samples of paired data in ranges x and y with some tied values: | ||
| Rank of ith value in range x | =(RANK(INDEX(x, i), x, 1)- RANK(INDEX(x, i), x, 0)+COUNT(x)+1)/2 | |
| Assuming that the ranges rx and ry contain the ranks of the data in x and y respectively: | ||
| Spearman rank correlation coefficient, rs | = CORREL(rx, ry) | |
In the example above:
| D2: | =RANK(B2, $B$2:$B$7, 1) | copy down to D7 |
| E2: | =RANK(B2, $B$2:$B$7, 0) | copy down to E7 |
| F2: | =(D2-E2+COUNT($B$2:$B$7)+1)/2 | copy down to F7 |
| F9: | =CORREL(C2:C7, F2:F7) | adjusted for ties |