If Formula - conditional formatting - three different formatting rules

Hi,

I was looking for some help. If anyone is willing to point my in the right direction, I'd be incredibly grateful. I'm currently trying to use conditional formatting to colour cell in column E one of two different colours using the "Use a formula to determine which cells to format" option.

Basically the rules I wish to apply are;


If cell in column D = "Promotion" from the data validation drop down options, then DO NOT apply conditional formatting at all.

However If cell in column D = "Nominal increase" from the data validation drop down options, THEN apply conditional formatting as per below;


CONDITIONAL FORMATTING RULES
IF cell in column C = "London" AND cell in column E < 5.01 THEN colour Green
IF cell in column C = "London" AND cell in column E > 5.01 then colour Red

OR

If cell in column C <> "London" AND cell in coumn C < 3.01 THEN colour Green
If cell in column C <> "London" AND cell in column C > 3.01 THEN colour Red



I think it's just an IF formula with an "AND" & "OR" included, but I've never been very good at the syntax around these.

Also I'm not sure how I can apply 2 conditional formatting formulas to one column & have both in action at the same time...

A copy of my worksheet is attached, there are already some very simple formulas applied, the understanding would be that information would be selected from the drop down option in column D & then a figure input by the end user into column E only. All the other columns will be locked down so they are for information purposes only. The conditional formatting would be used to bring to the end user's attention whether the information they've input into column E is acceptable.


Hope that makes sense, please let me know if I need to supply anything else.

I'm sorry I wasn't clear, was I?


Please see below revised rules, I messed that up slightly previously.

CONDITIONAL FORMATTING RULES
IF cell in column C = "London" AND cell in column E < 5.01 THEN colour Green
IF cell in column C = "London" AND cell in column E > 5.01 then colour Red

OR

If cell in column C <> "London" AND cell in coumn E < 3.01 THEN colour Green
If cell in column C <> "London" AND cell in column E > 3.01 THEN colour Red

Does that make more sense?

Sorry sorry!

Ok, maybe I should use words.

If value in E column is between 3.01 & 5.01 AND value in column C is london = colour green.

If value in column E is between 3.01 & 5.01 AND value in column c is NOT london = colour red.

Basically in real life terms, people in london have an allowance to go up to 5.01 & it still be "ok", i.e. colour green .
People based outside of london can only go up to 3.01. If they go above this then that's "bad" & they get coloured red.

Does that make more sense?

Hi there!

Thanks so much for getting back to me.

I'm not sure this is quite working the way I hoped. I probably didn't explain myself properly again, sorry.

So I would hope that every cell should be coloured red or green;

Green if column C is london AND in column E is BELOW 5.01
Red if column C is london AND in column E is ABOVE 5.01

Green if column C is NOT London AND in column is ABOVE 3.01
Red if Column C is NOT london AND in column E is above 3.01


Would you be able to pull that together for me? Thank you so much for your help so far, I'm sorry this is taking a long time to explain!

Oh, no I think I might have confused the matter earlier...

So;

Red If C2 = London AND E2 is ABOVE 5.01
Green if C2 = London AND E2 is BELOW 5.01

That's one condition totally to itself.
AND THEN

Red if C2 <> London AND E2 is ABOVE 3.01
Green if C2 <> London AND E2 is BELOW 3.01

So there will always be either "London" or not "London" in column C & there will always be a number in E2.

Does that make more sense?

Yup, I think I'm confusing you here.

So it's not simply when the numbers are between 3.01 & 5.01 that colours need to be applied. I'll try & explain my thinking below again;

So Increase Type = column D
Location = column C
Increase % = column E


If Increase Type = “Promotion”, do nothing

OR

(If Increase Type = “Nominal Increase”,

THEN

Red If Location = London AND Increase % is ABOVE 5.01
Green if Location = London AND Increase % is BELOW 5.01

OR

Red if Location <> London AND Increase % is ABOVE 3.01
Green if Location <> London AND Increase % is BELOW 3.01 )



Am I at all helping here?...

Yup, I think I'm confusing you here.

So it's not simply when the numbers are between 3.01 & 5.01 that colours need to be applied. I'll try & explain my thinking below again;

So Increase Type = column D
Location = column C
Increase % = column E


If Increase Type = “Promotion”, do nothing

OR

(If Increase Type = “Nominal Increase”,

THEN

Red If Location = London AND Increase % is ABOVE 5.01
Green if Location = London AND Increase % is BELOW 5.01

OR

Red if Location <> London AND Increase % is ABOVE 3.01
Green if Location <> London AND Increase % is BELOW 3.01 )



Am I at all helping here?...