Confident Cells

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.

ABCDEFG
1EmployeeRegionScoreRank
2GraceNorth673rd highest score79
3TomSouth54Top North region score72
4PriyaEast88Top scorerPriya
5MarekNorth72
6NiaEast61
7OllieSouth45
8LucySouth79
9HamzaNorth58
10RachelEast83
11SunilNorth49
12TanyaSouth70
13WillEast63

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, and SMALL(range, k) the k-th lowest.
  • MAXIFS and MINIFS give 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

ABCDEFG
1NameTeamSalesRank2nd highest
2AmiraSales52Top in Sales
3BenFinance61Top seller
4CaraSales47
5DevSupport70
6EwaSales58
  1. =RANK(C2,$C$2:$C$6) in . RANK(value, range): Amira's 52 is 4th highest of the five.
  2. =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

ABCDEFG
1NameTeamSalesRank2nd highest
2AmiraSales52Top in Sales
3BenFinance61Top seller
4CaraSales47
5DevSupport70
6EwaSales58
  1. =LARGE(C2:C6,2) in G1. LARGE(range, k) gives the k-th highest: the 2nd highest is 61.
  2. =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.
  3. =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

  1. In D2, give each employee's rank, with 1 for the highest score.
  2. In G2, find the third-highest score.
  3. In G3, find the highest score in the North region.
  4. In G4, show the name of the employee with the top score.