RANK, LARGE, SMALL, MEDIAN & MODE practice exercises
6 graded problems with realistic data, from easy to medium.
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. The 6 exercises below come from LearnExcel's practice library. Each one gives you a realistic table, a cell to fill, and a hint if you get stuck. Read them here, then solve them in the interactive grid, where your formula is checked the moment you press Enter.
New to the function? Read the RANK, LARGE, SMALL, MEDIAN & MODE guide first: syntax, a worked example and the mistakes people make.
In this set
- Rank a score on a leaderboard
- Rank race times, fastest first
- Find the 2nd-highest value in a list
- Find the 2nd-lowest value in a list
- Find the median instead of being misled by an outlier
- Find the most common rating
The exercises
Five players' scores are listed below. Find the rank of the player in B4 (1st place = the highest score).
| A | B | C | |
|---|---|---|---|
| 1 | Player | Score | |
| 2 | Mia | 82 | |
| 3 | Omar | 95 | |
| 4 | Priya | 71 | ? |
| 5 | Jin | 88 | |
| 6 | Tasha | 60 |
Enter a formula in C4.
Show a hint
RANK(number, ref) ranks highest-first by default: =RANK(B4,B2:B6).
RANK is the formula behind every leaderboard, standings table, or sales ranking: one function, recalculates automatically as scores change.
In a race, the lowest time wins. Find the rank of the runner in B4 where 1st place is the fastest (lowest) time.
| A | B | C | |
|---|---|---|---|
| 1 | Runner | Time (min) | |
| 2 | Alex | 24.1 | |
| 3 | Beth | 22.8 | |
| 4 | Cole | 25.6 | ? |
| 5 | Dana | 23 | |
| 6 | Eli | 26.2 |
Enter a formula in C4.
Show a hint
RANK's optional 3rd argument flips the order; any nonzero value ranks lowest-first: =RANK(B4,B2:B6,1).
Whether "best" means highest or lowest depends entirely on what you're measuring: that 3rd argument is how RANK handles both without a different formula.
Find the 2nd-highest monthly sales total in the range below, without sorting the data.
| A | B | |
|---|---|---|
| 1 | Month | Sales |
| 2 | Jan | 4200 |
| 3 | Feb | 5100 |
| 4 | Mar | 3800 |
| 5 | Apr | 6400 |
| 6 | May | 4900 |
| 7 | 2nd highest | ? |
Enter a formula in B7.
Show a hint
LARGE(range, k) returns the k-th largest value: =LARGE(B2:B6,2).
LARGE/SMALL answer "what's the Nth-best?" directly: no need to sort the sheet or build a helper column just to find a runner-up.
Find the 2nd-lowest expense in the list below, without sorting the data.
| A | B | |
|---|---|---|
| 1 | Category | Expense |
| 2 | Rent | 1200 |
| 3 | Utilities | 180 |
| 4 | Supplies | 95 |
| 5 | Software | 240 |
| 6 | Travel | 410 |
| 7 | 2nd lowest | ? |
Enter a formula in B7.
Show a hint
SMALL(range, k) returns the k-th smallest value: =SMALL(B2:B6,2).
SMALL is LARGE's mirror image: together they let you pull any rank, from the top or the bottom, out of a list.
Five houses on a street just sold. One sale was a mansion that skews the average way up. Find the median sale price: a better sense of what's "typical" here than the average would give.
| A | B | |
|---|---|---|
| 1 | House | Sale price |
| 2 | 1 | 310000 |
| 3 | 2 | 295000 |
| 4 | 3 | 340000 |
| 5 | 4 | 1850000 |
| 6 | 5 | 320000 |
| 7 | Median | ? |
Enter a formula in B7.
Show a hint
MEDIAN(range) sorts the values and returns the middle one: =MEDIAN(B2:B6).
A single huge outlier (that mansion) drags AVERAGE way up, but barely moves MEDIAN; whenever a dataset might have outliers, MEDIAN is the more honest "typical value."
Customers rated a product from 1 to 5 in the survey below. Find the rating that came up most often.
| A | B | |
|---|---|---|
| 1 | Review | Rating |
| 2 | 1 | 4 |
| 3 | 2 | 5 |
| 4 | 3 | 3 |
| 5 | 4 | 4 |
| 6 | 5 | 4 |
| 7 | 6 | 2 |
| 8 | Most common rating | ? |
Enter a formula in B8.
Show a hint
MODE(range) returns whichever value appears most often in the range: =MODE(B2:B7).
RANK, LARGE, SMALL, and MEDIAN all describe where a value sits in order; MODE answers a different question entirely: not where something ranks, but what shows up the most.
Solve these in a real grid
Type your formula, get graded instantly. Your first 10 exercises are free, no signup.
Start the RANK, LARGE, SMALL, MEDIAN & MODE exercises →