Rank and percentile in Excel: RANK.EQ, PERCENTILE and PERCENTRANK

RANK.EQ and RANK.AVG: syntax and ties

=RANK.EQ(number, ref, [order]) returns the position of number within ref. With order omitted or 0, the largest value is rank 1, as for scores, sales or votes. With order 1, the smallest value is rank 1, as for race times, costs or error rates. Non-numeric cells in ref are ignored. Lock the range with dollar signs, $B$2:$B$11, before you fill the formula down, or every row ranks against a different, shrinking list.

Ties share the better rank, and the following rank is skipped. Ben and Dan both scored 92, so both get rank 2 and nobody gets rank 3; Ava and Grace share rank 4, and Jonas is 6th. RANK.AVG gives tied values the average of the places they occupy: 2.5 for the 92s and 4.5 for the 88s. That is the version statistical rank tests and the Spearman correlation expect. The old RANK function still works and behaves like RANK.EQ. If every row needs a unique place, add a tie-breaker with COUNTIF, as shown in the top 10 list guide.

PERCENTILE.INC vs PERCENTILE.EXC

=PERCENTILE.INC(array, k) returns the k-th percentile for k from 0 to 1. Excel sorts the values and finds position (n − 1) × k + 1, interpolating between neighbours. For ten scores and k = 0.9 the position is 9 × 0.9 + 1 = 9.1, so the result lies one tenth of the way from the 9th value (92) to the 10th (95): 92 + 0.1 × 3 = 92.3. With k = 0 and k = 1 it returns the minimum and the maximum.

=PERCENTILE.EXC(array, k) uses position (n + 1) × k instead: 11 × 0.9 = 9.9, so 92 + 0.9 × 3 = 94.7. The exclusive method never returns the minimum or maximum and gives #NUM! for k below 1/(n + 1) or above n/(n + 1), with ten values below 0.0909 or above 0.909. On small samples the two can differ a lot, on large ones they converge. The old PERCENTILE equals PERCENTILE.INC, MEDIAN equals PERCENTILE.INC at 0.5 (here 86), and QUARTILE.INC(array, 1) equals the 25th percentile (76.5). Pick one method, write it next to the result and use it consistently.

PERCENTRANK: where one value stands

PERCENTILE answers "which score marks the top 10%?", PERCENTRANK answers "what share of the class is below this score?". =PERCENTRANK.INC(array, x, [significance]) divides the number of values below x by n − 1 when x is in the list. Five scores lie below 88, so the percent rank is 5 ÷ 9 = 0.5556. Excel truncates the result to three significant digits by default and returns 0.555, not 0.556. Pass a fourth digit as significance, PERCENTRANK.INC($B$2:$B$11,B2,4), if you need 0.5555.

With the inclusive version the lowest value gets 0% and the highest 100%. PERCENTRANK.EXC uses (values below + 1) ÷ (n + 1) and stays strictly between 0 and 1: 6 ÷ 11 = 0.545 for the 88. If x lies between two values of the list, both functions interpolate; if it lies outside the range they return #N/A.

Worked example: ranks, percent ranks and percentiles of ten scores

The scores are inputs (yellow); there are two ties, 92 and 88. Columns C to F are filled down, the block below calculates the cut-offs. The Top 25% flag compares each score with the 75th percentile in F14.

Worked example: ranks, percent ranks and percentiles of ten scores

	A	B	C	D	E	F
1	Student	Score	RANK.EQ	RANK.AVG	PERCENTRANK.INC	Top 25%?
2	Ava	88	4	4.5	55.5%	No
3	Ben	92	2	2.5	77.7%	Yes
4	Chloe	75	8	8.0	22.2%	No
5	Dan	92	2	2.5	77.7%	Yes
6	Eli	68	10	10.0	0.0%	No
7	Finn	81	7	7.0	33.3%	No
8	Grace	88	4	4.5	55.5%	No
9	Hugo	95	1	1.0	100.0%	Yes
10	Ivy	70	9	9.0	11.1%	No
11	Jonas	84	6	6.0	44.4%	No
12
13	Cut-offs and statistics					Value
14	75th percentile = top-25% cut-off (PERCENTILE.INC)					91.0
15	90th percentile, PERCENTILE.INC					92.3
16	90th percentile, PERCENTILE.EXC					94.7
17	Median (= 50th percentile)					86.0
18	First quartile (QUARTILE.INC, 25th percentile)					76.5
19	Students at or above the cut-off					3
20	Eli's rank from the bottom (order = 1)					1
21	Percent rank of Ava's 88 with PERCENTRANK.EXC					54.5%

Fictional test scores. Statistics packages and textbooks use several percentile definitions, so other tools may return slightly different cut-offs for the same data.

Step by step: a top-25% flag

To mark the top quarter, calculate the cut-off once and compare every row with it. The 75th percentile is at position 9 × 0.75 + 1 = 7.75: three quarters of the way from 88 to 92, so 91. The flag is =IF(B2>=$F$14,"Yes","No"), with the cut-off cell locked. Ben, Dan and Hugo qualify, and =COUNTIF(B2:B11,">="&F14) confirms three students.

With ties, a "top 25%" group rarely holds exactly 25% of the rows: if the cut-off falls on a tied value, all students with that value are in or out together. That is usually fair, but say so in the report. An equivalent test is PERCENTRANK.INC(...) >= 0.75; for bands such as quartiles or quintiles, compare each value with several percentile cut-offs in ascending order.

Ascending ranks, ranks within groups and conditional percentiles

For lists where lower is better, set order to 1: =RANK.EQ(B6,B2:B11,1) gives Eli's 68 rank 1 from the bottom. To rank within a group, such as each class or each region, count the larger values in the same group: =COUNTIFS($A$2:$A$11,A2,$B$2:$B$11,">"&B2)+1, where column A holds the group. This returns the same result as RANK.EQ, restricted to the group.

A percentile for one group only works with IF inside the function: =PERCENTILE.INC(IF($A$2:$A$11="Class A",$B$2:$B$11),0.9). In Microsoft 365 it calculates directly; in older versions confirm it with Ctrl+Shift+Enter. IF returns FALSE for the other rows, and PERCENTILE ignores those values.

Which function answers which question

"What place is this?" → RANK.EQ (or RANK.AVG for statistics). "Which value is the threshold for the top X%?" → PERCENTILE.INC, or QUARTILE.INC for quarters. "What share of the data lies below this value?" → PERCENTRANK.INC. "What is the middle value?" → MEDIAN. The first and third answer a question about one row, the second about the whole distribution, which is why the cut-off belongs in its own cell and the rank in a column.

Common mistakes

• Not locking the range. =RANK.EQ(B2,B2:B11) filled down becomes RANK.EQ(B3,B3:B12), and the ranks no longer refer to the whole list. Use $B$2:$B$11.
• Expecting no gaps after ties. RANK.EQ skips places after a tie: two players in 2nd place mean there is no 3rd. Use a tie-breaker or a dense rank if you need consecutive numbers.
• Mixing INC and EXC. PERCENTILE.INC and PERCENTILE.EXC give different cut-offs on small samples, 92.3 vs 94.7 here. State the method and do not mix them across reports.
• Reading PERCENTRANK as rounded. It truncates to three significant digits by default, so 0.5556 is shown as 0.555. Use the significance argument for more digits.
• Forgetting the order argument. Without order = 1 the fastest time or the lowest cost gets the worst rank.
• Entering k as a whole number. PERCENTILE.INC(B2:B11, 90) returns #NUM!; k must be between 0 and 1, so use 0.9 or 90%.