AND function gives TRUE result for empty, BLANK cell – why?

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.

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)

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!

Leave a Comment