Excel Conditional formating

Hi Camila

This was a fun challenge to solve!

The important thing to remember about Conditional Formatting is

1) the formatting is only applied when the Condition = True
So any formula we create must produce a TRUE result for the formatting to be applied. No other result is considered.

2) Remember to change cell references between Absolute & Relative to ensure correct values are being looked at. Absolute is used by default so often needs to be changed to relative if applying conditional formatting to a selection.

So for your example you might need to create 3 conditional formats all using the 'Use formula' option

1) if g4 < 80 the cell should be red, this needs tweaking as later you testing for zero (0). So using the AND function allows to test for both conditions.

=AND(G4>0, G4<80)

formatting = RED

2) =G4>80

Formatting = GREEN

3)if g4 = 0 excel should look at D4 and if that =0, cell should be while, if it doesn't, cell should be red red.

=AND(G4=0, D4<>0)

Formatting = RED

Notice: D4 <> (Not equal to) 0
Testing for this as conditional formatting only changes on a true value we cannot have 2 checks. So if D4 = 0 it would produce FALSE and keep the colour white. If the value in D4 is anything else then it will return TRUE and change the colour to RED.


I hope this makes sense!

Kind regards

Dennis