Conditional formatting using formula IF

[Noortje Vollenberg]

Hi!

I can't find a way to make this conditional formatting work that I need. The columns that I am working with are L and M. I need to set up conditional formatting for column M.

A1: Customer	L1: Budget 2018	M1: Revenue 2018
Name	€ 5.450,00	€ 760,00
Name		€ 0,00
Name	€ 500,00	€ 975,00
Name	€ 2.000,00	€ 0,00
Name		€ 450,00

The formatting rules should be the following:

- If M is € 0,00 and L is empty > M cell has white background

- If M is € 0,00 and L is not empty> M cell has red background

- If M is lower than L but more than € 0,00> M  cell has orange background

- If M is equal to or more than L > M cell has green background

So the above table should look something like this:

A1: Customer	L1: Budget 2018	M1: Revenue 2018
Name	€ 5.450,00	€ 760,00
Name		€ 0,00
Name	€ 500,00	€ 975,00
Name	€ 2.000,00	€ 0,00
Name		€ 450,00

But I can't make this work with a formula. I've tried using the IF and ISBLANK formulas, but maybe I'm using them wrong.

Can anybody help me find a formula that works for this? Or is this simply not possible?

Thanks for helping out!

[#2]

Hi Noortje,

Rules could be

red

=(LEN($L2)>0)*($M2=0)

orange

=($M2<$L2)*($M2>0)

green

=($M2>=$L2)*($M2>0)*(LEN($L2)>0)

[#3 · Accepted solution]

Hi,

Top solve your problem you will have to create 4 customized conditional formating rules using a combinaison of these functions: AND, NOT, ISBLANK and these operators >; >=;

See the attached file for the solution

[#4]

Hi Noortje,

You have these four rules in column M:

White Backcolor 

=AND(M2=0, L2="")

Red Backcolor  

=AND(M2=0, L2<>"")

Orange Backcolor 

=AND(M2<L2, M2>0)

Green Backcolor 

=AND(M2>=L2, L2<>"")

Please find the attached file.

[#5]

Hi,
Your solution worked. I had something similar already, but was missing the TRUE function.
Thanks for your help!