Ranking With RANK and PERCENTILE

Often you do not just want the value, you want its position within the group. Is this salesperson 1st or 12th? Is this score in the top 10%?

Two function families answer this: RANK tells you the ordinal position, and PERCENTILE tells you the value at a given cutoff. Together they turn raw numbers into rankings and benchmarks.

RANK.EQ gives a value's position within a range. You pass the value, the range, and an order flag.

• Order 0 (or omitted) ranks largest-first (descending).
• Order 1 ranks smallest-first (ascending).

Here the highest sales figure gets rank 1.

Notice the dollar signs: $B$2:$B$20. The range must stay fixed as you copy the formula down, while the value reference B2 shifts to B3, B4, and so on.

Forget the dollar signs and the range slides with each row, producing nonsense ranks. Absolute references on the range are essential here.

RANK.EQ gives tied values the same rank, then skips the next. If two values tie for 2nd, both show 2 and the next gets 4 (no 3rd place).

If you instead want the average rank for ties, use RANK.AVG. Two values tied for 2nd and 3rd would each show 2.5.

To force unique ranks even with ties, add a COUNTIF tiebreaker. It counts how many equal values appear above the current row and adds that offset.

This guarantees a clean 1, 2, 3, 4 sequence with no duplicates, which is useful for leaderboards.

PERCENTILE.INC answers: what value sits at a given percentage point? Pass the range and a fraction between 0 and 1.

The formula below returns the 90th percentile, the value below which 90% of the data falls. It is perfect for setting thresholds like "top 10% performers."

Quartiles split data into four parts. The QUARTILE.INC function is a shortcut: quartile 1 equals the 25th percentile, quartile 2 the 50th (the median), and quartile 3 the 75th.

The formula below returns the third quartile, identical to PERCENTILE.INC(A2:A101,0.75).

There are two flavors: PERCENTILE.INC (inclusive, accepts 0 and 1) and PERCENTILE.EXC (exclusive, only between 0 and 1, never the extremes).

For most everyday reporting, .INC is the standard choice. Use .EXC when following statistical conventions that exclude the boundary points.

RANK gives a position; PERCENTILE gives a value at a cutoff. PERCENTRANK.INC does the reverse of percentile: given a value, what percentile is it?

If a student scored 88 and PERCENTRANK returns 0.92, that student outperformed 92% of the group. Multiply by 100 to display it as a percent.

RANK and PERCENTILE work only on numbers. Text labels and blank cells inside the range are ignored, so a stray header will not break your ranking.

Be careful with numbers stored as text, though: they are skipped entirely, which shifts every rank and percentile. If a rank looks off, check that all your values are true numbers, not left-aligned text.

You have 20 reps with sales in column B. You want each rep's rank and whether they are in the top quartile.

• Rank: =RANK.EQ(B2,$B$2:$B$21,0)
• Top-quartile flag compares their sales to the 75th percentile.

The formula below returns Top or Standard for each rep based on the cutoff.

Test your understanding of ranking functions.

You learned to position values within a group:

• RANK.EQ gives ordinal position; order 0 = largest first, 1 = smallest first. Lock the range with $.
• RANK.AVG averages tied ranks; add COUNTIF for unique ranks.
• PERCENTILE.INC returns the value at a cutoff; QUARTILE.INC is a 25/50/75 shortcut.
• PERCENTRANK.INC reverses it: the percentile of a given value.