Page 175 - Excel Workbook for Dummies

17_798452 ch12.qxp 3/13/06 7:33 PM Page 158
158 Part II: Using Formulas and Functions

1. Position the cell pointer in cell B9 that contains the original division formula.
You need to edit this formula by adding an IF function that inserts 0 (zero) rather
than the #DIV/0! error value in the cell if cell K7 is empty or contains 0 (zero) as
is currently the case.
2. Press F2 to place Excel in Edit mode and then press the ← key to position the
insertion point between = (equal to sign) and the B in B7.
3. Type IF( — open parenthesis — and then select cell K7.
The edited formula in the cell and on the Formula bar should now read:

=IF(K7B7/$K$7
4. Press F4 one time to convert the relative cell reference, K7, to the absolute refer-
ence, $K$7.
5. Type =0,0, (that is, the equal to sign followed by zero, a comma, and then
another zero and comma).
The edited formula in the cell and on the Formula bar should now read
=IF($K$7=0,0,B7/$K$7
6. Click the I-beam mouse pointer at the very end of the formula, after the 7 in the
final $K$7 cell reference to position the insertion point there and then type ) —
close parenthesis.
The final, edited formula in the cell and on the Formula bar should now read
=IF($K$7=0,0,B7/$K$7)
7. Click the Enter button on the Formula bar to complete this edit and enter the
edited formula with the IF function into cell B9.
In place of that ugly #DIV/0! error value in cell B9, the benevolent 0.00% should
now appear.
8. Copy the edited formula in cell B9 to the right to the cell range C9:J9.
Now all the #DIV/0! error values are gone from the Production Schedule work-
sheet, replaced by 0.00% entries.

9. Position the cell cursor in cell B3 and enter the #NA value using the NA function
in the Information function category.
The NA function is one of those few Excel functions that doesn’t require any
arguments; therefore, you can use this function to enter the #NA error value into
the current cell simply by entering =NA() and clicking the Enter button.
Note that the moment you enter the #NA value into B3, this error value spreads
to the subtotal cells, K3 and B7, and from there to the grand total in cell K7 and
from there to all the division formulas that use its value as their divisor.
To trap this in cell K7 and prevent its spread to the cell range B9:J9, you need to
edit the original formula in cell B9 by adding the ISERROR function and then
copy this version across the columns of this row.
10. Position the cell cursor in cell B9.
11. Edit the IF function in this cell’s formula by replacing the logical_test argument,
$K$7=0, with ISERROR($K$7).