Math and statistics Updated November 2026

RANK

RANK.EQ(number, ref, [order]) returns the rank of a number within a list: 1 for the largest by default, or 1 for the smallest when order is 1. Tied values get the same rank and the next rank is skipped. RANK.AVG gives tied values the average rank; the old RANK works like RANK.EQ.

Syntax

RANK.EQ(number, ref, [order])   RANK.AVG(number, ref, [order])   LARGE(array, k)   SMALL(array, k)

Part	What it is
`number`	The value to rank, usually the cell in the current row.
`ref`	The whole list, locked with $ so it does not shift when filled down.
`order`	0 or omitted: largest is 1. Any other number: smallest is 1.

Examples

=RANK.EQ(B2, $B$2:$B$20)

Result: 1 for the largest value in B2:B20, 2 for the next, and so on

=RANK.EQ(B2, $B$2:$B$20, 1)

Result: 1 for the smallest value, useful for times and costs

=RANK.EQ(B2, $B$2:$B$20) + COUNTIF($B$2:B2, B2) - 1

Result: Unique ranks even when values tie; the earlier row gets the better rank

The expanding range $B$2:B2 counts how many times the value has appeared so far.

=LARGE(B2:B20, 2)

Result: The second largest value in B2:B20

SMALL(B2:B20, 2) gives the second smallest.

Common mistakes

Ranks are wrong after filling down because ref was not locked: use $B$2:$B$20.
Two rows show rank 3 and nobody is rank 4: that is how RANK.EQ handles ties. Use the COUNTIF tie-breaker if you need unique ranks.
#N/A: the number is not in ref, which happens when the ranked cell sits outside the range.
Text values in ref are ignored, and numbers stored as text are not ranked.

Practice RANK

These drills check your answer on live data, free, no account needed.

Second biggest dealFind the second largest deal in the list and put it in E2.

Questions

What is the difference between RANK, RANK.EQ and RANK.AVG?

RANK and RANK.EQ are the same: tied values share the top rank available. RANK.AVG gives tied values the average of the ranks they would occupy, so two values tied for 2nd both get 2.5.

How do I rank without duplicates?

Add a tie-breaker: =RANK.EQ(B2, $B$2:$B$20) + COUNTIF($B$2:B2, B2) - 1.

How do I rank within a group?

Use COUNTIFS: =COUNTIFS($A$2:$A$20, A2, $B$2:$B$20, ">" & B2) + 1 ranks B2 among rows with the same group in A.

MAXIFS and MINIFSHow to use MAXIFS and MINIFS in Excel to find the highest or lowest value that meets conditions, with examples by region, status and date, plus the 2016 way.
SORT and SORTBYHow the Excel SORT and SORTBY functions sort a range with a formula that updates itself, with examples for descending order, several columns and outside keys.
COUNTIFHow to use COUNTIF in Excel to count cells equal to a value, above a number, containing text or not blank, with examples, a duplicate check and fixes.
COUNTIFSHow to use COUNTIFS in Excel to count rows where two or more conditions are all true, with examples for text plus number, date ranges and not-equal.