Page 169 - Excel Workbook for Dummies

17_798452 ch12.qxp 3/13/06 7:33 PM Page 152
152 Part II: Using Formulas and Functions
table (in this case, that number is 2 when the Tip Percentage is 15% or 3
when the Tip Percentage is 20%)
7. Click the Insert Function button on the Formula bar, select VLOOKUP in the
Lookup & Reference category, and select OK.
8. Use the Paste Name dialog box (Insert➪Name➪Paste) to paste the range name
Food_total in the Lookup_value argument text box and Tip_table in the
Table_array argument text box in the Function Arguments dialog box.
Now all you need is to construct a formula using the IF function in the Col_index_
num text box. The IF function inserts 2 as the column index number when the
cell named Tip_percent contains 15% as its entry; otherwise, it inserts 3. This is
how this function should appear in the Col_index_num argument text box:
IF(Tip_percent=15%,2,3)
9. Click the Col_index_num argument text box to put the insertion point inside it,
and then do the following:
• Type IF( — don’t forget the open parenthesis and make sure you don’t put
a space between it and IF.
• Open the Paste Name dialog box and then use it to insert the range name
Tip_percent immediately following the open parenthesis.
• Type =15%,2,3) — don’t forget the commas or the close parenthesis and
make sure that there are no spaces between any of the values.
The Formula result shown at the bottom of the Function Arguments dialog box
for the VLOOKUP function should now read $1.80 (the tip amount for a food total
of $9.33 at 20%). If this checks out, proceed to step 10.
10. Select OK in the Function Arguments dialog box to close it and to enter the
lookup formula in the Tip cell, G4.
11. Change the Tip Percentage from 20% to 15% by positioning the cell cursor in this
cell and then selecting 15% on its drop-down list.
Excel immediately decreases the tip amount in cell G4 from $1.80 to $1.35.
12. Increase the food total in cell G3 to 75.50.
Excel increases the tip amount in cell G4 from $1.35 to $11.25.
13. Type 17 in the Tip Percentage cell and then click the Enter button on the
Formula bar.
Excel beeps at you and displays an error dialog box with the message, “The
value you entered is not valid.”
14. Select the Cancel button and then open the Data Validation dialog box again
(Data➪Validation).
Data Validation enables you to enter an input message that the user sees when-
ever he selects the cell, as well as an error message that is displayed whenever
he tries to enter a value that is no longer allowed.
15. Click the Input Message tab and then, in the Input Message text box, type Click
this drop-down button and then select 15% or 20% on its drop-down menu.
16. Click the Error Alert tab and then, in the Error Message text box, type The entry
in this cell is limited to 15% or 20% only! Click Cancel and then select 15% or
20% on the cell’s drop-down list before you select OK.
A text box with your input message now appears next to the lower-right of
cell G2.