Page 171 - Excel Timesaving Techniques for Dummies

32_574272 ch28.qxd 10/1/04 10:49 PM Page 156
156
Technique 28: Trapping Those Terrible Errors
its argument contains any type of error value, includ-
ing the special #N/A value. (Note that if you use
ISERR instead of ISERROR, the program checks for
all types of error values except for #N/A.)

• Figure 28-2: Worksheet template after eliminating the
#DIV/0 errors with the IF formula.

Trapping All Types • Figure 28-3: Worksheet template after #NA values have
of Error Values infected the percent-of-total formulas.

To add the ISERROR function, you insert it into the IF
The error-trapping formula created with the IF func-
function as the logical_test argument:
tion in cell B9 (see the preceding section) works fine
as long as you know that the grand total in cell N7
If N7 contains an error value or the #N/A value at
will contain either 0 or some other numerical value. the time the IF function is evaluated, you specify
It does not, however, trap any of the various error 0 as the value_if_true argument so that Excel
values, such as #REF! or #NAME?, or account for the inputs 0 in cell B9 rather than error value or
special #NA (Not Available) value. If, for some rea- #N/A.
son, one of the formulas feeding into the SUM for-
For the value_if_false argument, you specify
mula in N7 returns one of these beauties, they will
the original IF function that inputs 0 if the cell N7
suddenly cascade throughout all the cells with the
contains 0; otherwise, it performs the division
percent-of-total formulas (cell range B9:M9).
that computes what percentage the January pro-
Figure 28-3 illustrates this point. As a result of enter- duction figure is of the total production.
ing the NA function into cell B3, the #NA value has
This amended formula with the ISERROR as the
spread to grand-total cell N7 and, from there, to all
value_if_true argument and with the original IF
the cells with the percent-of-total formulas in the cell
function nested inside it as the value_if_false argu-
range B9:M9. Although the original IF formula stops
ment in cell B9 looks like this:
#DIV/0! error values dead in their tracks, it is power-
less against any other error value. =IF(ISERROR($N$7),0,IF($N$7=0,0,B7/$N$7))
To trap all other error values in the grand total cell
As soon as you copy this modified version of the
N7 and prevent them from spreading to the percent-
original formula into the cell range C9:M9, all the
of-total formulas, you need to add the ISERROR func-
cells with percent-of-total formulas will be pro-
tion to the basic IF formula. The ISERROR function
tected against those ugly error values, as shown in
returns the logical value TRUE if the cell specified as
Figure 28-4.