Page 172 - Excel Workbook for Dummies

17_798452 ch12.qxp 3/13/06 7:33 PM Page 155
Chapter 12: Using the Logical Functions 155

10. Use the Fill handle to copy this formula in cell E4 down to the cell range E5:E9.
In the final table, all the furniture items except for the Side chair and Arm chair
should be discounted and have discounted amounts computed in column E.
Check your final results against those shown in the Solved12-2.xls workbook file
in your Chapter 12 folder. If everything checks out, proceed to step 11.
11. Save your changes to a new workbook named Solved12-2-mine.xls in your
Chapter 12 folder inside the My Practice Spreadsheets folder and leave this file
open for Exercise 12-3.

Nesting IF functions

IF functions in decision-making formulas are great when you’re dealing with situations
that only require two alternatives — one that comes into play when a certain condi-
tion exists and the other that comes into play when it does not. But what about a situ-
ation where you have more than two alternatives?

For example, in the Spring Sale worksheet you used in the previous IF function exer-
cise, suppose you still only want to discount furniture that retails over $1,000 but
want to use two different discount amounts as well: 15% for suggested retail prices
that are lower than $1,500 and 20% for suggested retail prices that are above $1,500.

To accommodate such a case, you would have to nest a second IF function within
the original one, making the second nested IF function either the value_if_true or
value_if_false argument of the original. Specifically, in for the Spring Sale worksheet
example, you would nest the second IF function as the value_if_true argument of the
original IF function. The first IF condition then tests if the furniture item is to be dis-
counted (indicated by a Yes in the Discounted column, meaning that the item’s retail is
above $1,000). If this item is found to be eligible for a discount, the second value_if_true
argument IF function determines whether it receives a 20- or 15-percent discount.

Try It

Exercise 12-3: Building Formulas with Nested IF Functions

Use the Solved12-2-mine.xls workbook file you created in Exercise12-2 (if you don’t
have access to this file, open the Solved12-2.xls workbook in your Chapter 12 folder in
the My Practice Spreadsheets folder on your hard disk or in the Excel Workbook
folder on the workbook CD-ROM). You will use this version of the Spring Sale work-
sheet with the completed formulas in the Discounted and Discount Amounts columns
to practice using one IF function as an argument of another:
1. Position the cell cursor in cell E4 that contains the formula with the IF function
that currently computes a 20% discount when the Discounted column contains
Yes.
2. On the Formula bar, click the insertion point in immediately in front of the C in
C4 in the value_if_true argument of the IF function in this cell.
3. Type IF( — open parenthesis — and then click cell C4 in the worksheet.
The edited formula on the Formula bar should now read:

=IF(D4=”Yes”,IF(C4C4*20%,0)
4. Type >1500, — a greater than sign, 1500, and a comma — to complete the
value_if_true argument for the new nested IF function.