Can I use conditional formatting for none numeric data?

[helencj]

I'm working on a school sheet that records students predicted grades against their target grades for their GCSEs. They have a column for their target grade (A* to G) then an adjacent column into which their teacher puts their projected grade. I want to be able to colour the projected grade column red if they are below target and green if they are on or above target. There are several hundred individual grades, so other than doing each one individually is there a general formula I can use?

[Aardigspook]

Hi, welcome to the forum.

See the attached file for one way to get this to work: CF for Target v Projected grades _ for helencj.xlsx

It contains a list of grades in column E matched to numbers in column F (A* = 8, A = 7, etc). The target grades are in column A and projected grades in column B. The grades in Column B were selected, then 'Conditional Formatting' was clicked, 'New Rule' selected, then 'Use a formula to determine which cells to format'. This formula was then put in the formula box, with a red fill applied using the 'Format' button:

Please Login or Register  to view this content.

The same was then done with this formula with a green fill:

Please Login or Register  to view this content.

I hope that makes sense so you can apply it to your own file.

ps If it were me, I'd use another colour (blue?) for projected grades above target, from the green colour for on target - but maybe that's just me...

[FDibbins]

On it's own, excel does not know that A > B, or F < D - I am assuming this is kind of what you are looking for?

If so, we can add a small table that gives each letter a "score" A-G = 1-7, for instance, then use a vlookup into that table to test for <= target

Edit: I see that Aardigspook has the same idea

[José Augusto]

Or try to convert "A" .."G" in 6 downto 0 with

Please Login or Register  to view this content.

or 0 to 6

Please Login or Register  to view this content.

[sandy666]

Select rProjected range, open CF and use it for:
RED: =IF(CODE(B2)>CODE(A2),1,0)
GREEN: =IF(CODE(B2)<=CODE(A2),1,0)
where A2 Target, B2 Projected

No additional columns or tables


If you want more, check here

[FDibbins]

Sandy, that was a REALLY slick way of doing that, well done!!

1 small addition would be to test for either UPPER or LOWER (doesnt matter which) on the grades...a < B, so that would cover someone entering lower case grade against an upper case target

[sandy666]

No problem:
RED: =IF(CODE(UPPER(B2))>CODE(UPPER(A2)),1,0)
GREEN: =IF(CODE(UPPER(B2))<=CODE(UPPER(A2)),1,0)


edit:
Thanks for rep, Ford

Additionally against numbers:
YELLOW:=ISNUMBER(A2) with range An:Bm (e.g. A2:B50)
hope it will be exhaustive solution

of course DataValidation can be used or any other method.

[FDibbins]

Nicely done

[helencj]

Wow, thank you for all the fantastic replies! I am a complete noob at this and learned loads just by reading all your answers. I used Sandy's solution and added in an extra line to make them blue if they are above target (thank you for the suggestion.

[sandy666]

You are welcome and thanks for the rep

[Tony Valko]

RED: =IF(CODE(UPPER(B2))>CODE(UPPER(A2)),1,0)
GREEN: =IF(CODE(UPPER(B2))<=CODE(UPPER(A2)),1,0)

If those are being used in conditional formatting you don't need the IF function:

=CODE(UPPER(B2))>CODE(UPPER(A2))

=CODE(UPPER(B2))<=CODE(UPPER(A2))

In conditional formatting the format will be applied if the formula returns either TRUE or any number other than 0.

[Aardigspook]

@helencj
You're welcome and thanks for the rep. Sorry I haven't replied earlier, but I seemed to have some problems accessing the site.

@sandy666
Very clever - I never even thought of CODE. Have some rep

[sandy666]

Thank you, Aardigspook