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:

=SUM(A1:A3*1)

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:

=SUM(TRUE,TRUE,FALSE)

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!

Function VBASUM(rng)

Dim cell As Range

VBASUM = 0

For Each cell In rng

VBASUM = VBASUM + cell.Value

Next cell

End Function