Page 173 - Excel Workbook for Dummies

17_798452 ch12.qxp 3/13/06 7:33 PM Page 156
156 Part II: Using Formulas and Functions
The edited formula on the Formula bar should now read
=IF(D4=”Yes”,IF(C4>1500,C4*20%,0)
5. Click the I-beam mouse pointer to position the insertion point between the %
(percent sign) and the , (comma) immediately preceding 0 in this formula — be
sure not to press the → key.
6. Type , (comma) and then click cell C4 in the worksheet.
The edited formula on the Formula bar should now read

=IF(D4=”Yes”,IF(C4>1500,C4*20%,C4,0)
7. Type *15%) — that is an asterisk, 15% and a close parenthesis — to complete
the value_if_false argument for the nested IF function.
The final edited formula on the Formula bar should now read
=IF(D4=”Yes”,IF(C4>1500,C4*20%,C4*15%),0)
In essence, this edited form of the formula with nested IF function in the
value_if_true argument of the original IF function is saying
• Evaluate the contents of cell D4 and, if Excel finds that this cell contains
Yes as its entry, the program evaluates the contents of cell C4; otherwise, it
just enters 0 (zero) in the current cell.
• If Excel does end up evaluating the contents of cell C4, the program checks
to see if this cell contains a value greater than 1,500. If the cell does, it then
multiplies this value by 20 percent; otherwise, the program multiplies the
value in C4 by 15 percent.
8. Click the Enter button on the Formula bar and then copy this edited formula
down to the cell range E5:E9.
In this version of the Spring Sale table, the Hutch and Armoire are now dis-
counted 20 percent, the 36-inch round table and 72-inch dining table are both
discounted 15 percent, and the Side and Arm chairs still receive no discount at
all. Check your final results against those shown in the Solved12-3.xls workbook
file in your Chapter 12 folder. If everything checks out, proceed to step 9.
9. Save your changes to a new workbook named Solved12-3-mine.xls in your
Chapter 12 folder inside the My Practice Spreadsheets folder and then close
this file.

Constructing Error-Trapping Formulas

Sometimes, you know ahead of time that certain error values are unavoidable in a
worksheet as long as certain data entries are missing. The most common error value
that gets you into this kind of trouble is the #DIV/0! error value. This error value
appears not only when the divisor in a division formula is actually 0 (zero) but also
when the divisor refers to an empty cell (which carries the numerical equivalent) in
which you haven’t yet had an opportunity to make any data entry (as when generat-
ing a new workbook from an Excel template file).

Fortunately, you can use the IF function to suppress the appearance of such error
values in formulas. When you do this, you not only get the benefit of not having to
look at them (there is, after all, nothing subtle about them), but you also ensure
that they don’t spread to any other parts of the spreadsheet containing dependent