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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
DrTribos
C1 is also empty
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Perhaps b1 is ignored as per the other comment but c1 is not ignored because you change its state with the not (?
I think when you make empty cell b1 = empty cell b2 you change from nul / undefined to empty as in false.... hence the false...
ASKER
Okay, but how does that reconcile with evaluating the expressions (F9 them) inside the AND function, and getting a different answer?
Just gguessing that F9 would initialise so you'd get the expected outcome?
ASKER
Initialise what?
No amount of recalculation changes the full formula, including a complete rebuild of all dependencies.
No amount of recalculation changes the full formula, including a complete rebuild of all dependencies.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@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.
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.
ASKER
@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.
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.
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.
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.
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...
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...
ASKER
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.
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.