Solved conditional formatting with if(and)

November 13, 2015 at 14:29:24
Specs: Windows 7
I need to use conditional formatting to change the color of a cell if these conditions exist:
if a1 < 50 and a2 < 5% then turn yellow
if a1 > 49 and a2 < 7% then turn yellow

I tried =if(and(a1<50,a2<5%),if(and(a1>49,a2<7%)
but it doesn't work and I am not sure if a traditional formula can work in conditional formatting.

any suggestions?

See More: conditional formatting with if(and)

Report •

November 13, 2015 at 14:44:18
You have A1 as either less then 50 or more than 49,
which means A1 can, effectively, be any number.
So why bother with A1 at all?

A bit more of an explanation of what your trying to do might help.


message edited by mmcconaghy

Report •

November 13, 2015 at 14:47:49
if the sales person has less than 50 leads he has to have at least a 5% conversion rate but after he gets more than 50 leads he has to have at least a 7% conversion rate

Report •

November 13, 2015 at 14:54:55
✔ Best Answer
One of the oldest misconceptions about Conditional Formatting is that an IF statement is required.

Actually, the only requirement is that the formula return TRUE.

For either of your 2 conditions, all you really need is an AND function:


If you put those formulas in a cell, you will find that they will return either TRUE or FALSE, which is good enough for Conditional Formatting.

You can either use those formulas as 2 separates Rules in CF, setting the fill color to Yellow for both Rules, or you could combine them into a single formula that returns TRUE when the proper conditions are met:


BTW...are you aware that your conditions overlap?

A1 = 49.5
A2 = 4%

In this case, both ANDs are TRUE.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

November 13, 2015 at 15:21:50
wow never knew that about CF i did think an if statement was required. the formula worked. Numbers will always be whole.

last question how can i tell CF that for cell b1 to be yellow if either a2 or a5 is yellow. since there is no math to consider but rather a color dont know how to tell CF

Report •

November 13, 2015 at 15:31:32
Of course there is math involved.

Simply use the same conditions that would have turned either A2 or A5 Yellow. When either of those conditions are met, B1 will turn whatever color you CF'd it to be.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

November 14, 2015 at 08:12:11
Still having trouble with this last CF formula
I need a cell to turn yellow if any one of the minimums are not met, here is what I have so far (it always returns true even if it should be false):
formula i am using:

I want it to return true only if (F29<50,H29<5% OR F29>49,H29<7%)

thank you for your help :)

Report •

November 14, 2015 at 15:47:57
Why did you mark the thread solved if you are still having problems?

Perhaps you should read up on the syntax of the OR function. You only need one OR at the beginning of the formula. If any of the conditions inside the parentheses are TRUE the entire OR is TRUE.

e.g. =OR(A1=5, B1>8, AND(C1="Tom", D1<>"Sue"), A1+F1=12)

As long as at least one of those 4 conditions are TRUE the OR will return TRUE. If none of them are TRUE the OR will return FALSE.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

November 15, 2015 at 17:38:51
Thanks DerbyDad03, sorry I thought it was solved prematurely. I re-wrote the formula as follows:


Thanks for the help, you've helped me on other threads as well, I appreciate your help.

Report •

Ask Question