# Inputting KS3 Grades In Excel

Discussion in 'Heads of department' started by bobwebb, Jul 28, 2011.

1. ### bobwebb

Hi,
I want create an excel document that people in my department can put KS3 levels into and as a result if a student matches their target grade the inputted level goes yellow, if they exceed it goes green and if they fall short it goes red. I can use conditional formatting on excel 2007 to get the colours correctly but excel thinks that 5c is higher than 5b and that 5b is higher than 5a etc. Is there anyway to correct this problem?

2. ### blackpoolgirl

Hi,

The 'easiest' way I have been able to do this is to convert the levels to a numerical version (e.g. 3c = 1)This is done using the INDEX and MATCH function. From this you can then do a simple IF statement and then some conditional formatting.

Hope that helps!

3. ### littlemacNew commenter

In our school for tracking purposes on excel we input levels with a decimal point.
e.g. 4c = 4.0 4b= 4.3 4a=4.7

This way you can keep using conditional formatting and it allows you to filter the data, as well as being able to average out levels if you have different test scores. As an MFL teacher it is really useful to average out the listening, speaking, reading and writing levels and then round up/down to an overall level.

4. ### prescotta

VLOOKUP tables are the way to go with this problem ...

5. ### suzyfulton

Hi,
I've created a spreadsheet with a VLOOKUP table and would be happy to email this to you if you like. I have set it up to highlight green / red / orange depending on what their previous grade was in the preceeding square. Give me your email and I will send it to you

6. ### suzyfulton

In fact I've just uploaded it to my resources so if you click on my name you will find it there.