**
Fill This Form To Receive Instant Help**

Homework answers / question archive / New Perspectives Excel 2019 | Excel Module 3 SAM End of Module Project 1 Barrel Barnard Consulting Perform Calculations with Formulas and Functions GETTING STARTED Open the file NP_EX19_EOM3-1_FirstLastName_1

Barrel Barnard Consulting

Perform Calculations with Formulas and Functions

- GETTING STARTED

- Open the file
**NP_EX19_EOM3-1_**, available for download from the SAM website.*FirstLastName*_1.xlsx - Save the file as
**NP_EX19_EOM3-1_**by changing the “1” to a “2”.*FirstLastName*_2.xlsx- If you do not see the
**.xlsx**file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.

- If you do not see the
- With the file
**NP_EX19_EOM3-1_**still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.*FirstLastName*_2.xlsx- If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

**PROJECT STEPS**

- Chanelle Larson is a professional assistant with Barrel Barnard Consulting. Chanelle is responsible for collecting consultant hours for some of Barrel Barnard's projects and processing the payments. She wants to automate some tasks and calculate summary information automatically on the worksheet that consultants use to track their hours.

Switch to the*Timesheet*worksheet. In cell C5, insert the**NOW**function to record the current date. - Each worksheet covers a period of 20 work days. In cell C4, create a formula using the
**WORKDAY**function to calculate the date**19**days from the start date (cell**C3**). - Use AutoFill to complete the labels in the table of hours. Use the values in the range A9:A10 to extend the week numbering to the range A11:A12. Use the value in cell B8 to autofill the remaining weekday abbreviations in the range C8:F8.
- Add formulas to complete the table of hours used. In cell B17, create a nested formula with the
**IF**and**SUM**functions that check if the total number of hours worked in week 1 (cells**B9:F9**) is equal to**0**. If it is, the cell should display nothing (indicated with two quote marks: ""). Otherwise, the cell should display the total number of hours worked in week 1. Copy the formula from cell B17 to fill the range B18:B20. - In cell H2, create a formula using the
**WORKDAY**function to calculate the date**5**days after the period end date (cell**C4**). - Create lookup functions to complete the summary section. In cell I6, create a formula using the
**VLOOKUP**function to display the number of hours worked in the selected week. Look up the week number in cell**I5**in the range**A17:G20**, and return the value in the**2**nd column. Use absolute references for cell I5 and the range A17:G20. - Copy the formula from cell I6 to the range I7:I11 and then edit the formula in cell I7 to return the value in the
**3**rd column, the formula in cell I8 to return the value in the**4**th column, the formula in cell I9 to return the value in the**5**th column, the formula in cell I10 to return the value in the**6**th column, and the formula in cell I11 to return the value in the**7**th column. - Add formulas to complete the balances section. In cell K8, create a formula using the
**SUM**function that calculates the total of the range**D17:D20**and subtracts it from the value in cell**J8**. - In cell K9, create a formula using the
**SUM**function that calculates the total of the range**E17:E20**and subtracts it from the value in cell**J9**. - In cell K10, create a formula using the
**SUM**function that calculates the total of the range**F17:F20**, subtracts it from the value in cell**J10**, adds the total of the range**G17:G20**, and subtracts**160**. - Copy the formula in cell J11 to cell K11.
- In cell J16, create a formula using the
**AVERAGE**function that calculates the average of the range**B9:F12**. - In cell J17, create a formula using the
**MAX**function that returns the maximum value from the range**B9:F12**. - In cell J18, create a formula using the
**MIN**function that returns the minimum value from the range**B9:F12**.

Your workbook should look like the Final Figures on the following pages. The value in cell C5 has been intentionally blurred as it will never be constant. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.

** Final Figure 1: Timesheet Worksheet**

Already member? Sign In