Page 174 - Excel Workbook for Dummies
P. 174
17_798452 ch12.qxp 3/13/06 7:33 PM Page 157
Chapter 12: Using the Logical Functions 157
formulas. This means that in suppressing the display of error values in their original
formulas, you also end up trapping them in their original cells.
When using IF functions in the construction of error-trapping formulas, you often use
them in combination with some of the functions in the Information category, the most
versatile of which is the ISERROR function. This nifty little function evaluates the cell
reference you specify as its value argument and returns TRUE if the cell contains any
of those pesky error values #N/A, #VALUE!, #REF!, #REF!, #DIV/0!, #NUM!, #NAME?, and
#NULL) and FALSE if it contains any other kind of entry. You can use its little brother
function, ISERR, to test for all error values in a cell, excluding #NA, for Not Available,
which some users do not consider an error value per se.
Figure 12-3 shows you a situation where you need to construct an error-trapping for-
mula. Here, you see an empty version of the Production Schedule worksheet that is
on its way to being saved as an Excel template file. Before that can happen, however,
you would need to suppress all those #DIV/0! error values in the cell range B9:J9 until
you begin entering the production quota figures for the particular months. (The error
values appear because cell K7 with the grand total that it used as the divisor in their
formulas contains 0 (zero).) The way to do that, as you will see in Exercise 12-4, is by
constructing a formula that traps this error in the original formula and all its copies.
Figure 12-3:
Empty
Production
Schedule
worksheet
containing
#DIV/0!
error values
due to the 0
(zero) in the
grand total
in cell K7.
Try It
Exercise 12-4: Building Formulas that Trap Error Values
Open the Exercise12-4.xls workbook in your Chapter 12 folder in the My Practice
Spreadsheets folder on your hard disk or in the Excel Workbook folder on the work-
book CD-ROM. You will use this empty version of the Production Schedule worksheet
to practice constructing error-trapping formulas using the IF function:
