Microsoft Office Tutorials and References
In Depth Information
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 lit-
eral arguments. The following formula returns 2:
Although the VBA macro language is tightly integrated with Excel, sometimes it appears that the two applica-
tions 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
VBA considers TRUE to be –1, and FALSE to be 0.
The conclusion is that you need to be aware of Excel's inconsistencies and also be careful when summing a
range that contains logical values.
Circular reference errors
A circular reference is a formula that contains a reference to the cell that contains the formula. The reference
may be direct or indirect. For help tracking down a circular reference, see the following section.
Excel's Auditing Tools
Excel includes a number of tools that can help you track down formula errors. The following sections describe
the auditing tools built into Excel.
Identifying cells of a particular type
The Go to Special dialog box is a handy tool that enables you to locate cells of a particular type. Choose
Home ⇒ Editing ⇒ Find & Select ⇒ Go to Special; see Figure 22-7.