Making school data easy to track

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!

Hi, Looked at your worksheet but i am not too sure what do you want to do.
Do you want to change the values in your table based on list given in row 12 onwards for example you have
3c in cell B3 now do you want to change it to 7 or do you want a new table/column next to your values.
Thanks/VKS

Hi - I would like it if the level (e.g. 3c) still stays in the column. But somewhere excel secretly knows that 3c is worth 7.

this way teachers are still looking at it and seeing the accurate level of the child. I can also though track their progress term by term with colour formatting. I was thinking of a solution (which I can't do because I don't know enough about Excel!) of having a hidden column next to the level and using the hidden columns to have my conditional formatting in (e.g. if C3>B3 C3 will go green).

Hi-

try;


Regards,
Event

to get green value formula at conditional formating
=MATCH($B3;$A$12:$A$29;0)<MATCH($C3;$A$12:$A$29;0)

to get red
=MATCH($B3;$A$12:$A$29;0)>MATCH($C3;$A$12:$A$29;0)

see attachment

This is PERFECT - thank you so much - is there anyway you can explain to me how it works so I can make amendments, or change colours etc.

Explain the magic - please!

Who are you referring to!

Hi -

If you're pertaining to mine, click on C3 > Conditional Formatting > Manage Rules and you can see the formula there.

Regards,
Event

Thank you very much