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

and

if a1 > 49 and a2 < 7% then turn yellowI 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?

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.

MIKE

message edited by mmcconaghy

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

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:

=AND(A1<50,A2<5%)

=AND(A1>49,A2<7%)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:

=OR(AND(A1<50,A2<5%),AND(A1>49,A2<7%))

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.

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

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.

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:

=OR(AND(F29<50,H29<5%),OR(F29>49,H29<7%),OR(C25<95%),OR(D29<85%),OR(E29<65%))I want it to return true only if (F29<50,H29<5% OR F29>49,H29<7%)

or

C25<95%

or

D29<85%

or

E29<65%thank you for your help :)

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

anyof 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.

Thanks DerbyDad03, sorry I thought it was solved prematurely. I re-wrote the formula as follows: =OR(AND(F29<50,H29<5%),AND(F29>49,H29<7%),C29<95%,D29<85%,E29<65%)

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

Ask Your Question

Weekly Poll

Do you trust smart speakers to not spy on you?

Discuss in The Lounge

Poll History