Creative Commons License Photo Credit: josef.stuefer via Compfight

Some time ago I published instructions to help schools using Assessment Manager to calculate if their pupils were making acceptable progress between KS2 and KS4 based on the DfE guidance. Recently, @vikpaw emailed me a much more elegant method for using Assessment Manager to indicate the pupils who are (or are not) making expected progress. Here’s the resulting marksheet:


Here’s my summary of the DfE rules for expected progress:

Level 2YesYesYesYesYesYes
Level 3NoYesYesYesYesYes
Level 4NoNoYesYesYesYes
Level 5NoNoNoYesYesYes


So a level 5 pupil at KS2 is expected to make at least a grade B at KS4, while a level 2 pupil at KS2 is expected to make at least an E at GCSE.

As you know, both national curriculum levels and GCSE grades have a points value associated with them. For example, 4b has a points value of 27 and a GCSE grade C has a points score of 40. Hence there is a difference of 13 points between a level 4 and the expected progress of a level 4 pupil to grade C at GCSE. This ‘rule of 13 points’ holds for all the examples in the table above even though there is no official link between national curriculum sublevel points score and GCSE points scores. So we can create formulas in our marksheets to look for a difference of 13 points or more between the KS2 points score and the GCSE points score.

Assuming we already have a marksheet with a pupil’s KS2 result and GCSE expected grade, we just need to add two formula columns to our marksheet. The first column calculates the points difference between the KS2 result and our expected GCSE grade:

The second column uses a nested-if-then-else formula to decide if the value is greater than or equal to 13 and apply the relevant colour:

Once again, thanks to @vikpaw.