Settings

Open in SheetsFree, with no account. What you type stays in this browser. Saves as Excel, OpenDocument or CSV.

Mark book open in Sheets, in the light themeMark book open in Sheets, in the dark theme

What is in it

Each pupil’s marks for tests, coursework and the exam, weighted as you set, with the overall percentage, the grade from your boundaries, and the class average. The figures are examples to type over; the workbook runs wider than the columns shown here.

Mark book
Each mark is out of 100. The weights in row 4 set how much each counts and need not add up to 100. A mark left empty counts as nought in the overall figure.
Weight20202040
PupilTest 1Test 2CourseworkExamOverall %Grade
[Name]72658070=IF(COUNT(B6:E6)=0,"",ROUND(SUMPRODUCT(B6:E6,$B$4:$E$4)/SUM($B$4:$E$4),1))=IF(F6="","",IF(F6>=$J$5,$I$5,IF(F6>=$J$6,$I$6,IF(F6>=$J$7,$I$7,IF(F6>=$J$8,$I$8,$I$9)))))
[Name]58616655=IF(COUNT(B7:E7)=0,"",ROUND(SUMPRODUCT(B7:E7,$B$4:$E$4)/SUM($B$4:$E$4),1))=IF(F7="","",IF(F7>=$J$5,$I$5,IF(F7>=$J$6,$I$6,IF(F7>=$J$7,$I$7,IF(F7>=$J$8,$I$8,$I$9)))))
[Name]85907888=IF(COUNT(B8:E8)=0,"",ROUND(SUMPRODUCT(B8:E8,$B$4:$E$4)/SUM($B$4:$E$4),1))=IF(F8="","",IF(F8>=$J$5,$I$5,IF(F8>=$J$6,$I$6,IF(F8>=$J$7,$I$7,IF(F8>=$J$8,$I$8,$I$9)))))
=IF(COUNT(B9:E9)=0,"",ROUND(SUMPRODUCT(B9:E9,$B$4:$E$4)/SUM($B$4:$E$4),1))=IF(F9="","",IF(F9>=$J$5,$I$5,IF(F9>=$J$6,$I$6,IF(F9>=$J$7,$I$7,IF(F9>=$J$8,$I$8,$I$9)))))
=IF(COUNT(B10:E10)=0,"",ROUND(SUMPRODUCT(B10:E10,$B$4:$E$4)/SUM($B$4:$E$4),1))=IF(F10="","",IF(F10>=$J$5,$I$5,IF(F10>=$J$6,$I$6,IF(F10>=$J$7,$I$7,IF(F10>=$J$8,$I$8,$I$9)))))
=IF(COUNT(B11:E11)=0,"",ROUND(SUMPRODUCT(B11:E11,$B$4:$E$4)/SUM($B$4:$E$4),1))=IF(F11="","",IF(F11>=$J$5,$I$5,IF(F11>=$J$6,$I$6,IF(F11>=$J$7,$I$7,IF(F11>=$J$8,$I$8,$I$9)))))
=IF(COUNT(B12:E12)=0,"",ROUND(SUMPRODUCT(B12:E12,$B$4:$E$4)/SUM($B$4:$E$4),1))=IF(F12="","",IF(F12>=$J$5,$I$5,IF(F12>=$J$6,$I$6,IF(F12>=$J$7,$I$7,IF(F12>=$J$8,$I$8,$I$9)))))
=IF(COUNT(B13:E13)=0,"",ROUND(SUMPRODUCT(B13:E13,$B$4:$E$4)/SUM($B$4:$E$4),1))=IF(F13="","",IF(F13>=$J$5,$I$5,IF(F13>=$J$6,$I$6,IF(F13>=$J$7,$I$7,IF(F13>=$J$8,$I$8,$I$9)))))
=IF(COUNT(B14:E14)=0,"",ROUND(SUMPRODUCT(B14:E14,$B$4:$E$4)/SUM($B$4:$E$4),1))=IF(F14="","",IF(F14>=$J$5,$I$5,IF(F14>=$J$6,$I$6,IF(F14>=$J$7,$I$7,IF(F14>=$J$8,$I$8,$I$9)))))
=IF(COUNT(B15:E15)=0,"",ROUND(SUMPRODUCT(B15:E15,$B$4:$E$4)/SUM($B$4:$E$4),1))=IF(F15="","",IF(F15>=$J$5,$I$5,IF(F15>=$J$6,$I$6,IF(F15>=$J$7,$I$7,IF(F15>=$J$8,$I$8,$I$9)))))
=IF(COUNT(B16:E16)=0,"",ROUND(SUMPRODUCT(B16:E16,$B$4:$E$4)/SUM($B$4:$E$4),1))=IF(F16="","",IF(F16>=$J$5,$I$5,IF(F16>=$J$6,$I$6,IF(F16>=$J$7,$I$7,IF(F16>=$J$8,$I$8,$I$9)))))
=IF(COUNT(B17:E17)=0,"",ROUND(SUMPRODUCT(B17:E17,$B$4:$E$4)/SUM($B$4:$E$4),1))=IF(F17="","",IF(F17>=$J$5,$I$5,IF(F17>=$J$6,$I$6,IF(F17>=$J$7,$I$7,IF(F17>=$J$8,$I$8,$I$9)))))
=IF(COUNT(B18:E18)=0,"",ROUND(SUMPRODUCT(B18:E18,$B$4:$E$4)/SUM($B$4:$E$4),1))=IF(F18="","",IF(F18>=$J$5,$I$5,IF(F18>=$J$6,$I$6,IF(F18>=$J$7,$I$7,IF(F18>=$J$8,$I$8,$I$9)))))

Worked out for you

These cells are formulas, so they follow whatever you type. Each is shown as written in its first row.

Overall %
=IF(COUNT(B6:E6)=0,"",ROUND(SUMPRODUCT(B6:E6,$B$4:$E$4)/SUM($B$4:$E$4),1))
Grade
=IF(F6="","",IF(F6>=$J$5,$I$5,IF(F6>=$J$6,$I$6,IF(F6>=$J$7,$I$7,IF(F6>=$J$8,$I$8,$I$9)))))
Class average: Test 1
=IFERROR(ROUND(AVERAGE(B6:B35),1),"")
Class average: Test 2
=IFERROR(ROUND(AVERAGE(C6:C35),1),"")
Class average: Coursework
=IFERROR(ROUND(AVERAGE(D6:D35),1),"")
Class average: Exam
=IFERROR(ROUND(AVERAGE(E6:E35),1),"")
Class average: Overall %
=IFERROR(ROUND(AVERAGE(F6:F35),1),"")

Filling it in

  • Set the weights in row 4 to match how the course is assessed. They are relative, so 1, 1, 1 and 2 works as well as 20, 20, 20 and 40.
  • Change the grade boundaries in the table on the right, and every grade updates.
  • Enter a nought only when the work was handed in and scored nought. A blank also counts as nought in the overall figure, so add the mark as soon as you have it.
  • Pupils’ names and marks are personal data. This workbook stays in your browser, and on any device you save it to.

More templates

All 32 templates