Statistical · ranking · Nov 2026

The RANK function,
measured & explained.

A measured tie table for RANK.EQ vs RANK.AVG, plus the COUNTIF ranking method that works in every spreadsheet. Live, editable, free.

D1
fx
=RANK(B2, B2:B8, [order])
0
measured gallery rows
0
sourced errors fixed
0
platforms checked
// 01 — Anatomy

How RANK works

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(number, ref, [order])
numberRequired. The value whose rank you want.
refRequired. The list of numbers to rank against; text and blanks are ignored.
orderOptional. 0/omitted = descending (largest is 1); 1 = ascending.
.bool-moat td.res-text{color:var(--cyan);font-weight:700;font-family:var(--mono)}
// 02 — Ranking & ties

What RANK does on a tie

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.

Measured ranking gallery (COUNTIF method)

ExpressionResultWhy
=COUNTIF(B2:B8,">"&B2)+14Rank of 88 descending (3 scores higher)
=COUNTIF(B2:B8,">"&B3)+12Rank of 95 (tied with Dave)
=COUNTIF(B2:B8,">"&B6)+17Rank of 60 — the lowest score
=COUNTIF(B2:B8,">"&B4)+16Rank of 72
=COUNTIF(B2:B8,"<"&B2)+13Ascending rank of 88
=COUNTIF(B2:B8,"<"&B6)+11Ascending rank of 60 — lowest is 1
=SUMPRODUCT(--(B2:B8>B2))+14Rank of 88 via SUMPRODUCT — equals the COUNTIF method
=COUNTIF($B$2:$B$8,">"&B2)+14Locked range — fill-down safe

The tie table — native RANK.EQ vs RANK.AVG (static, documented Excel behaviour)

ScoreRANK / RANK.EQ (desc)RANK.AVG (desc)RANK (asc)
100 (Grace)117
95 (Bob, Dave)2 (tie)2.55 (tie)
88 (Alice, Frank)4 (tie)4.53 (tie)
72 (Carol)662
60 (Eve)771
ExpressionResultWhy
=COUNTIF(B2:B8,">"&B2)+14Three scores beat 88 (95,95,100), so 3+1=4 — competition rank
=COUNTIF(B2:B8,"<"&B2)+13Ascending: two scores below 88 (72,60), so 2+1=3
RANK.AVG(95,…)2.5The two 95s average positions 2 and 3
RANK.EQ(95,…)2Both 95s take the top tied rank; next rank skips to 4
⚠ RANK is legacy. Microsoft recommends RANK.EQ for new work (identical behaviour) and RANK.AVG for averaged ties. All three ignore text and blanks in the reference range. RANK itself returns #NAME? in HyperFormula — this lab uses the portable COUNTIF method instead.
Portable rank: =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.
// 03 — Try it live

Interactive RANK playground

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.

A1Sample data — click any cell to edit, the formula re-evaluates live
Result
—
✓ readyHyperFormula 3.5
// 04 — Error library

The real-world error library

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.

// 05 — Cross-platform

Where RANK works

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.

PlatformSupportNotesGotcha
✓ Supported full RANK◐ Partial works with caveatsCompiled from public docs · verify against your build
// Go deeper

Master ranking and ties

RANK is one piece. The full course walks the toolkit end to end with live, measured examples.

  • RANK / RANK.EQ / RANK.AVG — the tie rules
  • The portable COUNTIF ranking method
  • Competition vs average vs dense ranks
  • Group-wise ranking with COUNTIFS
Free while in beta
Browse the function library

No account needed. Every page has a live playground.

// 07 — FAQ

Frequently asked