Conditional Formatting Simplification

Question

I have two questions about conditional formatting. 1 issue I have the output isn't correct, the 2nd I want to know if there just an easier way to accomplish the same output.

Question_1:

I am currently comparing two numbers (A1, A2)and I have the difference in a third cell (A3) I want to change Change the colour of Cell A1 based on the value of A3.

This is what I have and it is all coming up either green or red.

AND(A3>5.01,A3<-10.01)  Colour == Amber

A3<-10.01               Colour == Red

AND(A3=0,A3>-5)         Colour == Green

A3 >0                   Colour == Blue

Question_2

I have 7 possible grades and all grades have a point 1,2 and 3. So A1,A2,A3 ETC

I want to colour the current grade to see if it is above or below their predicted grade. My issue is that as there are 21 possibilities I am creating conditional formatting for each possibility against each possibility. Both for if it is above and below. Below is a snippet just for the A1,A2,A3 to see if it is above the predicted grade.

Can anyone think of anything quicker to accomplish this?

TIA!

=IF(AND(S4="A1",U4="A2"),TRUE,IF(AND(S4="A1",U4="A3"),TRUE,IF(AND(S4="A1",U4="B1"),TRUE,IF(AND(S4="A1",U4="B2"),TRUE,IF(AND(U4="A1",S4="B3"),TRUE,IF(AND(S4="A1",U4="C1"),TRUE,IF(AND(S4="A1",U4="C2"),TRUE,IF(AND(S4="A1",U4="C3"),TRUE,IF(AND(S4="A1",U4="D1"),TRUE,IF(AND(S4="A1",U4="D2"),TRUE,IF(AND(S4="A1",U4="D3"),TRUE,IF(AND(S4="A1",U4="E1"),TRUE,IF(AND(S4="A1",U4="E2"),TRUE,IF(AND(S4="A1",U4="E3"),TRUE,IF(AND(S4="A1",U4="F1"),TRUE,IF(AND(S4="A1",U4="F2"),TRUE,IF(AND(S4="A1",U4="F3"),TRUE,IF(AND(S4="A1",U4="G1"),TRUE,IF(AND(S4="A1",U4="G2"),TRUE,IF(AND(S4="A1",U4="G3"),TRUE,IF(AND(S4="A1",U4="F"),TRUE,IF(AND(S4="A1",U4="U"),TRUE,IF(AND(S4="A2",U4="A3"),TRUE,IF(AND(S4="A2",U4="B1"),TRUE,IF(AND(S4="A2",U4="B2"),TRUE,IF(AND(U4="A2",S4="B3"),TRUE,IF(AND(S4="A2",U4="C1"),TRUE,IF(AND(S4="A2",U4="C2"),TRUE,IF(AND(S4="A2",U4="C3"),TRUE,IF(AND(S4="A2",U4="D1"),TRUE,IF(AND(S4="A2",U4="D2"),TRUE,IF(AND(S4="A2",U4="D3"),TRUE,IF(AND(S4="A2",U4="E1"),TRUE,IF(AND(S4="A2",U4="E2"),TRUE,IF(AND(S4="A2",U4="E3"),TRUE,IF(AND(S4="A2",U4="F1"),TRUE,IF(AND(S4="A2",U4="F2"),TRUE,IF(AND(S4="A2",U4="F3"),TRUE,IF(AND(S4="A2",U4="G1"),TRUE,IF(AND(S4="A2",U4="G2"),TRUE,IF(AND(S4="A2",U4="G3"),TRUE,IF(AND(S4="A2",U4="F"),TRUE,IF(AND(S4="A2",U4="U"),TRUE,FALSE)))))))))))))))))))))))))))))))))))))))))))

=IF(AND(S4="A3",U4="B1"),TRUE,IF(AND(S4="A3",U4="B2"),TRUE,IF(AND(s4="A3",u4="B3"),TRUE,IF(AND(S4="A3",U4="C1"),TRUE,IF(AND(S4="A3",U4="C2"),TRUE,IF(AND(S4="A3",U4="C3"),TRUE,IF(AND(S4="A3",U4="D1"),TRUE,IF(AND(S4="A3",U4="D2"),TRUE,IF(AND(S4="A3",U4="D3"),TRUE,IF(AND(S4="A3",U4="E1"),TRUE,IF(AND(S4="A3",U4="E2"),TRUE,IF(AND(S4="A3",U4="E3"),TRUE,IF(AND(S4="A3",U4="F1"),TRUE,IF(AND(S4="A3",U4="F2"),TRUE,IF(AND(S4="A3",U4="F3"),TRUE,IF(AND(S4="A3",U4="G1"),TRUE,IF(AND(S4="A3",U4="G2"),TRUE,IF(AND(S4="A3",U4="G3"),TRUE,IF(AND(S4="A3",U4="F"),TRUE,IF(AND(S4="A3",U4="U"),

               

Answer 1

Question 1: I think you simply have an error in the formulas and the minus sign is missing in the first one on the 5. Do this instead:

AND(A3>=-10.01,A3<=-5.01)  Colour == Amber

A3<-10.01               Colour == Red

AND(A3<=0,A3>-5.01)         Colour == Green

A3 >0                   Colour == Blue

I also adjusted the formulas to use >= and < to make sure there are no values like -5.01 that "slip through the cracks" when the value is exactly -5.01 or -10.01 and match none of the conditions.

Question 2:

You could make a table like this which sorts the grades in increasing order:

|Grade   |
|========|
| A1     |
| A2     |
| A3     |
| B1     |

etc. Then use the MATCH function to retrieve the order in the list and simply subtract, i.e.:

=MATCH(S4,$B$5:$B$8,0)-MATCH(U4,$B$5:$B$8,0)

where $B$5:$B$8 is a reference to the list of grades in ascending order.

Or if you want the boolean TRUE or FALSE you can compare the subtraction to 0:

=(MATCH(S4,$B$5:$B$8,0)-MATCH(U4,$B$5:$B$8,0))>0