A measured tie table for RANK.EQ vs RANK.AVG, plus the COUNTIF ranking method that works in every spreadsheet. Live, editable, free.
RANK is the legacy name; RANK.EQ is identical, RANK.AVG averages ties. The portable equivalent is COUNTIF(ref,">"&number)+1 for descending — measured equal to RANK.EQ on this grid.
RANK returns a value's position in a list. Excel 2010 split it into RANK.EQ (competition: ties share the top rank) and RANK.AVG (ties share the average). The live lab ranks with the universal COUNTIF method — measured equal to RANK descending — so you can edit scores and watch the leaderboard move. Click any row to load it.
| Expression | Result | Why |
|---|---|---|
| =COUNTIF(B2:B8,">"&B2)+1 | 4 | Rank of 88 descending (3 scores higher) |
| =COUNTIF(B2:B8,">"&B3)+1 | 2 | Rank of 95 (tied with Dave) |
| =COUNTIF(B2:B8,">"&B6)+1 | 7 | Rank of 60 — the lowest score |
| =COUNTIF(B2:B8,">"&B4)+1 | 6 | Rank of 72 |
| =COUNTIF(B2:B8,"<"&B2)+1 | 3 | Ascending rank of 88 |
| =COUNTIF(B2:B8,"<"&B6)+1 | 1 | Ascending rank of 60 — lowest is 1 |
| =SUMPRODUCT(--(B2:B8>B2))+1 | 4 | Rank of 88 via SUMPRODUCT — equals the COUNTIF method |
| =COUNTIF($B$2:$B$8,">"&B2)+1 | 4 | Locked range — fill-down safe |
| Score | RANK / RANK.EQ (desc) | RANK.AVG (desc) | RANK (asc) |
|---|---|---|---|
| 100 (Grace) | 1 | 1 | 7 |
| 95 (Bob, Dave) | 2 (tie) | 2.5 | 5 (tie) |
| 88 (Alice, Frank) | 4 (tie) | 4.5 | 3 (tie) |
| 72 (Carol) | 6 | 6 | 2 |
| 60 (Eve) | 7 | 7 | 1 |
| Expression | Result | Why |
|---|---|---|
| =COUNTIF(B2:B8,">"&B2)+1 | 4 | Three scores beat 88 (95,95,100), so 3+1=4 — competition rank |
| =COUNTIF(B2:B8,"<"&B2)+1 | 3 | Ascending: two scores below 88 (72,60), so 2+1=3 |
| RANK.AVG(95,…) | 2.5 | The two 95s average positions 2 and 3 |
| RANK.EQ(95,…) | 2 | Both 95s take the top tied rank; next rank skips to 4 |
=COUNTIF($B$2:$B$8,">"&B2)+1 for descending, "<" for ascending. Lock the range with $ to fill down. It runs in Excel, Google Sheets, LibreOffice and engines without RANK — measured equal to RANK.EQ here.Edit the scores and watch the ranks recalculate. The gallery uses the portable COUNTIF method (measured equal to RANK); native RANK.EQ/RANK.AVG tie behaviour is in the static table above.
10 failure patterns mined from actual user reports on Reddit, Stack Overflow and Super User — each with the authentic symptom, root cause, a fix, and a runnable formula. Not textbook theory.
RANK works in every Excel version; RANK.EQ/RANK.AVG arrived in 2010. The COUNTIF ranking method in this lab is universal — it runs even where RANK itself is unavailable.
| Platform | Support | Notes | Gotcha |
|---|
RANK is one piece. The full course walks the toolkit end to end with live, measured examples.