Confident Cells

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.

Your turn

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.

ABCD
1PupilClassScoreClass position
2Isla Baker9A60
3Oliver Green9B93
4Maya Price9B33
5Rosa Evans9B53
6Daniel Turner9A82
7Arjun Brown9A21
8Tyrese Khan9C75
9Layla Wilson9C97
10Mia Jones9B67
11Esme Hussain9A39
12Finley Mensah9C92
13Callum Jones9A32
14Jack Khan9B24
15Zainab Wood9C86
16Freya Evans9A87
17Ethan Clarke9A76
18Ruby Hughes9A30
19Oscar Khan9B51
20Kacper Wood9C81
21Aryan Green9C34

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

ABCD
1PupilClassScoreClass position
2Ana9X72
3Bilal9X85
4Chloe9Y64
5Dan9Y91
6Ella9X58
  1. =COUNTIFS($B$2:$B$6,B2,$C$2:$C$6,">"&C2)+1 in D2. In 9X, Bilal (85) is 1st, Ana (72) 2nd and Ella 3rd. In 9Y, Dan is 1st and Chloe 2nd.
  2. =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.
  3. =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

  1. 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.
  2. 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.
  3. 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.
  4. In H2, work out the average score for the set in G2. Your formula will be copied down to H4.
  5. 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.