complete all 16 steps in the instruction file.
New Perspectives Excel 2016 | Module 8: SAM Project 1b New Perspectives Excel 2016 | Module 8: SAM Project 1b Academic Support Center Advanced Functions and Conditional Formatting GETTING STARTED Open the file NP_EX16_8b_FirstLastName_1.xlsx, available for download from the SAM website. Save the file as NP_EX16_8b_FirstLastName_2.xlsx by changing the “1” to a “2”. 0. 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_8b_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 Luke Alberto is the director of the Academic Support Center at Ocean Side College. He started planning the tutoring staffing assignments for the upcoming academic year in an Excel table. Luke 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: a. 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. b. Correct the duplicate values by updating the Employee ID for Terri Ortez to 2026 and the Employee ID for Veola Huie to 2030. (Hint: When this sub-step is completed, the conditional formatting rule should no longer highlight any values in the range.) Delete the conditional formatting rule applied to the range D2:D31. In cell E2, enter a formula using the HLOOKUP function and structured references to determine a staff member’s pay rate (which is based on the number of years of service). c. Use a structured reference to look up the value in the Service Years column. Retrieve the value in the 2nd row of the table in the range Q15:U16. Since staff members do not receive a raise after every year of service, find an approximate match. d. Fill the formula into the range E3:E31 if necessary. In cell G2, enter a formula without a function using structured references to determine the number of years since Alison Simoneau completed the HR training. The formula should subtract the value shown in the HR Training Year column from 2017 and should automatically fill to the range G3:G31. The HR Department updated their training two years ago. The conditional formatting rule Luke created in the range G2:G31 does not clearly identify those staff members who completed the HR training 2 or more years ago (and will be required to complete the new HR training). Edit the conditional formatting rule applied to the range G2:G31, so that the highlighted cells are formatted with the bold font style and the Turquoise, Accent 1 font color (5th column, 1st row in the Theme Colors palette). Luke now wants to determine how many members of his staff will be eligible to be subject leaders. In cell J2, enter a formula using the IF function and a structured reference to determine if Alison Simoneau can be a subject leader. e. The IF function should first determine if the staff member is a college graduate. Use a structured reference to the College Graduate column. f. The function should return the text Yes if the staff member’s college graduate status is equal to Yes. g. The function should return the text No if the staff member is not a college graduate. To be a Supervisor, the staff member must either have more than 5 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 Alison Simoneau can be a supervisor. h. The IF function should first determine if the staff member’s Service Years is greater than 5 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. i. The function should return the text Yes if a staff member meets one or both of those criteria. j. The function should return the text No if a staff member meets neither of those criteria. To be a Supervisor for online tutoring, a staff member must be a supervisor and have completed Academic Technology training. In cell L2, enter a formula using the IF and AND functions, as well as structured references to determine if Alison Simoneau can be an online tutor. k. The logical test in the IF function should determine if the staff member’s Supervisor status is “Yes” AND the staff member’s Academic Technology Training status is “Yes”. Remember to use a structured reference to the Supervisor and the Academic Technology Training columns. l. The function should return the text Yes if a staff member meets both of those criteria. m. The function should return the text No if a staff member meets none or only one of those criteria. Online students require tutors with Academic Technology training. Academic Technology training is offered to staff with more than 2 years of service at the academic support center. Luke wants to identify the staff members eligible for Academic Technology 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 Academic Technology training, and if not, whether that staff member is eligible for Academic Technology training. n. If the value of the Academic Technology Training column is equal to “Yes”, the formula should return the text Completed. Remember to use a structured reference to the Academic Technology Training column. o. If the value of the Academic Technology Training column is equal to No, the formula should determine if the value in the Service Years column is greater than 2. p. The formula should return the text Yes if the staff member’s Service Years value is greater than 2. q. The formula should return the text No if the staff member’s Service Years value is not greater than 2. Staff members with over 1 year of service are eligible for tuition remission at the college. Luke wishes to create a new calculated column in the table to identify staff members eligible for tuition remission. In cell N1, enter the text Tuition Remission as the calculated column heading. (Hint: If a filter arrow appears in cell N1, ignore it.) In cell N2, enter a formula using the IF function and a structured reference to determine if Alison Simoneau is eligible for tuition remission. r. The IF function should first determine if the staff member’s Service Years is greater than 1. Remember to use a structured reference to the Service Years column. s. The function should return the text Eligible if the staff member’s Service Years is greater than 1. t. The function should return the text Not Eligible if the staff member’s Service Years is not greater than 1. Luke wants a quick way to look up staff members by their Employee ID. In cell Q3, nest the existing VLOOKUP function in an IFERROR function. If the VLOOKUP function returns an error result, the text “Invalid Employee ID” should be displayed by the formula. (Hint: Test if this formula is working by changing the value in cell Q2 to 0, but remember to set the value of Q2 back to 2005 when the testing is complete.) In cell Q4, enter a formula using the VLOOKUP function to display a staff member’s pay rate. Look up the employee ID listed in cell Q2. Use a structured reference to retrieve the value in the 5th column of the Staff table. Since the formula is looking for a specific staff member’s data, find an exact match. Finally, Luke wishes to determine how many staff members have Academic Technology training, as well as their average level of experience. In cell Q9, enter a formula using the COUNTIF function and structured references to count the number of staff with Academic Technology training. In cell R9, enter a formula using the AVERAGEIF function and structured references to determine the average service years of staff members with Academic Technology training as shown in the Service Years column. 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 page. 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 2