Page 169 - Excel Timesaving Techniques for Dummies

32_574272 ch28.qxd 10/1/04 10:49 PM Page 154
28 Trapping Those
Terrible Errors

Technique

ost of the error values returned by the formulas you put in your
Save Time By worksheets represent bona-fide calculation errors that you need
Mto take care of. (See Technique 29 for some ideas on how to trace
Trapping division by zero
errors before they occur and eliminate their source.) Other error values are unavoidable at differ-
ent stages in spreadsheet development (most notably when you lack cer-
Trapping all types of error tain input values) or in the bare-bones template stage.
values
One of the most common of the latter type of error values is the #DIV/0!
error value. This error crops up whenever you have a division formula
that attempts to divide by a cell that either contains the value 0 or is still
blank (the equivalent of 0 to Excel) because you haven’t had time to
input any value into the cell. Another is the #NA (not available) error
value. If it gets entered into a cell with the NA function, it spreads to all
the other formulas that refer to it either directly or indirectly, giving the
faulty impression that not much of anything is currently available in the
spreadsheet.

This technique covers ways you can construct formulas that stop these
kinds of errors dead in their tracks, preventing them from spreading else-
where in your worksheet. As part of this technique, I cover the use of the
granddaddy of all logical functions: the IF function. If you’re already famil-
iar with the working of this function, feel free to skip the next section and
dive right into the procedure for stopping those ugly division-by-zero
error values.

If I Were a Logical Function

Excel includes a bunch of logical functions that you can use. The hall-
mark of a logical function is that it returns only one of two answers when
calculated:

Logical TRUE (also given the value 1)
Logical FALSE (also given the value 0)