New Perspectives Excel 2016 | Module 8: SAM Project 1a Camp Bright Firewood WORKING WITH ADVANCED FUNCTIONS GETTING STARTED Open the file NP_EX16_8a_FirstLastName_1

Subject:MS ExcelPrice:19.99 Bought3

Share With

New Perspectives Excel 2016 | Module 8: SAM Project 1a

Camp Bright Firewood

WORKING WITH ADVANCED FUNCTIONS

GETTING STARTED

• Open the file NP_EX16_8a_FirstLastName_1.xlsx, available for download from the SAM website.
• Save the file as NP_EX16_8a_FirstLastName_2.xlsx by changing the “1” to a “2”.
  • 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.
• With the file NP_EX16_8a_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
  • If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

PROJECT STEPS

1. Dean Hilson is the director of Camp Bright Firewood. He started planning the camp staffing assignments for next year in an Excel table, and needs your help completing the table. First, he’d like to confirm that all the staff data was entered correctly into the worksheet.

Go to the Staff Listing worksheet and complete the following steps:

1. 1. Apply a conditional formatting Highlight Cells Rule to the range A2:A31 that formats any duplicate values with Light Red Fill with Dark Red Text.
   2. Correct the duplicate values by updating the Staff ID for Artie Jimenez to 1055 and the Staff ID for Sylvia Lee to 1087. (Hint: When this sub-step is completed, the conditional formatting rule should no longer highlight any values in the range.)
2. Delete the conditional formatting rule applied to the range D2:D31.
3. In cell E2, enter a formula using the HLOOKUP function to determine a staff member’s base salary (which is based on the number of years of service).
   1. Use a structured reference to look up the value in the Service Years column. Retrieve the value in the 2^nd row of the table in the range Q15:U16. Since base salary is tiered based on the number of years of service, find an approximate match.
   2. Fill the formula into the range E3:E31, if necessary.
4. In cell G2, enter a formula without a function using structured references to determine the number of years since Adam Moriarty received his last First Aid Certification. The formula should subtract the value shown in the First Aid Certification Year column from 2018 and should automatically fill to the range G3:G31.
5. The conditional formatting rule Dean created in the range G2:G31 highlights staff members who were last First Aid certified 3 or more years ago (and will be required to retake the First Aid Certification class), but it doesn’t stand out enough.

Edit the conditional formatting rule applied to the range G2:G31, so that the highlighted cells are formatted using the bold font style and the Orange, Accent 2 font color (6^th column, 1^st row in the Theme Colors palette).

6. According to camp policy, staff members need to be at least 23 years old to transport students. Dean now wants to determine how many members of his staff will be available to drive.

In cell J2, enter a formula using the IF function and a structured reference to determine if Adam Moriarty can be a Transportation team member.

6. 1. The IF function should first determine if the staff member’s age is greater than or equal to 23. Use a structured reference to the Age column.
   2. The function should return the text Yes if the staff member’s age is greater than or equal to 23.
   3. The function should return the text No if the staff member’s age is not greater than or equal to 23.
7. To be a Group Leader, the staff member must either have more than 3 service years or be a college graduate.

In cell K2 enter a formula using the IF and OR functions, as well as structured references, to determine if Adam Moriarty can be a group leader.

7. 1. The IF function should first determine if the staff member’s Service Years is greater than 3 OR if the staff member’s college graduate status is “Yes”. Remember to use a structured reference to the Service Years and the College Graduate columns.
   2. The function should return the text Yes if a staff member meets one or both of those criteria.
   3. The function should return the text No if a staff member meets neither of those criteria.
8. To be a Bunk Leader, a staff member must have over 4 service years and have completed leadership training.

In cell L2, enter a formula using the IF and AND functions, as well as structured references to determine if Adam Moriarty can be a bunk leader.

8. 1. The logical test in the IF function should determine if the staff member’s Service Years is greater than 4 AND the staff member’s Leadership Training status is “Yes”. Remember to use a structured reference to the Service Years and the Leadership Training columns.
   2. The function should return the text Yes if a staff member meets both of those criteria.
   3. The function should return the text No if a staff member meets none or only one of those criteria.
9. Many of the special staff teams require leadership training, which is offered to staff with more than 1 year of service at Camp Bright Firewood. Dean wants to identify the staff members eligible for leadership training in the table.

In cell M2, enter a formula using a nested IF function and structured references to determine first if a staff member already has completed leadership training, and if not, whether that staff member is eligible for leadership training.

9. 1. If the value of the Leadership Training column is equal to the text “Yes”, the formula should return the text Completed. Remember to use a structured reference to the Leadership Training column.
   2. If the value of the Leadership Training column is not equal to yes, the formula should determine if the value in the Service Years column is greater than 1.
   3. The formula should return the text Yes if the staff member’s Service Years value is greater than 1.
   4. The formula should return the text No if the staff member’s Service Years value is not greater than 1.
10. Staff members with over 3 years of experience are considered Senior Staff members and receive first choice of staffing assignments. Dean needs a new calculated column in the table to identify senior staff members.

In cell N1, enter the text Staff Level as the calculated column heading. (Hint: If a filter arrow appears in cell N1, ignore it.)

11. In cell N2, enter a formula using the IF function and a structured reference to determine if Adam Moriarty is a Senior Staff member.
    1. The IF function should first determine if the staff member’s Service Years is greater than 3. Remember to use a structured reference to the Service Years column.
    2. The function should return the text Senior if the staff member’s Service Years is greater than 3.
    3. The function should return the text Junior if the staff member’s Service Years is not greater than 3.
12. Dean wants a quick way to look up staff members by their Staff ID.

In cell Q3, nest the existing VLOOKUP function in an IFERROR function. If the VLOOKUP function returns an error result, the text “Invalid Staff ID” should be displayed by the formula. (Hint: You can test that this formula is working by changing the value in cell Q2 to 0, but remember to set the value of cell Q2 back to 1036 when the testing is complete.)

13. In cell Q4, enter a formula using the VLOOKUP function to display a staff member’s years of service. Look up the staff ID listed in cell Q2. Use a structured reference to retrieve the value in the 4^th column of the CBFStaff table. Since the formula is looking for a specific staff member’s data, find an exact match.
14. Dean wishes to determine several totals and averages for his staff.

In cell Q9, enter a formula using the COUNTIF function and structured references to count the number of staff members who have participated in Leadership Training.

15. In cell R9, enter a formula using the AVERAGEIF function and structured references to determine the average the number of service years of staff members who have participated in Leadership Training as shown in the Service Years column.
16. In cell R10, enter a formula using the AVERAGE function and a structured reference to determine the average number of years of service of all staff as shown in the Service Years column.

Your workbook should look like the Final Figures on the following pages. Save your changes, close the document, and then exit Excel. Follow the directions on the SAM website to submit your completed project.

Final Figure 1: Staff Listing Worksheet – Columns A:N

Final Figure 2: Staff Listing Worksheet – Columns O:U

Instructions_NP_EX16_8a.docx