Advanced Marksheet Techniques

Essential CPD for assessment co-coordinators and data managers.

Over three hours of video lectures that take you step-by-step through real-world assessment problems. Learn how to:

  • Use nested-if-then-else 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


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 9-1 (4:36)
  • Creating a LOOKUP table and using the formula to convert to 9-1 (8:04)
  • Use a GRADE TALLY formula to calculate Total GCSE A*-C for a pupil (12:29)
  • Including non-GCSE 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 9-1 values of old A-G grades in 2017 and 2018 (2:54)
  • Calculating the new 9-1 points score for old A-G grades (4:40)
  • Calculate the points values for BTEC and non GCSE subjects (4:31)
  • Double-up maths and adjust other non-standard 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

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

David Pott
David Pott

I'm an independent SIMS and Power BI consultant working with schools for over 15 years. I spend lots of time with different schools, talking about the best ways to use SIMS and Power BI and creating effective assessment and analysis systems.

Frequently Asked Questions

When does the course start and finish?
The course starts now and never ends! It is a completely self-paced online course - you decide when you start and when you finish.
How long do I have access to the course?
How does lifetime access sound? After enrolling, you have unlimited access to this course for as long as you like - across any and all devices you own.
What if I am unhappy with the course?
We would never want you to be unhappy! If you are unsatisfied with your purchase, contact us in the first 30 days and we will give you a full refund.

Get started now!