Statistical
Tie-Averaged Fractional Rank vs. Competition Rank (RANK.AVG & RANK.EQ Formula)
Assign midpoint fractional ranks (such as 2.5 for a two-way tie at 2nd and 3rd place) using RANK_EQ plus a COUNTIF tie-span adjustment so rank sums stay invariant.
Why tied ranks need midpoint averaging in statistics and prize splits
RANK_EQ assigns every tied value the top position of its tie group (1, 2, 2, 4, 4, 4), which lowers the total sum of ranks whenever ties occur. Non-parametric tests such as Spearman rank correlation and Wilcoxon rank-sum require RANK.AVG, where m tied observations spanning positions r through r + m - 1 receive their arithmetic mean r + (m - 1) / 2.
Adding (COUNTIF(range, value) - 1) / 2 to RANK_EQ(value, range, order) works for both descending scores (order = 0) and ascending race times (order = 1), preserving the exact N * (N + 1) / 2 total rank sum.
| Candidate | Score | RANK_EQ (Competition) | Tie Count (m) | RANK.AVG (Midpoint) |
|---|---|---|---|---|
| Aria | 95 | 1 | 1 | 1.0 |
| Ben | 88 | 2 | 2 (pos 2–3) | 2.5 |
| Chloe | 88 | 2 | 2 (pos 2–3) | 2.5 |
| Devon | 82 | 4 | 3 (pos 4–6) | 5.0 |
| Elena | 82 | 4 | 3 (pos 4–6) | 5.0 |
| Farah | 82 | 4 | 3 (pos 4–6) | 5.0 |
Exact RANK.AVG Midpoint Formula (Descending Order)
Computes the competition rank in B2:B7 and adds half of (tie_count - 1) so tied scores share their average rank.
=RANK_EQ(B3, $B$2:$B$7, 0) + (COUNTIF($B$2:$B$7, B3) - 1) / 2Rank-Sum Conservation Audit (N * (N + 1) / 2)
Verifies that the sum of midpoint ranks across all 6 rows equals 6 * 7 / 2 = 21 regardless of ties.
=SUM(D2:D7)Try the example
Use the original values or change them to test your understanding.