Excel function reference

RANK / LARGE / SMALL / MEDIAN / MODE

Quick answer

Five ways to ask "how does this compare to the rest of the set": a position, a specific-place value, or the group's middle or most common value.

RANK(number, ref, [order]) · LARGE(array, k) · SMALL(array, k) · MEDIAN(number1, [number2], ...) · MODE(number1, [number2], ...)

RANK gives a position, 1st, 2nd, 3rd, while LARGE and SMALL give the actual value sitting at that position: "who's 2nd" versus "what's the 2nd-highest score" are different questions answered by different functions. MEDIAN finds the true middle value once everything is sorted, which resists a single outlier far better than AVERAGE does, since one unusually large or small value can drag an average far from where most of the data actually sits. MODE returns whichever value appears most often in a set; if every value happens to be unique, there's no mode at all, and MODE returns an error rather than guessing at one.

Syntax

RANK(number, ref, [order]) · LARGE(array, k) · SMALL(array, k) · MEDIAN(number1, [number2], ...) · MODE(number1, [number2], ...)

Argument	Description
number	RANK: Required. The value to rank within ref.
ref	RANK: Required. The full set of values it's being ranked against.
order	RANK: Optional. 0 (or omitted) ranks highest-first; any nonzero value ranks lowest-first.
array, k	LARGE/SMALL: Required. The range to search, and which place to return: k=1 is the largest (LARGE) or smallest (SMALL) value.
number1, ...	MEDIAN/MODE: Required. The values, cells, or ranges to find the middle value or most frequent value of.

A real example: the runner-up score without building a full leaderboard

Pulling the second-highest score directly, with no need to rank the entire list first:

=LARGE(A2:A5,2)

This answers "what is the 2nd-highest value" in one step; RANK would instead answer the related but different question of "what position does a specific score hold" among the same set.

Worked examples

A leaderboard position

	A	B
1	Score	Scores
2	88	72
3		95
4		88
5		60

=RANK(A2,B2:B5) → 2

The second-highest value directly

	A
1	Scores
2	72
3	95
4	88
5	60

=LARGE(A2:A5,2) → 88

The most frequently placed order size

	A
1	Order size
2	2
3	4
4	2
5	6
6	2

=MODE(A2:A6) → 2

Common mistakes

Forgetting the order argument when a lower number should actually rank first (like a race time): without it, RANK assumes higher is better.
Confusing RANK (a position) with LARGE/SMALL (a value). "What rank is 88?" and "what's the 2nd-highest value?" are different questions with different functions.
Expecting MODE to return something on a set where every value is different. With no repeats, there is no mode, and MODE errors rather than guessing one.

Related functions

Try it yourself

A free, graded practice exercise using this exact function: instant feedback, no signup required.

Practice ranking and stats functions →