Module 8 Sam project 1 b . I need help with 4, 5, 7

New Perspectives Excel 2016 T Module 8:SAM Pr 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. 4. 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). 5. 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. 6. In cell 32, enter a formula using the IF function and a structured reference to 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 column. a. The function should return the text Yes 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 service 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 "Yes". Remember to use a structured reference to the Service Years and the College Graduate columns. a. The function should return the text Yes if a staff member meets one or both of those criteria. b. 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 online tutor. a. 8. 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. b. The function should return the text Yes if a staff member meets both of those criteria. New Perspectives Excel 2016 T Module 8:SAM Pr 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. 4. 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). 5. 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. 6. In cell 32, enter a formula using the IF function and a structured reference to 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 column. a. The function should return the text Yes 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 service 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 "Yes". Remember to use a structured reference to the Service Years and the College Graduate columns. a. The function should return the text Yes if a staff member meets one or both of those criteria. b. 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 online tutor. a. 8. 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. b. The function should return the text Yes if a staff member meets both of those criteria