Statistical
Spearman Rank Correlation (rho) formula: outlier-resistant CORREL on ranks
Measure monotonic non-linear relationships and resist extreme outliers by computing Pearson CORREL across RANK columns.
Why Spearman rho spots perfect monotonic trends that Pearson r underestimates!
Spearman correlation is Pearson correlation of the paired average ranks. Use the same rank direction for both columns and keep the observations paired.
RANK alone gives tied observations the minimum rank. Add (COUNTIF(range,value)-1)/2 to obtain their average rank, so ties are handled correctly.
The seeded series are strictly increasing and therefore have rho=1 even though their raw Pearson correlation is 0.7559. They are illustrative nonlinear values, not an exact exponential curve. Ranks reduce sensitivity to magnitudes but do not make the statistic immune to influential observations.
Use complete numeric pairs. A constant ranked column has zero variance, so correlation is undefined. Correlation does not establish causation.
| X (A) | Nonlinear Y (B) | RankX (C) | RankY (D) | Comparison |
|---|---|---|---|---|
| 1 | 2 | 5 | 5 | Linear Pearson r = 0.7559 |
| 2 | 8 | 4 | 4 | Spearman Rank rho = 1.0000! |
| 3 / 4 / 5 | 25 / 90 / 1000 | 3 / 2 / 1 | 3 / 2 / 1 | Swap Y2<->Y3 -> rho = 0.9000 |
Spearman Rank Correlation (CORREL on ranks) vs linear Pearson r
Correlate the average-rank helpers, including their tie adjustment.
=ROUND(CORREL(C2:C6, D2:D6), 4)Compare against raw linear Pearson correlation on skewed values
Shows how one 1,000 outlier pulls linear CORREL(A2:A6, B2:B6) down to 0.7559.
=ROUND(CORREL(A2:A6, B2:B6), 4)Average ranks when values tie
Enter this in C2 and fill down. For D2:D6, substitute column B for A.
=RANK(A2,$A$2:$A$6)+(COUNTIF($A$2:$A$6,A2)-1)/2Try the example
Use the original values or change them to test your understanding.