Page 170 - Excel Timesaving Techniques for Dummies

32_574272 ch28.qxd 10/1/04 10:49 PM Page 155
155
Trapping Division by Zero Errors
In other words, taking a page from Ebert and Roeper, production total is of the yearly total. And as long as
with a logical function it’s either thumbs up or any worksheet generated from this template lacks
thumbs down. any kind of production input, all the formulas in the
cell range B9:M9 will continue to display these lovely
The most important logical function, especially in #DIV/0! error values.
terms of trapping error values, is the basic IF func-
tion. The syntax of the IF function is as follows:

IF(logical_test,value_if_true,
value_if_false)

The logical_test argument sets up some kind of
equality (A=B), inequality (A<>B), or comparison
(A>B, A<=B, and so on) that either is the case (true)
or is not (false):
• Figure 28-1: Worksheet template riddled with #DIV/0!
The value_if_true argument tells Excel what to
errors.
calculate or input when the logical test is found
to be true.
You can easily eliminate these division-by-zero error
The value_if_false argument tells the program values in row 9 with the help of the IF function. All
how to proceed or what to input when the logical you have to do is have the IF function’s logical_test
test turns out to be false.
argument test the contents of the grand total cell N7:
For example, you could use the IF function to create
If this cell contains 0, the IF function’s
a formula that computes tax for a sale item only
value_if_true argument inputs 0 (rather than
when a taxable cell contains the word Yes: #DIV/0!) in the cell containing the IF formula.
=IF(A79=”Yes”,C79+C79*7/5%,C79) If the cell contains anything besides 0, the IF
function’s value_if_false argument calculates
In English, this IF construction says if cell A79 (the the original percentage formula and returns this
Taxable? column) contains the word Yes, Excel mul- result to the cell.
1
tiplies the extended price in cell C79 by 7 ⁄2% and
then adds this tax amount to the extended price. This kind of IF formula, entered into cell B9 to calcu-
Otherwise (that is, if cell A79 contains anything late the January percent of total, will look like this:
besides the label Yes), Excel just returns the original
=IF($N$7=0,0,=B9/$N$7)
extended price in cell C79 without adding any tax
to it.
Figure 28-2 shows you how the Production Schedule
template looks after entering this IF formula into cell
Trapping Division by Zero Errors B9 and then copying it across row 9 to the cell range
C9:M9. As you can see in this figure, the addition of
this little IF function to the formulas in row 9 cer-
The #DIV/0! error values are sometimes unavoidable
tainly did the trick. Because cell N7 currently con-
at different stages in your spreadsheet and can bene- tains 0, the value_if_true argument for all the IF
fit from being eliminated from the worksheet. Figure
functions is triggered, and therefore all the percent-
28-1 shows you such a situation. Here is a blank tem- of-total cells now contain 0 themselves.
plate that contains a row of formulas (B9 through
M9) that calculate what percentage each month’s