Page 171 - Excel Workbook for Dummies

17_798452 ch12.qxp 3/13/06 7:33 PM Page 154
154 Part II: Using Formulas and Functions
In Exercise 12-2, you will construct the necessary formulas with IF functions for this
version of the Spring Sale worksheet — one set to determine whether or not the furni-
ture item should be discounted and another set to compute the discount amount only
when the item is eligible for the discount.

Try It

Exercise 12-2: Using the IF Function to Build Formulas that Perform Alternate
Calculations

Open the Exercise12-2.xls workbook file in your Chapter 12 folder in the My Practice
Spreadsheets folder on your hard disk or in the Excel Workbook folder on the work-
book CD-ROM. You will use this expanded version of the Spring Sale worksheet to
practice constructing the formulas with IF functions needed to determine whether a
furniture item is eligible for a discount. If it is, you create formulas to compute the
discount amount and sale price:

1. Position the cell cursor in cell D4 where you build the formula that determines
whether the 36-inch round table is to be discounted.
2. Click the Insert Function button on the Formula bar. Next, double-click IF in the
Select a Function list box after selecting Logical in Select a Category drop-down
list box to open the Function Arguments dialog box for the IF function.

The determinant for eligibility for a discount is whether the suggested retail
price is greater than or equal to $1,000. In terms of the Logical_text argument
text box, you need to create this type of comparative expression using cell C4,
as in
C4>=1000
3. Select cell C4 in the worksheet and then type >=1000 before pressing Tab.
4. Type “Yes” (and be sure to enclose Yes in the pair of double quotation marks) in
Value_if_true argument text box before you press Tab.
5. Type “No” (and be sure to enclose No in the pair of double quotation marks) in
Value_if_false argument text box.

The Formula result at the bottom of the Function Arguments dialog box should
be equal to Yes as the value currently entered into cell C4 is indeed greater than
1000. If this checks out, proceed to step 6.
6. Select OK to close the Function Arguments dialog box and enter the formula with
the IF function into cell D4.
7. Use the Fill handle to copy this formula in D4 down to the cell range D5:D9.
Now, you need to create a formula using the IF function that checks whether the
cell in the Discounted column contains Yes or No and then calculates the dis-
counted amount accordingly.
8. Position the cell pointer in cell E4.
9. Construct a formula in this cell using the IF function that multiplies the value in
the Retail Price column by 20% if the Discounted column contains Yes; other-
wise, the formula enters 0 (zero) into the cell.

The argument text boxes in the Function Arguments dialog box for the IF func-
tion you create for this formula should contain the following values:
• D4="Yes" in the Logical_text argument text box
• C4*20% in the Value_if_true argument text box
• 0 in the Value_if_false argument text box