Page 363 - Excel 2013 All-in-One For Dummies

Error-Trapping Formulas 345
Figure 2-2:
2012 Production Schedule spreadsheet after trapping all the #DIV/0! errors.
To trap all error values in the grand total cell K8 and prevent them from spreading to the percent-to-total formulas, you need to add the ISERROR function to the basic IF formula. The ISERROR function returns the logical value TRUE if the cell specified as its argument contains any type of error value, including the special #N/A value (if you use ISERR instead of ISERROR, it checks for all types of error values except for #N/A).
To add the ISERROR function, place it in the IF function as the logical_test argument. If, indeed, K8 does contain an error value or the #N/A value at the time the IF function is evaluated, you specify 0 as the value_if_true argu- ment so that Excel inputs 0 in cell B10 rather than error value or #N/A. For the value_if_false argument, you specify the original IF function that inputs 0 if the cell K8 contains 0; otherwise, it performs the division that computes what percentage the January production figure is of the total production.
This amended formula with the ISERROR and two IF functions in cell B10 looks like this:
=IF(ISERROR($K$8),0,IF($K$8=0,0,B8/$K$8))
As soon as you copy this original formula to the cell range C10:J10, you’ve protected all the cells with the percent-of-total formulas from displaying and spreading any of those ugly error values.
Some people prefer to remove the display of zero values from any template that contains error-trapping formulas so that no one interprets the zeros as the correct value for the formula. To remove the display of zeros from a worksheet, deselect the Show a Zero in Cells That Have Zero Values check box in the Display Options for this Worksheet section of the Advanced tab
Book III Chapter 2
Logical Functions and Error Trapping