Recent Posts
Featured Posts

Data Wizard Tutorial: Use a LOOKUP reference to calculate grades from point scores.


Wizard Girl.jpg

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.

  1. 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.

Untitled.png

  1. 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:

Untitled1.png

  1. 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.

  2. 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

Untitled2.png

  1. 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)

Untitled3.png

Untitled4.png

  1. Ok, it's time to make the lookup. Go back to the tab that has your data on it. Here's mine: