What Is Truth?

Category: General Weirdness / VBA Weirdness | [Item URL]

True is true, and False is false, right?

True. Except when you use Excel, things can get a bit confusing. Take a look at the worksheet below. the Range A1:A3 contains Boolean values (TRUE or FALSE).

When you add these three cells, you can get any of three results, depending on the method you use.

Simple addition

The formula in cell A5 uses the addition operator. The sum of these three cells is 2. The conclusion: Excel treats TRUE as 1, and FALSE as 0.

The SUM function

But wait! The formula in cell A6 uses Excel's SUM function. In this case, the sum of these three cells is 0.

It is possible to "force" these logical values to be treated as values by the SUM function. It requires an array formula. Enter the formula below using Ctrl+Shift+Enter, and it will return 2.

=SUM(A1:A3*1)

Oddly, the SUM function does return the correct answer if the logical values are passed as literal arguments. The formula below returns 2:

=SUM(TRUE,TRUE,FALSE)

A VBA function

Although VBA is tightly integrated with Excel, sometimes it appears that the two applications don't understand each other. The formula in cell C7 uses a simple VBA function (listed below), and it returns -2!

Function VBASUM(rng)
    Dim cell As Range
    VBASUM = 0
    For Each cell In rng
        VBASUM = VBASUM + cell.Value
    Next cell
End Function

VBA considers True to be -1, and False to be 0.

Still more

Tom Schipper sent me a workbook that demonstrates even more logical weirdness. The figure below demonstrates. The formula being entered is:

=AND(X,Y,Z)

The names X and Z refer to empty cells (Excel considers them to be FALSE). The name Y refers to a cell that contains a 1 (Excel considers that to be TRUE). Even though two of the arguments for the AND function are FALSE, the formula evaluates to TRUE!