Why doesn't a blank cell in excel evaluate to false?

Question

A blank cell evaluates to each of the following:

When then does OR(A1,A1) evaluate to an error, when A1 is equivalent to (or coerced to) both 0 and FALSE? Is this a known inconsistency, or what's the reason for this behavior in Excel?

Answer 1

Just answering the question based on my response in the comment section of the question. The case: OR(A1,A1) returns #VALUE! can be explained looking at OR documentation. Here is the OR syntax:

OR(logical1, [logical2], ...)

It expects a logical condition for each input argument that can evaluate to either TRUE or FALSE.

In the Remark section of the referred documentation indicates the OR behavior for empty cells:

If an array or reference argument contains text or empty cells, those values are ignored.

In our case A1 is an empty cell, therefore both input argument are ignored.

In another remark item, Microsoft states:

If the specified range contains no logical values, OR returns the #VALUE! error value.

which explains why for this case it returns #VALUE!, i.e. none of the input arguments are logical values. For example: OR(A1,A1,1=1) returns TRUE, because the first two input arguments are ignored, but the last one evaluates to TRUE. Since blank cells and texts are treated the same way, the behavior of OR(A1,A1) for the case of blanks is equivalent to OR("","").

If you want to return a valid value for this case, we can use one of the following options:

OR(ISBLANK(A1), ISBLANK(A1)) -> testing for blanks
OR(A1="", A1="") -> testing for nothing

Check this link: Using IF to check if a cell is blank. You can also use LEN function, i.e. OR(LEN(A1)=0, LEN(A1)=0) as it is suggested by Exceljet. Remember testing for nothing is different than testing for blanks. If A1 has the value ="" (or any formula that returns "") then ISBLANK(A1) function returns FALSE, but A1="" returns TRUE.

The case OR(0,0) is different, it returns FALSE because Excel treats 0 as FALSE, it does an implicit conversion, coercing the 0 value to FALSE.