Ranking and top scores: practice exercises
RANK, LARGE, MAXIFS, finding the top scorer. 4 exercises on real-looking data, each marked the moment you press Check. The first exercise is below to try now; the full lesson is part of Pro.
Your turn
In D2, give each employee's rank, with 1 for the highest score.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Employee | Region | Score | Rank | |||
| 2 | Grace | North | 67 | 3rd highest score | 79 | ||
| 3 | Tom | South | 54 | Top North region score | 72 | ||
| 4 | Priya | East | 88 | Top scorer | Priya | ||
| 5 | Marek | North | 72 | ||||
| 6 | Nia | East | 61 | ||||
| 7 | Ollie | South | 45 | ||||
| 8 | Lucy | South | 79 | ||||
| 9 | Hamza | North | 58 | ||||
| 10 | Rachel | East | 83 | ||||
| 11 | Sunil | North | 49 | ||||
| 12 | Tanya | South | 70 | ||||
| 13 | Will | East | 63 |
How it works
RANK(value, range) gives a position, with 1 for the highest. Lock the range with $ when you copy it down. LARGE(range, k) gives the k-th highest value, and SMALL the k-th lowest.
MAXIFS and MINIFS find the highest or lowest value that meets a condition. To find who has the top score, look up the MAX.
=RANK(C2,$C$2:$C$6) =LARGE(C2:C6,2) =MAXIFS(C2:C6,B2:B6,"Sales") =XLOOKUP(MAX(C2:C6),C2:C6,A2:A6)
Positions and top values
RANK(value, range)gives a position, with 1 for the highest.LARGE(range, k)gives the k-th highest value, andSMALL(range, k)the k-th lowest.MAXIFSandMINIFSgive the highest or lowest value that meets a condition.- To find who has the top value, work out the MAX and look it up.
Worked example: RANK
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Name | Team | Sales | Rank | 2nd highest | ||
| 2 | Amira | Sales | 52 | Top in Sales | |||
| 3 | Ben | Finance | 61 | Top seller | |||
| 4 | Cara | Sales | 47 | ||||
| 5 | Dev | Support | 70 | ||||
| 6 | Ewa | Sales | 58 |
=RANK(C2,$C$2:$C$6)in .RANK(value, range): Amira's 52 is 4th highest of the five.=RANK(C2,$C$2:$C$6)in . The range is locked, so every row is ranked against the same list. Without the $ signs, the range would slide down as it's copied, and the ranks would go wrong.
Worked example: LARGE, MAXIFS and who
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Name | Team | Sales | Rank | 2nd highest | ||
| 2 | Amira | Sales | 52 | Top in Sales | |||
| 3 | Ben | Finance | 61 | Top seller | |||
| 4 | Cara | Sales | 47 | ||||
| 5 | Dev | Support | 70 | ||||
| 6 | Ewa | Sales | 58 |
=LARGE(C2:C6,2)in G1.LARGE(range, k)gives the k-th highest: the 2nd highest is 61.=MAXIFS(C2:C6,B2:B6,"Sales")in G2.MAXIFS(range to take the highest from, range to check, criteria): the highest in Sales is 58. MINIFS works the same way.=XLOOKUP(MAX(C2:C6),C2:C6,A2:A6)in G3. To find who: MAX gives the top sales, then XLOOKUP finds it in the sales and returns the name: Dev.
What you'll practise
- In D2, give each employee's rank, with 1 for the highest score.
- In G2, find the third-highest score.
- In G3, find the highest score in the North region.
- In G4, show the name of the employee with the top score.