Excel Mock Exam Model
23rd January 2015,
Dear Colleagues,
This week has a very
Secondary focus, sorry Primary. I am
focusing on trying to make life a little easier when it comes to Mocks and exam
marking. I have attached a
spread sheet model which will take some of the pain out of calculating your
grades, percentages and tracking progress of your students against their
ALIS/YELLIS data. The main drawback is
that you have to input the student data initially, this can be copied and pasted from the class lists on the school network, once this is done, you have it completed for the remainder of the
course. It can also be adapted for use
with other exams in preparation for the upcoming IGCSE and A-Level Exams.
The model has one
major assumption, that your course consists of two exam papers, I know this is
not true for all courses, however, I can adapt and change this for anyone who
would be interested. Please email me with
specifics and I will send you a more appropriate model that meets your course
needs.
The spread sheet
consists of two worksheets, Class and Grades.
On the ‘Class’ sheet you will notice six grey columns. These are the columns which require data; student data, ALIS/YELLIS grade and the raw student marks from paper
1 and paper 2. You will also have to input
the total mark for the paper at the bottom of the paper 1 and 2 columns. This is then used to work out the percentages. I have
included instructions in blue boxes on the worksheets which explain what data
needs to be input into each column and cell.
The second worksheet
called ‘Grades’ is where you will input your grade boundaries for each exam
paper. This means that the formulae on the
‘Class’ worksheet will automatically calculate your grades once you enter the student’s
raw mark for each paper. If you are
using the model for an A-Level class you can simply leave the F and G cells
blank and the model will ignore these grades.
Once you have
entered the required data in the grey columns click back onto the ‘Class’
worksheet. Here you will notice the
final column called ‘Review’. This compares
the student performance on the exam with their ALIS/YELLIS target grade. This is returned as a number. Minus numbers mean they are performing below
their target grade and the higher the number the more grade boundaries they are
down. For example, Neil Groves is listed
as having an A* target on the attached spread sheet. He actually achieved an E, which means the ‘Review’
column shows -5 as he is five grades below his target, lazy sod. The ‘Review’ column has also been colour
coded so you can easily spot those students who are exceeding their targets and
identify those who may require a little extra help.
I hope this make
sense and I hope I have not used too much jargon. If you have any questions please email me and I
will try and help. I’m also sure Mr Ben will
be able to provide even more insight into the world of Excel if you ask him
nicely.
I have attached Dan's Physics spreadsheet. It works in a
slightly different way but essentially performs the same role, so a combination
of parts from either worksheet may suit different subjects or teachers. Other
than the students’ details, you only need enter raw marks, the total mark for
the paper (at the top) and the paper weightings. It will then calculate
individual paper percentages, a total weighted percentage and grade and a
recommendation for core/extended entry based upon their final percentage. It
will assign a colour to the final percent cell based on the mark, from green
through yellow to red. Yellow is centred on the percentage for a C grade. There
is a separate sheet for IGCSE, AS and A2.
To change the grade boundaries you only need change them in the small
table at the below bottom left of the student data. The percentages are written
so as to allow for rounding up.
To change the core/extended recommendation then this is looked up from
that same table as the grade boundary for a C. If anyone would like help with
any of the functions please ask Dan and he will be glad to give you a hand.
No comments:
Post a Comment