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:
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
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
It has the same behavior in combinations:
=AND("false",0) <---Results in FALSE
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:
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:
'Displays "True" if cell A1 on the active sheet has no contents.
MsgBox Range("A1").Value2 = vbEmpty
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:
You can see the same behavior by omitting arguments entirely:
...is the same thing as...
...which is the same as...