Cannot find my error with conditional formatting w/ logical statements

mwebb

New Member
Joined
Mar 17, 2016
Messages
9
I have conditional format rule:

=IF(AND($K$2>0.5,OR($L$2<>"Yes",$L$2<>"N/A")),TRUE,FALSE)

I want, "If K2 is greater than .5 and L2 does not equal "Yes" or "N/A", then apply formatting, otherwise apply no formatting.

I don't understand why my formula doesn't work, but it seems to just never apply formatting. I'm sure it is a easy fix.

Thanks,

Michael
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
You don't need an IF statement. Basically be creating a Boolean expression it returns TRUE/FALSE inherently.
However, he issue is you need AND, nor OR. OR means if either is TRUE, the whole expression is TRUE. You want both to be TRUE, not just one, i.e.
Code:
[COLOR=#333333]=AND($K$2>0.5,$L$2<>"Yes",$L$2<>"N/A")[/COLOR]
Note. If you are checking for the #N/A error and not literal text "N/A", try:
Code:
[COLOR=#333333]=AND($K$2>0.5,$L$2<>"Yes",ISNA($L$2)=FALSE)[/COLOR]
 
Last edited:
Upvote 0
Are you saying that you want L2 to either be "yes" , or to be "N/A",

Or are you saying that you don't want L2 to be either one...?
 
Upvote 0
I am checking for the string "N/A" not the error code. I only want to format the cells if K2 is greater than .5 AND L2 is NOT "Yes" OR "N/A". In other words, if L2 is "Yes" OR "N/A" then even K2 is greater than .5 it will not apply the rule.

Thanks
 
Upvote 0
So, if K2 > .5 then TRUE unless L2 = "Yes" OR L2 = "N/A". Hopefully this makes sense.
 
Upvote 0
I figured it out myself: =IF($K2>0.5,(IF($L2="Yes",FALSE,IF($L2="N/A",FALSE,TRUE))),FALSE)
 
Upvote 0
After playing around a bit with some Boolean algebra, I came up with this version:

=NOT(OR($K2<=0.5,$L2="Yes",$L2="N/A"))

or even:

=AND(K2>0.5,L2<>"Yes",L2<>"N/A")
 
Last edited:
Upvote 0
Code:
[COLOR=#333333]=AND(K2>0.5,L2<>"Yes",L2<>"N/A")[/COLOR]
That looks really familiar (see the first reply!);)

mwebb,
Did you try any of the solutions we proposed? Note what I said about using IF and all the TRUE/FALSE assignments - they are not needed and can unnecessarily complicate your code.
The AND or OR function return TRUE or FALSE inherently, as do any function where you have two statements separated by =, >, <, >=, or <= (so you are doing a comparison).
 
Upvote 0
Joe back up what Joe has already said....
CF works ONLY on a TRUE/FALSE (or 1/0) answer, so you just need to structure formulas that will return 1 of those. Anything extra is redundant.

=A1=B1 will return either a TRUE or FALSE
=IF(A1=B1,TRUE,FALSE) will do the same, but the IF statement is not needed
 
Upvote 0
D'oh! I was just trying to simplify his working version in post #6. :oops: I didn't realize you had already figured it out from the non-working version and explanation in post #1.

mwebb, I second Joe's thought that simpler is better. Your version has the advantage that you wrote it, and as a consequence you understand it. But the shorter AND version has your 3 conditions displayed very prominently making it easier to maintain. Whichever version you end up using, you now have at least 2 working versions, and hopefully learned a bit too.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top