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:

=SUM(A1:A3*1)

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:

=SUM(TRUE,TRUE,FALSE)

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!

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.

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.