Introduction

Our example school wants to create a marksheet that calculates the value added for each pupil in Y4 based on their KS1 result. The school assumes that pupils in year 4 should have acheived 3 sublevels of progress since KS1. A simple formula calculates how many sublevels they have actually acheived and subtracts this from 3 to give a positive or negative score to indicate how many levels above or below their target they are.

Method

1) Create a new template called *Value Added Y4 Maths and add the following aspects and results sets (the names of the aspects may vary on your system):

Attainment Maths (result set = Y2 Summer), column name = KS1 Result
Attainment Maths (result set = Y4 Summer), column name = Attainment Maths Y4 Sum

2) Create a column for data review using a formula. The column will calculate the points score of the KS1 result. Create the formula as follows:

Column name: KS1 PS
Formula type: Value
Column: KS1 Result

3) Create another column for data review using a formula. This column will calculate the points score of Attainment Maths Y4 Sum

Column Name: Y4 PS
Formula type: Value
Column: Attainment Maths Y4 Sum

4) Next we need to subtract Year 4 points score from the KS1 points score:

Column Type: Data review using a formula
Column Name: Y4 - KS1 PS
Formula type: Difference
Column 1: Y4 PS
Column 2: KS1 PS

5) Now we convert the difference in points into sublevels (2 points = 1 sublevel):

Column Type: Data review using a formula
Column Name: Y4 Maths Levels Added
Formula type: Division
Column: Y4 - KS1 PS
Constant: 2

6) Finally, each pupil should have made 3 sublevels of progress by year 4, so we calculate the value added as the difference between their actual number of levels progress (calculated at point 5 above) and 3 levels. We”ll save this result back into the aspect we created earlier, so remember to create a column for data entry using a formula.

Column Type: Data entry using a formula�
Column Name: Y4 Maths Value Added
Formula type: Difference
Column: Y4 Maths Levels Added
Constant: 3

Save your template and create the relevant marksheets for year 4.