After totals and averages, the next question a list gets asked is almost always positional: top five customers, three worst months, the second-biggest order, are we in the top 10%? Rank questions. Excel has a compact family for them, plus one modern idiom that has quietly replaced half the old ways — and, as usual, a couple of honesty rules about what a rank actually means.
The family
MAX and MIN
you know. Their generalisation:
=LARGE(Sales[Amount], 2) second biggest
=SMALL(Sales[Amount], 3) third smallest
LARGE(range, k) is “the k-th biggest” — so LARGE(..., 1) is
MAX, and a top-five block is LARGE against k = 1…5 (feed it
SEQUENCE(5) and it spills
all five at once).
The other direction — where does this value stand? — is:
=RANK.EQ([@Amount], Sales[Amount]) → 7 (7th biggest)
And percentile standing, for “top 10%” questions:
=PERCENTRANK.INC(Sales[Amount], [@Amount]) → 0.93
— this value beats 93% of the list. The reverse,
=PERCENTILE.INC(range, 0.9), gives the threshold the top
10% clears — the honest way to set a cut-off, and the natural
partner of a banding table.
The modern idiom: the live top-five table
The old way to show “top five customers” was LARGE for the values and a fragile INDEX/MATCH to fetch the names. The modern way is one readable formula:
=TAKE(SORT(Customers, 2, -1), 5)
Sort the table by column two, descending; take the first five rows. Names, values, whole rows — spilled, and live: new data in, the league table re-ranks itself. This block is the beating heart of half the one-page dashboards worth building, and it retires an entire generation of folklore.
The honesty rules of ranking
Ties are a decision. Two customers on 4,180: RANK.EQ gives
both rank 2 and skips rank 3 — standard sports ranking, but
say so, or add a tiebreaker column (rank by amount, then
alphabetically) so the league table is deterministic. The
old LARGE-then-MATCH folklore breaks on ties — it fetches the
same name twice — which is one more reason the SORT idiom wins.
Top-five hides the shape. A podium where first place is 4,180 and fifth is 4,020 tells a different story from one where fifth is 300 — and the podium alone can’t say which. Pair rank tables with the whole distribution (next lesson’s histogram) or at least a share-of-total column (the percent pattern): “top five = 71% of revenue” is a finding; “here are five names” is a list.
Percentiles beat averages for skewed data. “Average order
£412” and “median order £96” describe the same list when a few
whales dominate — MEDIAN is PERCENTILE(…, 0.5), and for
anything money-shaped it’s usually the truer centre. When the
two disagree wildly, that disagreement is the interesting fact.
Rank, threshold, share, shape — four small tools, one instinct: position questions deserve real functions, not a sort and a squint. The squint doesn’t update on refresh. The formulas do.