Confident Cells

Mark sheets and grade boundaries: practice exercises

Totals, grades from boundaries, % at grade 7+, marks to the next grade. 5 exercises on real-looking data, each marked the moment you press Check. This lesson is free, with no account needed.

Your turn

Each paper is out of 80. In E2, work out each pupil's total across the three papers.

ABCDE
1PupilPaper 1Paper 2Paper 3Total
2Ellie Bennett172641
3Ruby Okafor96210
4Daniel Hussain527521
5Louis Baker372174
6Alicja Kowalski515648
7Evie Shah582526
8Ibrahim Wright117212
9Oliver Bennett761535
10Aisha Price137767
11Esme Wright374331
12Isla Nowak391464
13Layla Nowak544938
14Oscar Baker203246
15Rosa Price69139
16Mohammed Evans646312
17Freya Green756459
18Callum Shah126118
19Joshua Hughes347225
20Tyrese Green223615
21Amira Ali277340

How it works

A grade boundary table lists the lowest mark for each grade, smallest first. VLOOKUP(total, table, 2, TRUE) finds the last boundary that the total reaches, which is the grade. TRUE means "approximate match", and it's what makes boundaries work.

U grades are text, so COUNT leaves them out but COUNTA includes them. A percentage "out of everyone entered" needs COUNTA.

To find the next boundary up, MATCH with 1 gives the position of the current grade's boundary, and +1 moves one row down the table.

=SUM(B5:D5) =VLOOKUP(B2,$F$2:$G$6,2,TRUE) =COUNTIF(B2:B5,">=100")/COUNTA(A2:A5) =INDEX($F$2:$F$6,MATCH(B2,$F$2:$F$6,1)+1)-B2

Grades from boundaries

A boundary table lists the lowest total for each grade, smallest first. VLOOKUP(total, table, 2, TRUE) finds the last boundary the total reaches and gives that grade.

TRUE (approximate match) is what makes boundaries work. A total of 112 isn't in the table, so VLOOKUP settles on 100, the last boundary at or below it.

Worked example

ABCDEFG
1PupilTotalGradeTo next gradeFromGrade
2Ana1120U
3Bilal58404
4Chloe149705
5Dan311006
61307
  1. =VLOOKUP(B2,$F$2:$G$6,2,TRUE) in C2. 112 reaches 100, so grade 6. 58 reaches 40 (grade 4), 149 reaches 130 (grade 7), and 31 doesn't reach 40, so U.
  2. =INDEX($F$2:$F$6,MATCH(B2,$F$2:$F$6,1)+1)-B2 in D2. MATCH with 1 finds the row of the boundary they've reached; +1 moves to the next one up, and INDEX gives its mark. Ana needs 130 − 112 = 18 more.
  3. =COUNTIF(B2:B5,">=100")/COUNTA(A2:A5) in D7. The share of pupils on 100 or more: 2 out of 4, 0.5, which shows as 50% with a percentage format.

COUNT or COUNTA?

U is text, so COUNT leaves U grades out but COUNTA counts them. A percentage "of everyone entered" must divide by COUNTA, or pupils with a U quietly disappear and the percentage looks better than it is.

Grades are numbers, so COUNTIF(grades,">=4") counts grade 4 and above.

What you'll practise

  1. Each paper is out of 80. In E2, work out each pupil's total across the three papers.
  2. The grade boundaries are in G2:H11: the lowest total for each grade. In C2, look up each pupil's grade.
  3. In H1, count how many pupils got grade 4 or above.
  4. In H1, work out the percentage of pupils who got grade 7 or above, out of everyone entered, including those who got a U. H1 is formatted as a percentage.
  5. Nobody reached grade 9 this time. In C2, work out how many more marks each pupil needed to reach the next grade up, using the boundaries in G2:H11.