Here’s a handy tip for improving the functionality of your student data, particularly when it comes to predicting grades at GCSE and A Level. The formula I will show you uses a lookup table to convert a point score to a grade – A, B, C etc. All you need to know is the grade boundaries (or estimated ones!)
This tutorial assumes only very basic excel knowledge. All you need is an excel workbook with some student performance data.
On a new spreadsheet, create a table of your estimated grade boundaries. Go to the ‘Insert’ tab and click ‘insert table’. It’s important not to skip this step – it’s possible to do a lookup reference by using normal cells, but it’s a pain if you want to apply the lookup to a lot of input cells. Plus, you can add rows and move columns around with impunity.
I would make a column for every unit, one for the total and one for the Grade levels. Don’t forget to name the columns. You need as many rows as you have grades. Under the current system for GCSEs, this would be A*, A, B, C, D, E, F, G (so eight). Obviously that will change from next year but you could substitute anything. You’ll need to enter the lower grade boundaries for each grade (I would advise heavy use of simple formulae for calculating totals where possible to avoid user error). Here’s what mine looks like:
Now, update your table name. This step is optional, but it will make the formula we do in a minute a lot easier to follow.
Click on your table, then select Table1 (if this is your first table in the workbook) from the drop-down next to your formula bar (your menu will look different from mine depending on what other stuff you have in your excel document). If you have select
ed the right table it should become highlighted automatically.
Now click on the label ‘Table1’ and type in something sensible, like grade_boundaries (spaces are not allowed; use an underscore instead or just run the words together)
Ok, it's time to make the lookup. Go back to the tab that has your data on it. Here's mine:
In the left hand column I have the students’ predicted UMS total, but this could be anything – a grade for a specific unit or whatever else you want, as long as there is a named column in grade_boundaries that contains the grade boundaries for that unit. The right hand column is where I want my letter grade to appear.
So, I’m going to select the first cell in my WAG column (red oval). In the formula bar, I am going to type this:
=LOOKUP(AK2,grade_boundaries[UMS], grade_boundaries[Grade])
Where it says ‘AK2’ in my formula, make sure you type the cell reference for your UMS point score (blue oval).
Make your life easier – copy and paste it! All of those parenthesis and brackets are doing a job.
Here’s a translation of that formula into English so you can make your own:
'= ' This tells excel that you are putting in a formula.
'LOOKUP' This is a reference to a built-in excel function (like a shortcut to a piece of code that helps excel calculate stuff. Functions take specific parameters, and this one has space for there. Here they are:
=LOOKUP(Cell you want to look up, the column you want to look it up in, the column containing the result of the lookup)
In our case, that’s
=LOOKUP(AK2,grade_boundaries[UMS], grade_boundaries[Grade])
You should end up with something like this:
Now just grab the bottom right corner of your current working at grade (where it says ‘A’) and drag the handle down to auto-fill your column.
Check a few of them to make sure they are correct (if you miss type the formula or your reference table isn’t correctly formatted it can cause problems).
That’s it! There are lots of applications for the LOOKUP formula in managing data for teachers that I’m sure you will discover, but this is a simple example that I use pretty regularly. I’ll be posting another tutorial soon where I show you how to use this lookup to automatically generate targets for reports and feedback forms.
If you would like to share this information with colleagues on paper, here's a PDF that is nicely formatted for print. Feel free to share this information to make your life, and the lives of your teacher friends, that much easier!