The Computer Oracle

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

--------------------------------------------------
Hire the world's top talent on demand or became one of them at Toptal: https://topt.al/25cXVn
and get $2,000 discount on your first invoice
--------------------------------------------------

Music by Eric Matyas
https://www.soundimage.org
Track title: Techno Bleepage Open

--

Chapters
00:00 And Function Gives True Result For Empty, Blank Cell - Why?
00:33 Accepted Answer Score 43
00:54 Answer 2 Score 0
01:41 Answer 3 Score 6
04:09 Answer 4 Score 2
04:59 Thank you

--

Full question
https://superuser.com/questions/1119505/...

--

Content licensed under CC BY-SA
https://meta.stackexchange.com/help/lice...

--

Tags
#microsoftexcel #worksheetfunction #microsoftexcel2013

#avk47



ACCEPTED ANSWER

Score 43


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.




ANSWER 2

Score 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)



ANSWER 3

Score 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!




ANSWER 4

Score 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