1.1 Create the VLOOKUP. *

The lookup value is the room code in cell B2, the table array is the sorted table Rates created above, the col_index is the column number that will return the standard rate. * Copy the formula down to the last reservation entry.

1.2 Crafting a Nested IF() to choose proper Rate_Code (Hint: There is a tutorial for creating nested IF functions in the Appendix) Since the VLOOKUP() only provided (or looked-up) the standard rate (table column 4) for all reservations you will need to add logic to the VLOOKUP() to handle reservations that have been made as advance or corporate. This can be done with a nested if that replaces the 4 in the VLOOKUP().

1.3 - Add the Nested IF() to the VLOOKUP() so it will choose the proper Rate_Code * Rewrite the Model above, replacing room rate code, with the proper cell reference for data in row 2. * Insert the Nested IF into the VLOOKUP(). * Copy the revised formula down to the last reservation. (Hint: Use the fill handle) * Format the room rate column to currency format two decimal places.

1.4 Compute the Number of days in the reservation from start date and end date In cell I1 put the Label: Number of Days if it does not already exist.

* In I2 insert a formula to calculate the total number of elapsed days between the start and end date. Format the column a number with no decimal places.

1.5 Create a Total Charge Column.

* In cell J1 type the text: Total Charge if it does not already exist.

* In cell J2 enter a formula that will calculate the rate * the number of days.

* Copy this formula for all reservations.

* Format column as currency with two decimal places.

1.6 - Create Column Summaries

* At the bottom of the table show the average length of time a client reserves a room (show 2 decimal places)

* At the bottom of the table show the average room rate (not revenue) as currency with 2 decimal places.

* At the bottom of the table show the average room rate with a Group Discount (not revenue) as currency with 2 decimal places.

* At the bottom of the table show the average room rate with a Additional Discount (not revenue) as currency with 2 decimal places

Megan Davis Convention Center Daily Meeting Room Rates BA Room code Meeting Room Barnstable room BE Bershire CH Charles River PE WA Wadman Advance Rate Standard Rate Corporate Rate $ 1,225.00 $ 1,470.00 $ 955.50 $ 468.50 $ 562.20 $ 365.43 $ 550.00 $ 660.00 $ 429.00 $ 975.50 $ 1,170.60 $ 760.89 $ 475.00 $ 570.00 $ 370.50 Percy Megan Davis Convention Center Daily Meeting Room Rates BA Room code Meeting Room Barnstable room BE Bershire CH Charles River PE WA Wadman Advance Rate Standard Rate Corporate Rate $ 1,225.00 $ 1,470.00 $ 955.50 $ 468.50 $ 562.20 $ 365.43 $ 550.00 $ 660.00 $ 429.00 $ 975.50 $ 1,170.60 $ 760.89 $ 475.00 $ 570.00 $ 370.50 Percy