Note: All pages below are subject to having relevant Roles and Permissions.
This guide will show you how to add a variety of Formula Columns to an Assessment Marksheet. Formulas are used in assessment for many reasons and there are different formulas you can choose from, (e.g. mean, sums) to compare between students, between predicted and actual grades, and between assessment data points.
In this guide we looked at the following popular Formulas;
In the first instance when looking at formulas for Assessment Types the calculation is looking at the value of the Grade Set or Mark Set attached to the Assessment Type chosen not the Name or Description
When adding Formulas in a Tracking Template they must be positioned below the columns they are referencing.
Formula examples are covered in the Assessment Training see here to book Assessment Training.
Pre-requisites:
- Basic Assessment Sheet completed – please see How to create an Assessment Sheet; and
- Assessment Data in the Sheet.
Difference
In the example below we have added a column to show the difference between 2 different Assessment Types.
Start by going to Modules > Assessment > Manage Templates
Select the Template that you wish to add a formula to and click Edit
Second box down Columns > Add > Column Type > Add Formula Columns
The Add Template Column screen will appear.
Complete the relevant information
- Column Type: If you want to use the result in Reports, you need to tick Entry. If not please tick Review. Basically the Review is just a calculation that just shows in the assessment sheet.
- Chose the Column Name – a shorthand name, here we have chosen Difference – PG v CG (difference between the predicted grade and the current grade).
- Choose the Formula* type that you want to use (in this case, Difference).
- Choose the Columns you want to include in the Formula – predicted grade and current grade for example
Then Click Add
This will have Added the Difference column and it will appear at the bottom of your list of Columns. If you want to move it, you can by Changing the Number Order.
Note: you Cannot move it Above the columns that are included in the formula.
Once you have the column in place remember to click save, then close the Template and then open the Template to view it (Modules > Assessment > Assessment Sheets List.)
Here you will be able to view the new columns in the marksheet, as in the image below:
So in the example below Predicted Grade = 7, Current Grade = 6 so the difference is -1 as the Current Grade is 1 less than the Predicted Grade
If you need to amend anything, navigate back to the Template from Modules > Assessment > Manage Templates.
Grade Tally
In the example below we have added a column to show the Count or Grade Tally of Grade 5’s in a single term and single Assessment Type, for example the number of Grade 5’s and above in the Current Grade Assessment Type for the Autumn 1 Term
Go to Modules>Assessment>Manage Templates select the Tracking Template and click Edit.
In the example below we have created a Tracking Template to show a Student Broadsheet of Assessment Types to show 3 different Assessment Types for the Autumn 1 Term.
When we created this Tracking Template in the Subject column we selected each individual Subject as appose to Same as Template in order to product a Student Broadsheet
Click Add
Add Formula Columns
Then we selected Review
For Column Name we chose Current Grade 5+
In Formula we chose Grade Tally Columns
Then we selected the 7 Columns and in Grades we types in 5,6,7,8 and 9. Please check the Grades in your Grade Set if you are unsure what to enter here.
This will add the formula column as the last column in the Tracking Template.
We then moved the Formula Column to show after all the Current Grades in the Assessment Sheet by selected the column and changing the number as per the example below.
Click Save and Close
To check the Formula Column in correct go to Modules>Assessment>Assessment Sheets List.
Click on the Assessment Sheet and in the example below we can see the Formula Column counting 3 Grade 5 and above
Nested if then Else
In the example below we have added a Formula Entry column to show ‘Nested if then Else‘ looking at the Predicted Grade against a Target Grade
We want the column to show the following;
- Predicted Grade is more than Target Grade then output is Above Target
- Predicted Grade is the same as Target Grade then output is On Target
- Predicted Grade is less than Target Grade then output is Below Target
We are using Formula Entry as we want to report on this Formula so before adding the Formula column to the Tracking Template we have created a Grade Set called Target Status and attached this to an Assessment Type called Target Status.
See here for guidance on creating Assessment Types and Grade Sets
Within the Target Status Grade Set the Grades are Above Target, On Target and Below Target
Go to Modules>Assessment>Manage Templates and select the Tracking Template, click Edit
Click Add
Choose Add Formula Columns from the Column Type drop down, click Add.
In the example below we have chosen Entry as the Column Type, we have called the Column Name Prediction v Target then as its a Formula Entry column we first need to define the Assessment Type, Term, Year Group and Subject
It’s for this reason that for Formula Entry you need to create the Assessment Type before adding the Formula Column.
Then in the Formula drop down we have chosen Nested if then else, the screen will now expand so we can define the parameters for the Nested if then else column
In the Source drop down we have defined for the Column the following
If ‘Predicted Grade -Autumn 1-Year 11- Art ‘ then on the Comparator we have chosen > (more than) then in Reference we have chosen ‘Target – All Terms- Y11-Art’ THEN as Value we have typed Above Target
Above Target is a Grade is the Grade Set attached to the Target Status Assessment Type.
Please take care when typing in the Value as it has to be an exact match to the Grade in the Grade Set attached to the Assessment Type
- A numerical Value in the outcome must match the ‘Value’ column of the Gradeset
- If Letters are used then it must match the ‘Name’ column of the Gradeset.
Then we have clicked on the clone item icon twice to create another 2 lines in the Formula, this is purely to save time adding in another row however, you can select the + if required
In this example as we are comparing the same 2 columns we have just changed the Comparators for the 2 ELSE IF rows we have added and changed to Value in the Outcome Column as per the example below
Finally we changed the ELSE to Value and typed in N/A
So this column will show;
- If Predicted Grade is MORE THAN Target then Outcome is Above Target
- ELSE IF Predicted Grade is EQUAL TO Target then Outcome is On Target
- ELSE IF Predicted Grade is LESS THAN Target then Outcome is Below Target
- ELSE is N/A in the example the Students doesn’t have both sets of data in the Assessment Types defined in the Formula
Click Add
As we have just added a Formula Column for one Subject you can click on the Column and click Copy under the Columns panel this will create another identical column.
Then select the New Column, right click and Edit to then Edit Formula for another subject.
Click OK to Save the formula in the added Column
Then Save and Close the Tracking Template
Go to Modules>Assessment>Assessment Sheets List to check/view the Formula Column
In the example below we can see the Nested if then Else Formula Column added, note in this example the Target Status Grade Set has the Grades coloured.
Mean
In this example we are adding a Formula Review Column to show the Mean Current Grade
Go to Modules>Assessment>Manage Templates and select the Tracking Template, click Edit
Click Add
Choose Add Formula Columns from the Column Type drop down, click Add.
Then in the example below we have called the Column Mean Current Grade, selected the Mean from the Formula drop down and in the Column selected the 7 Current Grade columns.
In the Rounding we have chosen Rounding off in this example.
Then Save and Close the Tracking Template
Go to Modules>Assessment>Assessment Sheets List to check/view the Formula Column.
In the example below we can see the Mean Current Grade Column to show the Average of the 7 Current Grade Columns
Max
In this example we are adding a Formula Review Column to show the Max Current Grade
Go to Modules>Assessment>Manage Templates and select the Tracking Template, click Edit
Click Add
Choose Add Formula Columns from the Column Type drop down, click Add.
In the example below we have chosen Review and on the Column Name typed in Max Current Grade then we have chosen the 7 Current Grade columns and typed in 1
This will show the maximum grade in the selected column, below is an example of how the Maximum Selection works
Student has grades 7, 6, 7, 5, 4, 6, 2, 6, 6,
- max 1 = 7
- max 2 = 7
- max 3 = 6
- max 4 = 6
- max 5 = 6
- max 6 = 6
- max 7 = 5
- max 8 = 4
- max 9 = 2
Click Add
Then Save and Close the Tracking Template.
Go to Modules>Assessment>Assessment Sheets List to check/view the Formula Column.
Position
In this example we are adding a Formula Review Column to show the Position, prior to this we created a Mean column of a selected number of Assessment Types.
In this example we wanted to see the Mean ( or Average) ATL Attitude to Learning Assessment Type for each Student then rank the Students.
Go to Modules>Assessment>Manage Templates and select the Tracking Template, click Edit
Click Add
Choose Add Formula Columns from the Column Type drop down, click Add.
In the example below we have chosen Review and on the Column Name typed in ATL Ranking then we have chosen the Position Formula and then selected Average ATL column.
Then Save and Close the Tracking Template.
Go to Modules>Assessment>Assessment Sheets List to check/view the Formula Column.
In the example below we can see the Average ATL column and the ATL Ranking column showing the Position Formula
If Then Else
In this example we want to see an output of Yes for the Students for have achieved a Grade 5 and above in their English and Maths Current Grade
In the first instance we have added a Grade Tally column for English and Maths Current Assessment Type looking for Grades 5,6,7,8,9 see Grade Tally in this guide.
Then we have added an If Then Else Formula Review Column as below in the Source we have selected the Grade Tally Column for the English and Maths Grade 5 and above and then in the Comparator chosen >= ( more than and equal to) in the Value we have chosen 2 with an Outcome of Yes and Else set to No.
Click OK to Save
Then Save and Close the Tracking Template.
Go to Modules>Assessment>Assessment Sheets List to check/view the Formula Column.
