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

344 Error-Trapping Formulas
Figure 2-1:
Blank 2012 Production Schedule spreadsheet that’s full
of #DIV/0! errors.
The first percent-of-total formula in cell B9 contains the following:
=B8/$K$8
Because cell K8 with the grand total contains 0, the formula returns the #DIV/0! error value. Now, I show you how to set a trap for the error in the logical_test argument inside an IF function. After the logical_test argument, you enter the value_if_true argument (which is 0 in this example) and the value_if_false argument (which is the B7/$K$7). With the addition of the IF function, the final formula looks like this:
=IF($K$8=0,0,B8/$K$8)
This formula then inputs 0 into cell B10, as shown in Figure 2-2, when the for- mula actually returns the #DIV/0! error value (because cell K8 is still empty or has a 0 in it), and the formula returns the percentage of total production when the formula doesn’t return the #DIV/0! error value (because cell K8 with the total production divisor is no longer empty or contains any other value besides 0). Next, all you have to do is copy this error-trapping formula in cell B10 over to J10 to remove all the #DIV/0! errors from this worksheet.
The error-trapping formula created with the IF function in cell B10 works fine as long as you know that the grand total in cell K8 will contain either 0 or some other numerical value. It does not, however, trap any of the various error values, such as #REF! and #NAME?, nor does it account for the special #NA (Not Available) value. If, for some reason, one of the formulas feeding into the SUM formula in K8 returns one of these beauties, they will suddenly cascade throughout all the cells with the percent-of-total formulas (cell range B10:J10).