Microsoft Office Tutorials and References
In Depth Information
Formula Problems and Solutions
Logical value errors
As you know, you can enter TRUE or FALSE into a cell to represent logical True or logical False.
Although these values seem straightforward enough, Excel is inconsistent about how it treats
TRUE and FALSE.
Figure 21-6 shows a worksheet with three logical values in A1:A3 as well as three formulas that
sum these logical values in A5:A6. As you see, these formulas return three different answers!
Figure 21-6: This worksheet demonstrates an inconsistency when summing logical values.
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.
But wait! The formula in cell A6 uses Excel’s SUM function. In this case, the sum of these three
cells is 0. In other words, the SUM function ignores logical values. However, it’s possible to force
these logical values to be treated as values by the SUM function by using an array formula. Enter
the following formula using Ctrl+Shift+Enter, and it returns 2:
To add to the confusion, the SUM function does return the correct answer if the logical values are
passed as literal arguments. The following formula returns 2:
Although the VBA macro language is tightly integrated with Excel, sometimes it appears that the
two applications don’t understand each other. I created a simple VBA function that adds the
values in a range. The function (which follows), returns –2!
Dim cell As Range
VBASUM = 0
For Each cell In rng
VBASUM = VBASUM + cell.Value