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:
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:
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:
…is the same thing as…
…which is the same as…
As per my comment on @jcbermu’s answer, with a small correction:
If any but not ALL of the arguments contain text
valuesin cell values or empty cells, such values are ignored. But if ALL arguments contain text valuesin cell values or empty cells, the function returns
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
That is, if cell
A1 has value “abc”, then
#VALUE!. See rows 9 through 13 in the image below.
But is gets stranger…
If any of the arguments is an error, then
ANDwill return the first error. Except, if any of the arguments is a string-literal, then the return value is