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.
Each paper is out of 80. In E2, work out each pupil's total across the three papers.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Pupil | Paper 1 | Paper 2 | Paper 3 | Total |
| 2 | Ellie Bennett | 17 | 26 | 41 | |
| 3 | Ruby Okafor | 9 | 62 | 10 | |
| 4 | Daniel Hussain | 52 | 75 | 21 | |
| 5 | Louis Baker | 37 | 21 | 74 | |
| 6 | Alicja Kowalski | 51 | 56 | 48 | |
| 7 | Evie Shah | 58 | 25 | 26 | |
| 8 | Ibrahim Wright | 11 | 72 | 12 | |
| 9 | Oliver Bennett | 76 | 15 | 35 | |
| 10 | Aisha Price | 13 | 77 | 67 | |
| 11 | Esme Wright | 37 | 43 | 31 | |
| 12 | Isla Nowak | 39 | 14 | 64 | |
| 13 | Layla Nowak | 54 | 49 | 38 | |
| 14 | Oscar Baker | 20 | 32 | 46 | |
| 15 | Rosa Price | 69 | 13 | 9 | |
| 16 | Mohammed Evans | 64 | 63 | 12 | |
| 17 | Freya Green | 75 | 64 | 59 | |
| 18 | Callum Shah | 12 | 61 | 18 | |
| 19 | Joshua Hughes | 34 | 72 | 25 | |
| 20 | Tyrese Green | 22 | 36 | 15 | |
| 21 | Amira Ali | 27 | 73 | 40 |
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
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Pupil | Total | Grade | To next grade | From | Grade | |
| 2 | Ana | 112 | 0 | U | |||
| 3 | Bilal | 58 | 40 | 4 | |||
| 4 | Chloe | 149 | 70 | 5 | |||
| 5 | Dan | 31 | 100 | 6 | |||
| 6 | 130 | 7 |
=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.=INDEX($F$2:$F$6,MATCH(B2,$F$2:$F$6,1)+1)-B2in 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.=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
- Each paper is out of 80. In E2, work out each pupil's total across the three papers.
- The grade boundaries are in G2:H11: the lowest total for each grade. In C2, look up each pupil's grade.
- In H1, count how many pupils got grade 4 or above.
- 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.
- 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.