Formatting whilst using letters and numbers [Closed]

Report
Posts
1
Registration date
Monday October 3, 2016
Status
Member
Last seen
October 3, 2016
-
Posts
2720
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
April 19, 2021
-
I would like to conditionally format a spreadsheet based on grade progression, where the grade contains both a letter and number.

The grades are numerical split into 3 lettered tiers i.e 1L, 1M, 1U through to 9U.

I want to have the first test results in column one and the second test in column 2. If the value in column two is higher then column one the format it green.

i.e column 1, 1L and column 2 is 1M then would like the cell in column 2 to go green as it is a positive increase.

Many thanks

1 reply

Posts
2720
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
April 19, 2021
459
Hi Blakey,

First I created a table to let Excel know what the order of your ranking is.
Then I used VLOOKUP to retreive the ranks order.
Formula: =VLOOKUP($B1,$D$1:$E$11,2)>VLOOKUP($A1,$D$1:$E$11,2)
Applied range: =$B:$B



Keep in mind that the rankings table can be placed anywhere (i.e. out of sight, different sheet)

Best regards,
Trowa

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!