New Perspectives Excel 2016 | M Module 8: SAM Project 1b references to determ column f The enter a formula without a function using structurth HR vear ethe number of years since Alison Simoneau complet The formula should subtract the value shown in the rom 2017 and should automatically fill to the range HR Training G3:G31 HR Department updated ng two years ago. The conditional two forma their traini tti ng rule Luk e created in the range G2:G31 does not clearly those staff members who completed the HR training 2 or more will be required to complete the new HR training). G2:G31, So Edit the conditional formatting rule applied to the range highlighted cells are formatted with the bold font style and the Accent 1 font color (5th column, 1" row in the Theme Colors palette. eColod the rusotha the Luke now wants to determine how be subject leaders. many members of his staff will be eligible to In cell J2, enter a formula using the IF function and a structured refere determine if Alison Simoneau can be a subject leader The IF function should first determine if the staff member is a college graduate. Use a structured reference to the College Graduate colunim a. The function should return the text Ves if the staff member's college graduate status is equal to Yes. b. The function should return the text No if the staff member is not a college graduate c. To be a Supervisor, the staff member must either have more than 5 servic years or be a college graduate. 7. 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. 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 a. "Yes". Remember to use a structured reference to the Service Years and the College Graduate columns. The function should return the text Yes if a staff member meets one or b. both of those criteria. The function should return the text No if a staff member meets neither of those criteria. c. 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 onlin The logical test in the IF function should determine if the staft Supervisor status is "Yes" AND the staff mem Training status is "Yoof e tutor New Perspectives Excel 2016 | M Module 8: SAM Project 1b references to determ column f The enter a formula without a function using structurth HR vear ethe number of years since Alison Simoneau complet The formula should subtract the value shown in the rom 2017 and should automatically fill to the range HR Training G3:G31 HR Department updated ng two years ago. The conditional two forma their traini tti ng rule Luk e created in the range G2:G31 does not clearly those staff members who completed the HR training 2 or more will be required to complete the new HR training). G2:G31, So Edit the conditional formatting rule applied to the range highlighted cells are formatted with the bold font style and the Accent 1 font color (5th column, 1" row in the Theme Colors palette. eColod the rusotha the Luke now wants to determine how be subject leaders. many members of his staff will be eligible to In cell J2, enter a formula using the IF function and a structured refere determine if Alison Simoneau can be a subject leader The IF function should first determine if the staff member is a college graduate. Use a structured reference to the College Graduate colunim a. The function should return the text Ves if the staff member's college graduate status is equal to Yes. b. The function should return the text No if the staff member is not a college graduate c. To be a Supervisor, the staff member must either have more than 5 servic years or be a college graduate. 7. 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. 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 a. "Yes". Remember to use a structured reference to the Service Years and the College Graduate columns. The function should return the text Yes if a staff member meets one or b. both of those criteria. The function should return the text No if a staff member meets neither of those criteria. c. 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 onlin The logical test in the IF function should determine if the staft Supervisor status is "Yes" AND the staff mem Training status is "Yoof e tutor