**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 ****Template****s**

Select the **T****emplate** that you wish to add a formula to and click **Edit**

Second box down **Columns ****> ****Ad****d > ****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.