Excel Challenging Puzzle

[AaronL1990]

Hi everyone.

I'm fairly experienced in Excel but I am facing a very annoying issue. I'm a teacher and I have an electronic mark book for keeping track of my students grades. What I want to achieve is the following:

In column A, I have my students names
In column B, I have their target grade
In column C, D, E and F, I have their grades for assessments they've completed.

I'd like columns C,D,E,F to change colour depending on whether or not they've met their target grade. RED - BELOW, AMBER - EQUAL, GREEN - ABOVE. I've managed to get half way there I think but for some reason my formula isn't always correct. It's failing where, for example, if a students target grade is 4B, but they get either a 4C (which is lower) or 4A (which is higher) ... the cells always show the wrong colour! I've spent hours tinkering with the formula and looking up different solutions but I'm running out of hair now!

I've attached my spreadsheet with some mock data in to show where it's going wrong.

If anyone can help me with my issue I'd be eternally grateful.

Thanks for reading and I welcome any advice.

[:) Sixthsense :)]

In D2 Cell


=IF(C2="",0,IF(B2=C2,2,IF(CODE(RIGHT(UPPER(C2)))<CODE(RIGHT(UPPER(B2))),3,1)))


Drag it down...

[AaronL1990]

Thank you for your reply! That's almost fixed it for me. It only works if the number stays the same. If we go from a 4B to a 5C then it should show 3 GREEN and not 1 RED.

I appreciate any further help you can give!

[:) Sixthsense :)]

Revised Formula


In D2 Cell


=IF(C2="",0,IF(B2=C2,2,IF(CODE(RIGHT(UPPER(C2)))<CODE(RIGHT(UPPER(B2))),3,IF(VALUE(LEFT(B2))<VALUE(LEFT(C2)),3,1))))


Drag it down...

[AaronL1990]

Thank you very much! SOLVED