Previous Topic Next Topic

Formatting whilst using letters and numbers

Closed
Blakey3790 Posts 1 Registration date Monday October 3, 2016 Status Member Last seen October 3, 2016 - Oct 3, 2016 at 04:15 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Oct 4, 2016 at 11:37 AM
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
Related:

1 response

Answer 1 / 1
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Oct 4, 2016 at 11:37 AM
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