31

I have blank B2 cell.

=ISBLANK(B2) gives TRUE

A simple logical test on it returns FALSE

=IF(B2,TRUE,FALSE) gives FALSE

Yet when directly used in an AND statement, it returns TRUE

=AND(B2,TRUE) gives TRUE

Of course, when indirectly used in an AND statment, it still returns FALSE

=AND(IF(B2,TRUE,FALSE),TRUE) gives FALSE

Could you explain me please why my Excel behave in that way?

41

Extracted from here, look for the section titled Excel logical functions - facts and figures:

In Excel when you are using logical functions (AND, XOR, NOT, OR), if any of the arguments contains text values or empty cells, such values are ignored.

5
  • 2
    This is great to know!! – Gary's Student Aug 31 '16 at 12:15
  • 2
    Perhaps you could add the rule covering the expression IF(B2,TRUE,FALSE)? Also, I hoped that the logically correct (but a little counter-intuitive) conjunctions and disjunctions of resulting empty sets would be returned, i.e. AND(B2) would be true while OR(B2) was false, but experimentation shows that they return an error. – PJTraill Aug 31 '16 at 14:58
  • 3
    if any but not ALL of the arguments contains text values or empty cells, such values are ignored. But if ALL arguments contain text values or empty cells, the function returns #VALUE! – ThunderFrame Sep 1 '16 at 11:53
  • 1
    Good answer. @ThunderFrame's comment is paramount as well. – Raystafarian Sep 1 '16 at 19:47
  • There's one more caveat. There's a difference between text passed as a cell reference, as opposed to text passed as a string-literal. See my answer for details. – ThunderFrame Sep 3 '16 at 1:52
6

TL;DR - It is an artifact of how Excel stores and exchanges cell contents internally. An empty cell is a VARIANT with no value, which converts to FALSE due to how programming languages treat the truthiness of zero and non-zero values.

The behavior of AND() (and all of the other logical functions) is to convert both arguments into Booleans, then perform a logical and operation on them. You can peel back the covers and take a look at how it is defined in the Excel object model using Object Browser of the Visual Basic Editor:

AND in Object Browser

Note that it returns a Boolean, not a Variant. This means that at some point during the process of evaluating the function, all of the arguments have to be converted to Booleans. The actual observed behavior indicates that this is done early in the processing - Excel tries to be user friendly by attempting to convert all of the arguments and using the converted values in the calculation if it succeeds. This can be demonstrated by passing the String values "True" and "False" to the function:

=AND("true","true") <---Results in TRUE
=AND("false","true") <---Results in FALSE
Etc.

It also can be demonstrated with numerical arguments - it treats any non-zero value as true and zero as false:

=AND(42,-42) <---Results in TRUE
=AND(0,0) <---Results in FALSE
=AND(42,0) <---Results in FALSE
Etc.

It has the same behavior in combinations:

=AND("false",0) <---Results in FALSE
Etc.

By now you should get the picture. So how does this relate to testing a blank cell? The answer to this lies in how Excel stores the contents of a cell internally. Again, the Excel Object Model is enlightening:

Cell value in Object Browser

Note that it is a COM VARIANT structure. This structure basically contains 2 parts - a type, which tells the code using it how to interpret it, and a data area. A cell with no contents in Excel will have a "value" that is represented by a Variant with the sub-type VT_EMPTY. Again, this can be confirmed with a macro:

Sub DemoMacro()
    'Displays "True" if cell A1 on the active sheet has no contents.
    MsgBox Range("A1").Value2 = vbEmpty
End Sub

So what happens when the AND function converts that to a Boolean? Good question! It happens to be False, similar to how programming languages typically treat zero:

Sub DemoMacro2()
    MsgBox CBool(vbEmpty)
End Sub

You can see the same behavior by omitting arguments entirely:

=AND(,)

...is the same thing as...

=AND({vbEmpty},{vbEmpty})

...which is the same as...

=AND(0,0)

...which is:

=AND(FALSE,FALSE)
8
  • 1
    But the OP reports that an empty cell argument is treated as True, not (as you say) False. – nekomatic Sep 1 '16 at 15:34
  • @nekomatic - I missed that part. Edit coming. – Comintern Sep 1 '16 at 15:34
  • If A1 contains: TRUE, and B1 contains text preceded by an apostrophe: 'FALSE , then =AND(A1,B1) returns TRUE. mwa-ha-ha-ha. – ThunderFrame Sep 1 '16 at 20:15
  • Not the correct answer in this case, but a nice general explanation of function evaluation nonetheless. Treat this comment as a +1. – mtone Sep 3 '16 at 1:54
  • @ThunderFrame: What's your point? If A1 contains TRUE, and B1 contains any text value (including an empty string), then =AND(A1,B1) returns TRUE. – Scott Sep 7 '16 at 3:59
2

As per my comment on @jcbermu's answer, with a small correction:

If any but not ALL of the arguments contain text values in cell values or empty cells, such values are ignored. But if ALL arguments contain text values in cell values or empty cells, the function returns #VALUE!

With the correction implying:

If one or more of the arguments is text from a string-literal, as opposed to text in a cell reference, then the result is #VALUE!

That is, if cell A1 has value "abc", then =AND(A1,TRUE) returns TRUE, but =AND("abc",TRUE) returns #VALUE!. See rows 9 through 13 in the image below.

enter image description here

But is gets stranger...

If any of the arguments is an error, then AND will return the first error. Except, if any of the arguments is a string-literal, then the return value is #VALUE!

=AND(TRUE,TRUE,"abc") = #VALUE!

=AND(1/0,foo(),TRUE) = #DIV/0!

=AND(foo(),1/0,TRUE) = #NAME?

=AND(1/0,foo(),"abc",TRUE) = #VALUE!

=AND(foo(),1/0,"abc",TRUE) = #VALUE!

0
0

=ISBLANK(B2)
gives TRUE if B2 is empty not blank space

=AND(B2,TRUE) should be
=AND(B2="",TRUE) you have to write the value (B2 empty return True) and the 2nd logical is True and will return True
=IF(B2,TRUE,FALSE) should be =IF(B2="",TRUE,FALSE) will give True
=AND(IF(B2,TRUE,FALSE),TRUE) should be =AND(IF(B2="",TRUE,FALSE),TRUE) will give True
Try always to write all the test and don't think Excel will do.
Remember always the Logical Test, in your formulas not test for B2
How to use the IF function
Excel AND Function

2
  • 8
    I think you missed OPs point. The =ISBLANK(B2) is just to prove that cell B2 is blank. The =AND(B2,TRUE) is meant to test the boolean value of cell B2. Then only way this evaluates to TRUE is if cell B2 evaluates to TRUE. This is then contradicted by the following test, =IF(B2,TRUE,FALSE) returning FALSE. This is an awesome question as I did not know Excel ignores some cell values instead of evaluating them into a boolean value (TRUE or FALSE). – linhartr22 Aug 31 '16 at 15:55
  • 6
    Why is this upvoted? AND(B2,TRUE) definitely should not be AND(B2="",TRUE) in the context of the question. – Dmitry Grigoryev Sep 1 '16 at 8:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.