1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.
  2. Hi Guest, welcome to the TES Community!

    Connect with like-minded professionals and have your say on the issues that matter to you.

    Don't forget to look at the how to guide.

    Dismiss Notice

Inputting KS3 Grades In Excel

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

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

    littlemac New 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. VLOOKUP tables are the way to go with this problem ...
  5. 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. In fact I've just uploaded it to my resources so if you click on my name you will find it there. :)

Share This Page