Warning: A non-numeric value encountered in /home/itgsor5/public_html/domains/james-greenwood/wp-content/themes/Divi/functions.php on line 5763

A while back I posted a Google Spreadsheet markbook for OCR Nationals level 2 ICT’s oh-so-exciting unit 1. Since posting this as a resource, I’ve updated it to plug a couple of holes (the original markbook was missing multiple recipients as a pass criteria for AO2) and created markbooks for the other units we teach. See below for links. You need to have a Google Docs account in order to save a copy of the spreadsheets in order to enter your data.

There’s nothing terribly sophisticated about them, just a combination of conditional formatting, IF statements & Google’s handy “publish as webpage” feature, but they’re a very simple way to represent progress in an easily understandable way, and have helped my students a lot. It also cuts down on the amount of formative feedback necessary, so I can limit my comments to covering quality & content.

I’ve put the short walkthrough of how to use them effectively at the bottom of the post.

Unit 1: Skills for business


Unit 3: Digital imaging


Unit 8: Innovation in ICT & e-commerce


Unit 21: Computer graphics


Unit 23: Creating video


How to use them:

On the first sheet the only things you need to fill in are students’ names and forms – these are then referenced on each subsequent sheet. The front sheet contains conditional formatting in the form field to correspond with my school’s community colours – you may want to remove that.

From here you start marking work – say we start with AO1, you traffic light the bits the student has done by putting 1 for in-progress (yellow) or 2 for complete (green). Traffic lighting means Barry can very quickly see that he’s doing well, while Stanley needs to pull his socks up.

Once you’ve done, you move all the way to the right, and put a number from 1-4 in the penultimate column, 1 being below pass, 2 being pass, 3 being merit, 4 being distinction. The cell to the right of it has an IF statement that shows the grade for the assessment objective, and this is then fed into the front sheet.

I thought about using a lookup to count up the cells ticked to the left, but as much as writing that would be a minor pain in the arse (especially with kids missing a pass criteria but getting merit ones), I’ve found that when a student has completed all of the requirements for a distinction but their evidence is badly formatted, I don’t want them to see “distinction” for it until they’ve sorted it out.

This shows the front, overview sheet again, with the marks for AO1 updated.

Hope they prove useful.