Set lists and ranking: practice exercises
Position in class, sets from ranks, top 3, set averages, moves. 5 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.
In D2, work out each pupil's position within their own class (1 for the top score in that class), not within the whole year.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Pupil | Class | Score | Class position |
| 2 | Isla Baker | 9A | 60 | |
| 3 | Oliver Green | 9B | 93 | |
| 4 | Maya Price | 9B | 33 | |
| 5 | Rosa Evans | 9B | 53 | |
| 6 | Daniel Turner | 9A | 82 | |
| 7 | Arjun Brown | 9A | 21 | |
| 8 | Tyrese Khan | 9C | 75 | |
| 9 | Layla Wilson | 9C | 97 | |
| 10 | Mia Jones | 9B | 67 | |
| 11 | Esme Hussain | 9A | 39 | |
| 12 | Finley Mensah | 9C | 92 | |
| 13 | Callum Jones | 9A | 32 | |
| 14 | Jack Khan | 9B | 24 | |
| 15 | Zainab Wood | 9C | 86 | |
| 16 | Freya Evans | 9A | 87 | |
| 17 | Ethan Clarke | 9A | 76 | |
| 18 | Ruby Hughes | 9A | 30 | |
| 19 | Oscar Khan | 9B | 51 | |
| 20 | Kacper Wood | 9C | 81 | |
| 21 | Aryan Green | 9C | 34 |
How it works
RANK compares a score with the whole list. To rank within a class, count the pupils in the same class with a higher score and add 1: COUNTIFS($B$2:$B$21,B2,$C$2:$C$21,">"&C2)+1.
LARGE(range, k) gives the k-th highest score, and INDEX with MATCH turns a score back into a name.
=COUNTIFS($B$2:$B$6,B2,$C$2:$C$6,">"&C2)+1 =LARGE($C$2:$C$6,2) =AVERAGEIF($B$2:$B$6,"9Y",$C$2:$C$6) =IF(D2<=10,1,IF(D2<=20,2,3))
Position within a group
RANK compares a score with the whole list. For a position within a class, count the pupils in the same class with a higher score, and add 1:
=COUNTIFS(class column, this class, score column, ">"&this score)+1
The top pupil has nobody above them, so they get 0 + 1 = 1.
Worked example
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Pupil | Class | Score | Class position |
| 2 | Ana | 9X | 72 | |
| 3 | Bilal | 9X | 85 | |
| 4 | Chloe | 9Y | 64 | |
| 5 | Dan | 9Y | 91 | |
| 6 | Ella | 9X | 58 |
=COUNTIFS($B$2:$B$6,B2,$C$2:$C$6,">"&C2)+1in D2. In 9X, Bilal (85) is 1st, Ana (72) 2nd and Ella 3rd. In 9Y, Dan is 1st and Chloe 2nd.=INDEX($A$2:$A$6,MATCH(LARGE($C$2:$C$6,1),$C$2:$C$6,0))in G2. LARGE(...,1) is the top score, 91. MATCH finds where it is, and INDEX gives the name: Dan. Use 2 or 3 for second and third.=AVERAGEIF($B$2:$B$6,"9Y",$C$2:$C$6)in G3. The average score in 9Y: (64 + 91) ÷ 2 = 77.5.
Sets and moves
Sets from a rank work like bands: IF(rank<=H1, 1, IF(rank<=H2, 2, 3)), with the cut-off ranks in cells.
Set 1 is the top set, so a smaller set number is a move up. Compare new with old: new < old is Up, new > old is Down.
What you'll practise
- In D2, work out each pupil's position within their own class (1 for the top score in that class), not within the whole year.
- The top pupils by year rank, down to the rank in H1, go in Set 1. The next pupils, down to the rank in H2, go in Set 2, and everyone else in Set 3. In D2, work out each pupil's set.
- In H2, find the name of the pupil in the place shown in G2 (1st, 2nd or 3rd highest score). Your formula will be copied down to H4.
- In H2, work out the average score for the set in G2. Your formula will be copied down to H4.
- Set 1 is the top set. In D2, show Up if a pupil has moved to a higher set, Down if they've moved to a lower one, and leave it blank if they've stayed put.