Excel 2010 doesn't know that a B grade is lower than a B+ grade?

**diatoday** · 02-17-2013, 01:51 PM

Hi, all,

In column b I have letter grades like B- B and B+
In column c I have the same kinds of letter grades

I have conditionally formatted column c to have a green background if a cell in column c is higher than the cell to the left, column b
I have conditionally formatted column c to have a red background if a cell in column c is lower than the cell to the left, column b
I have conditionally formatted column c to have no background if a cell in column c is the same letter grade as the cell to the left, column b

This works fine EXCEPT Excel 2010 thinks that B is higher than B+; it thinks D+ is lower than D-

Is there a fix for this other than to work off a hidden column with numeric values?

BTW: Columns B and C are copied from cells in other pages of the spreadsheet. Those original cells use the lookup function to create the letter grades

Help, anyone?

**FDibbins** · 02-17-2013, 02:25 PM

Hi and welcome to the forum

Perhaps what you could try, would be to put all the grades in a table, and give each 1 a numeric value...eg A+ = 10 A = 9 etc. You could then use a vlookup to return the numeric value, and/or base the CF on that? you could hide the helper column used to find the value

**Winon** · 02-17-2013, 02:30 PM

@ FDibbins,

Great answer, since computers cannot think unless you tell it what to "Think"!

**FDibbins** · 02-17-2013, 02:34 PM

Thanks Winon

Hey we fly out there tomorrow

**Winon** · 02-17-2013, 02:41 PM

@ FDibbins,

Great! Sunny and warm on this side of the globe!

No reply to my answer on your PM?

**Pepe Le Mokko** · 02-17-2013, 03:29 PM

This works fine EXCEPT Excel 2010 thinks that B is higher than B+; it thinks D+ is lower than D-

This is not quite correct ( might be interesting to understand why you think this?). If you compare you will see that XL sees it is as follows "B+">"B-">"B". ( same for other letters)
Of course you then still have the problem of B- which is solved as proposed in post #2