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

error value that gets you into this kind of trouble is our old friend, the #DIV/0! error value. Suppose, for example, that you’re creating a new sales workbook from your sales template, and one of the rows in this template contains formulas that calculate the percentage that each monthly total is of the quarterly total. To work correctly, the formulas must divide the value in the cell that contains the monthly total by the value in the cell that contains the quarterly total. When you start a new sales workbook from its template, the cells that contain the formulas for determining the quarterly totals contain zeros, and these zeros put #DIV/0! errors in the cells with formulas that cal- culate the monthly/quarterly percentages.
These particular #DIV/0! error values in the new workbook don’t really rep- resent mistakes as such because they automatically disappear as soon as you enter some of the monthly sales for each quarter (so that the calculated quarterly totals are no longer 0). The problem that you may have is convincing your non-spreadsheet-savvy co-workers (especially the boss) that, despite the presence of all these error values in your worksheet, the formulas are hunky-dory. All that your co-workers see is a worksheet riddled with error values, and these error values undermine your co-workers’ confidence in the correctness of your worksheet.
Well, I have the answer for just such “perception” problems. Rather than risk having your manager upset over the display of a few little #DIV/0! errors here and there, you can set up these formulas so that, whenever they’re tempted to return any type of error value (including #DIV/0!), they instead return zeros in their cells. Only when absolutely no danger exists of cooking up error values will Excel actually do the original calculations called for in the formulas.
This sleight of hand in an original formula not only effectively eliminates errors from the formula but also prevents their spread to any of its depen- dents. To create such a formula, you use the IF function, which operates one way when a certain condition exists and another when it doesn’t.
To see how you can use the IF function in a formula that sometimes gives you a #DIV/0! error, consider the sample worksheet shown in Figure 2-1. This figure shows a blank Production Schedule worksheet for storing the 2010 production figures arranged by month and part number. Because you haven’t yet had a chance to input any data into this table, the SUM formulas in the last row and column contain 0 values. Because cell K8 with the grand total currently also contains 0, all the percent-of-total formulas in the cell range B10:J10 contain #DIV/0! error values.
Error-Trapping Formulas 343
Book III Chapter 2
Logical Functions and Error Trapping