New Perspectives Excel 2013 Tutorial 3: SAM Project 1b Matthew Pons 1

Subject:MS ExcelPrice:19.87 Bought3

Share With

New Perspectives Excel 2013 Tutorial 3: SAM Project 1b

Matthew Pons

1.  On the Room List worksheet, select cells A2:A10 and then use AutoFill to

apply the format only to the range B2:B10.

2. In cell B3 enter ah, in cell B4 enter bm, in cell B5 type lt, and then use

Flash Fill to automatically fill in the values for the range B6:B10.

3. On the Room Rental Log worksheet, merge and center the range A1:C1,

and then format the range using the Heading 1 cell style (1st style in the

Titles and Headings section of the Cell Styles panel).

4. Merge and center the range A2:C2, and then change the font to Verdana

and the font size to 12 pt.

5. Format the range A10:C10 using the Heading 2 cell style (2nd style from

the left in the Titles and Headings section of the Cell Styles panel) and

then center the range A10:C10.

6. In cell B5, create a formula using the VLOOKUP function to look up the

value of cell B4 (in the current worksheet) in the range A3:B10 on the

Room List worksheet and then return the value in the second column of

that range. Use FALSE as value of the Range_Lookup argument to specify

an exact match lookup.

7.  In cell B7, use the TODAY function to display the current date.

8.  Use AutoFill to fill the range A12:A25 based on cell A11.

9.  In cell C11, create a formula that uses the IF function to check if the

value in cell B11 is equal to 0.

a. If this condition is true, the current cell should be made empty (equal

to "").

b. If this condition is false, the current cell should display the result of

multiplying cell B11 (hours booked) by cell B8 (hourly fee). Use a relative

reference to cell B11 and an absolute reference to cell B8 so that the

formula can be copied to other cells.

10.  Copy the formula from cell C11 to all cells in the range C12:C25.

11.  In cell B26, create a formula that uses the SUM function to total cells

B11:B25.

12. In cell C26, enter a formula that uses the SUM function to total cells

C11:C25.

13. Format B11:B26 with the Number Number format, showing two decimal

places.

14. Format cell B8 with the Currency Number format, showing two decimal

places, and then format the range C11:C26 with the same format.

15. Format the range A26:C26 as bold and then add an outside border

around this range.

16. In cell B28, create a formula that uses the MAX function to display the

largest value from the range B11:B25.

17. In cell B29, create a formula that uses the MIN function to display the

smallest value from the range B11:B25.

18. In cell B30, create a formula that uses the AVERAGE function to display

the average of the values in the range B11:B25.

19. In cell B31, create a formula that multiplies the value in cell B30 by 7 (or

the number of days in a week).