Conditional Formatting

[JackNevin]

Hi
In one of my spreadsheets I have conditional formatting. The conditional formatting referes to the grade red for below, green for on target and blue for above target.
However the grade are ranked as for example

3a
3b
3c

with 3a being the highest and 3c being the lowest grade, but when a students target grade is 3b and they are on a 3a for that test it shows up red as if they are performing below target when they are above.

Is there anyway of correcting this so it will rank 3a being the highest grade and 3c being the lowest?

If any suggestion please comment!
Please see attachment to view the spreadsheet

[martindwilson]

where exactly is there en example of this?

[JackNevin]

If you click on the English Grades tab

and you will see the first students target grade is 3b however if you scroll across to the 6th test you will see it shows 55% with means 3a which should highlight blue. This is the same with all levels 2,3,4 and 5 are all ranked 3a, 3b and 3c.

[Richard Buttrey]

Hi,

Why not just swap the > and < signs around for the red and blue cond formats. That will work where grade numbers are the same.

You don't however say whether say a grade 4 plus any letter should be higher or lower than a grade 3 any letter. Depending on the answer to this you may need to construct cfs which use both the grade number and grade letter in an AND condition in the cf formula.

Regards

[JackNevin]

swapping the signs around will only work with grades that are for example 4a when there target is 4b it will change it from red to blue and doing that will affect all the other grades above 4b for example 5a, 5b and 4a will show up red when it fact they are above target.

[martindwilson]

trouble is that 5c is greater than 5a! so you need to sort that out
=LOOKUP(A1,{"2a","2b","2c","3a","3b","3c","4a","4b","4c","5a","5b","5c"},{3,2,1,6,5,4,9,8,7,12,11,10})
but its easier to create 2 named ranges as its a bit shorter
here is one way using lookup and named ranges for use in conditional format

[JackNevin]

Thanks

Il try it out properly tomorrow and get back on this post and let you know how it comes out!

Regards

[Richard Buttrey]

...which is why I asked the Q whether say a grade 4 is higher or lower than a grade 3. As I say you'll need to use an AND test and deal with the numbers and letters independently.