Making school data easy to track

[jjmmoo]

The Problem
I need to make data in a school worth something. If anyone can help me set up something whereby every level in the school system is worth a specific value I would really appreciate the help.

I need these values
1c = 1
1b = 2
1a = 3
2c = 4
2b = 5
2a = 6
3c = 7
etc. right up to 6a being worth 18

I've attached a spreadsheet with some example data. Is is possible to do it by using hidden columns or set up a separate sheet which is able to affect the main sheet?

Why?
Essentially each half term in school children are given a level to denote how they are doing. I need to be able to see at a glance which children are making progress through the year, which ones are stagnant and which ones are falling behind.

I would like to colour code eventually (using Conditional formatting) Aut 2 column to say is a child's Aut 2 level is > than their Aut 1 level the cell will turn green. If it's the same (=) then the cell will turn orange, if the level in Aut 2 is < than the level in Aut 1 then the cell will turn red.

I'm confident of doing the colour coding - what I can not do is make excel realise that 1c is less than 1b and 1a, or 4c is less than 4b, 4a. Understandably excel sees 4c as higher than 4b and 4a because our alphabet goes a, b, c not c, b, a!