If statements, conditions and cell display

[Guest]

I want Excel to background colour a cell, Red, Amber or Green dependent upon
the data in it compared to the data in the cell immediately left of it.

e.g. if the data is equal, or lower value = red
if the data is higher by 1 = amber
if the data is higher by 2+ = green

I have the formulas:

Formula is: =AND(B2-A2<=0,B2<>"",A2<>"")
Format: Red fill ( and I have for Amber and green)

Which work fine.

Now my prob is my data is not all numerical, ie it runs
6a,6b,6c....to...1a,1b,1c where 6a is highest and 1c is lowest. So excel
can't use that formula where letters are involved. I can change the 1c, 1b
etc into average point scores instead. i.e 1c=7, 1b=9,1a=11 etc.
But I wanted Excel to do it so if a member of my staff types in 1c, the cell
displays 7 and if they type in 4a the cell will display 29 etc. I tried
autocorrect, but every cell needs every possibility...does this make sense?
Really appreciate your help. - Bob

 

[Tom Ogilvy]

=AND(OR(LEFT(B2,1)>LEFT(A2,1),AND(LEFT(B2,1)>=LEFT(A2,1),RIGHT(B2,1)<RIGHT(A2,1))),B2<>"",A2<>"")

will tell you if B2 is greater than A2. You should be able to adapt to
equal and less than.

 

[Guest]

Thank you, but it doesn't seem to be working. I put the formula into
conditional formatting so I could do the colours. the results were
inconsistent. any ideas please?