Advanced Marksheet Techniques
Essential CPD for assessment cocoordinators and data managers.
Over three hours of video lectures that take you stepbystep through realworld assessment problems. Learn how to:
 Use nestedifthenelse formulas to add more than just 5 colours to marksheets,
 convert numbers to grades to make data analysis easy
 calculate 5+ A*C inc E+M grades using the grade tally formula,
 using the sum and mean formulas to work out the total and average GCSE points scores
 calculate the Estimated Attainment 8 value for a pupil
 calculate the Actual Attainment 8 value for a pupil
 calculate the P8 value for a pupil
 create a target or flightpath based on a baseline assessment (KS2 or Year 7)
 extend the Programme of Study marksheets using traditional formulas and templates
Curriculum
Advanced Marksheet Techniques
 Introduction and overview of this course (2:06)
 MARKS TO GRADE: Converting integers to grades for better analysis (14:39)
 NESTED IF THEN ELSE: Using 2 or more nested if then else columns for more colours (11:15)
 A general discussion about showing in year progress using sub grades (9:20)
 Use a LOOKUP formula to convert from A*G to 91 (4:36)
 Creating a LOOKUP table and using the formula to convert to 91 (8:04)
 Use a GRADE TALLY formula to calculate Total GCSE A*C for a pupil (12:29)
 Including nonGCSE subjects in the A*C count (eg BTEC) (2:42)
 Use a SUM formula to calculate the total GCSE Points score (4:01)
 Correcting Aspect, Resultset and Gradeset names (2:37)
 Using the MEAN formula to calculate the average points score for a pupil (2:23)
 Using the MAX formula to work out the capped (best 8) GCSE points score (7:11)
Performance 8 Calculations
 Create a LOOKUP table to calculate estimated attainment 8 (7:18)
 Using the LOOKUP formula to calculate the estimated attainment 8 value for a pupil (6:09)
 A note about the 91 values of old AG grades in 2017 and 2018 (2:54)
 Calculating the new 91 points score for old AG grades (4:40)
 Calculate the points values for BTEC and non GCSE subjects (4:31)
 Doubleup maths and adjust other nonstandard GCSE subjects (3:06)
 Doubling up English  but only if they do Lang AND Lit (7:42)
 Identifying the six highest Ebacc subjects taken by the pupil (5:06)
 Using the MAX formula to fill the OTHER element of the P8 calculation (5:26)
 Adding everything together to calculate the A8 figure (3:38)
 The final P8 Calculation for each pupil  well done! (6:46)
Targets and Flightpaths
 What is a target, what is a flightpath and why gradesets are important (3:47)
 Creating flightpaths and targets using a simple PLUS formula (11:58)
 Using a LOOKUP table to create a flightpath or target (11:00)
Course Curriculum

PreviewIntroduction and overview of this course (2:06)

PreviewMARKS TO GRADE: Converting integers to grades for better analysis (14:39)

PreviewNESTED IF THEN ELSE: Using 2 or more nested if then else columns for more colours (11:15)

StartA general discussion about showing in year progress using sub grades (9:20)

StartUse a LOOKUP formula to convert from A*G to 91 (4:36)

StartCreating a LOOKUP table and using the formula to convert to 91 (8:04)

StartUse a GRADE TALLY formula to calculate Total GCSE A*C for a pupil (12:29)

StartIncluding nonGCSE subjects in the A*C count (eg BTEC) (2:42)

StartUse a SUM formula to calculate the total GCSE Points score (4:01)

StartOoops! Correcting Aspect, Resultset and Gradeset names (2:37)

StartUsing the MEAN formula to calculate the average points score for a pupil (2:23)

StartUsing the MAX formula to work out the capped (best 8) GCSE points score (7:11)

StartCreate a LOOKUP table to calculate estimated attainment 8 (7:18)

StartUsing the LOOKUP formula to calculate the estimated attainment 8 value for a pupil (6:09)

StartA note about the 91 values of old AG grades in 2017 and 2018 (2:54)

StartCalculating the new 91 points score for old AG grades (4:40)

StartCalculate the points values for BTEC and non GCSE subjects (4:31)

StartDoubleup maths and adjust other nonstandard GCSE subjects (3:06)

StartDoubling up English  but only if they do Lang AND Lit (7:42)

StartIdentifying the six highest Ebacc subjects taken by the pupil (5:06)

StartUsing the MAX formula to fill the OTHER element of the P8 calculation (5:26)

StartAdding everything together to calculate the A8 figure (3:38)

StartThe final P8 Calculation for each pupil  well done! (6:46)
Get this course as part of the assessment bundle and save money
This course is also available as part of the Assessment Manager Training Course Bundle  all the assessment manager training modules in one cost effective bundle. Find out more here.
Your Instructor
I'm an independent SIMS and Power BI consultant working with primarily with SIMS for nearly 10 years. I spend lots of time in different schools, talking about the best ways to use SIMS and Power BI and creating effective assessment and analysis systems. I run a popular assessment manager blog full of tips and trick for data managers in schools.