Excel - Boolean Logic - Apparent Inconsistency

Hi All,

I am trying to understand an apparent inconsistency in the way that Excel (2010 but potentially other versions) evaluates boolean expressions.

Start with a blank worksheet, and enter in A1 (say):

=AND(B1,NOT(C1))

This gives TRUE which seems odd.

Next, if I evaluate B1 and NOT(C1) separately, I get 0 (Zero) and TRUE respectively.

If I then substitute those into the original formula I get:

=AND(0,TRUE)

which evaluates to FALSE.

Similarly, if I enter:

=AND(B2,C2)

and put B2 = B1 (which is empty)
and C2 = NOT(C1) (which is also empty)

it evaluates to FALSE which is consistent with the evaluation by parts above, but inconsistent with the original construct.


Please can some explain what I am missing here?

Thanks,

Alan.


PS:  I can get around this by using =AND(B1=TRUE,NOT(C1)) or something similar, but that's not what I am asking.
LVL 23
AlanConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saqib Husain, SyedEngineerCommented:
I think the AND function ignores b1 because it is empty.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DrTribosCommented:
C1 is also empty
0
DrTribosCommented:
And should return true if all conditions are true .
https://support.office.com/en-my/article/AND-function-5f19b2e8-e1df-4408-897a-ce285a19e9d9

But you invert a condition so expect to have a  true and a false.

Generally boolean state is false as a default.... so yes seems like a hint of weirdness
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

DrTribosCommented:
Perhaps b1 is ignored as per the other comment but c1 is not ignored because you change its state with the not (?
0
DrTribosCommented:
I think when you make empty cell b1 = empty cell b2 you change from nul / undefined to empty as in false.... hence the  false...
0
AlanConsultantAuthor Commented:
Okay, but how does that reconcile with evaluating the expressions (F9 them) inside the AND function, and getting a different answer?
0
DrTribosCommented:
Just gguessing that F9 would initialise so you'd get the expected outcome?
0
AlanConsultantAuthor Commented:
Initialise what?

No amount of recalculation changes the full formula, including a complete rebuild of all dependencies.
0
Rory ArchibaldCommented:
Per the help for AND:
If an array or reference argument contains text or empty cells, those values are ignored.
(emphasis added)

An empty cell is not the same as passing a literal 0 value.
0
DrTribosCommented:
I've been meaning to build this spreadsheet all day... so now I have  and it's kinda thought provoking, well was for me anyway.

So I worked my way down column A

A1: =AND(B1,NOT(C1))  <-- True

A7: =AND(B7, NOT(C7)) <-- False

The only difference between A1 & A7 is that B7 and C7 are not blank...
B7: =B8   <--  B8 is empty, returns a '0'
C7: =NOT(C8)  <-- C8 is empty, not C8 returns True

Difference between row 1 and 7 is that in row 1 'B1' is empty and therefore is ignored.  Although 'C1' is empty Not(C1) returns a TRUE therefore the expressions is effectively =And(ignored, True) = True

In row 7 there are no blanks, B7 equates to 0 which is not ignored and 0 evaluates to a boolean false.  Since only 1 false is required to return an overall false you will get an overall false...

I did not see any changes with F9... which expression in particular changed for you?
0
AlanConsultantAuthor Commented:
@DrTribos:

In the formula in A1, select just B1, press F9, and it will evaluate to 0 (zero), do the same for NOT(C1) and it will evaluate to TRUE.  Finally select the whole of the formula now showing in A1 = AND(0, TRUE), and evaluate that, and it gives FALSE (i.e. the exact opposite of what it evaluated to originally).

Do you get them same?

Alan.
0
AlanConsultantAuthor Commented:
@Rory Archibald:

Yes - good spot in the help (RTFM!)

Its still a bit odd, and the evaluation by parts is even more odd.

I've felt for at least 20 years that Excel needs a Null() function to return a value that is logically equal / to what you get if you reference an empty cell (and which would return IsBlank(Null()) = TRUE I guess).

As I said in my OP up above, you can always get around it, but you have to spot it first in order to go around, and that is not always easy, especially if you are picking up a workbook that someone else had created.

Alan.
0
DrTribosCommented:
Hi Alan... I will try that. I don't use XL and didn't know it was possible to evaluate on such a granular level.  I  had tried to evaluate by selecting B1 snd pressing F9.

Will look and let you know.  

However, that's what I meant by "initialize"... speculation that an empty cell is forced to return a value so perhaps becomes vbNullSomething instead of just empty or undefined.
0
DrTribosCommented:
Oh... I see... on my system (XL 2010) when I select the value in the expression (A1: =AND(B1,NOT(C1))) and press F9 that value is replaced by its actual value (i.e. becoming A1: =AND(0,NOT(C1)), which as you mentioned changes the value of the result from True to False.  However, it also changes the expression...

I would not have expected this either, but in the context of what Syed & Rory said And ignores blanks... pressing F9 changes the expression, there are no blanks of relevance.   B1 is changed to an actual value, zero.  In that context the result makes sense...

also, with this new perspective of F9 initialize was totally the wrong idea...
0
AlanConsultantAuthor Commented:
Hi All,

I am closing off and allocating points for the discussion - hope you are all okay with the allocations.

As I said in my OP, you can always build around this issue, and / or test for things much more explicitly, but it still seems like slightly odd behaviour, although no doubt it is 'logical' (for some given value of logic!)

Thank you all for your input.

Alan.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.