Homework answers / question archive / 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

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

MS Excel

Share With

---

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”.

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

1.        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.)

2.        Delete the conditional formatting rule applied to the range D2:D31.

3.        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 Service Years).

a.        Use a structured reference to the Service Years column ([Service Years]) as the lookup_value argument.

b.        Use an absolute reference to the range Q15:U16 as the table_array argument.

c.         Use 2 as the row_index_num argument.

d.        Use TRUE as the range_lookup argument, so that the formula will find the closest approximate value (as staff members do not receive a raise after every year of service).

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 Alison Simoneau completed the HR training. The formula should subtract the value shown in the HR Training Year column ([HR Training Year]) from 2017 and should automatically fill to the range G3:G31.

5.        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 (5^th column, 1^st row in the Theme Colors palette).

6.        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.

a.        The IF function should first determine if the staff member is a college graduate. Use a structured reference to the College Graduate column ([College Graduate]) and “Yes” as the logical test.

b.        If the staff member’s college graduate status is equal to Yes, the function should return the text Yes (using “Yes” for the value_if_true argument).

c.         If the staff member is not a college graduate the function should return the text No (using “No” for the value_if_false argument).

7.        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.

a.        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([Service Years]) and the College Graduatecolumns ([College Graduate]).

b.        If a staff member meets one or both of those criteria, the function should return the text Yes (using “Yes” for the value_if_true argument).

c.         If a staff member meets neither of those criteria, the function should return the text No (using “No” for the value_if_false argument).

8.        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.        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([Supervisor]) and the Academic Technology Training columns ([Academic Technology Training]).

b.        If a staff member meets both of those criteria, the function should return the text Yes (using “Yes” for the value_if_true argument).

c.         If a staff member meets none or only one of those criteria, the function should return the text No (using “No” for the value_if_false argument).

9.        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.

a.        If the value of the Academic Technology Training column is equal to “Yes”, the formula should return the text Completed (using “Completed” as the value_if_true argument). Remember to use a structured reference to the Academic Techology Trainingcolumn ([Academic Technology Training]).

b.        If the value of the Academic Technology Trainingcolumn is equal to No, the formula should determine if the value in the Service Yearscolumn ([Service Years]) is greater than 2.

c.         If the staff member’s Service Years value is greater than 2, the formula should return the text Yes (using “Yes” as the value_if_true argument of the nested IF function).

d.        If the staff member’s Service Years value is not greater than 2, the formula should return the text No (using “No” as the value_if_false argument of the nested IF function).

10.     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.)

11.     In cell N2, enter a formula using the IF function and a structured reference to determine if Alison Simoneau is eligible for tuition remission.

a.        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 Yearscolumn ([Service Years]).

b.        If the staff member’s Service Yearsis greater than 1, the function should return the text Eligible (using “Eligible” for the value_if_true argument).

c.         If the staff member’s Service Years is not greater than 1, the function should return the text Not Eligible (using “Not Eligible” for the value_if_false argument).

12.     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.)

13.     In cell Q4, enter a formula using the VLOOKUP function using the following parameters.

a.        Use cell Q2 as the lookup_value argument.

b.        Use Staff (which represents the table shown in A2:N31) as the table_array argument.

c.         Use 5 as the col_index_num argument.

d.        Use FALSE as the range_lookup argument, so that the function will only return an exact match to the Employee ID value shown in Q2.

14.     Finally, Luke wishes to determine how many staff members have Academic Technology training (and their average level of experience).

In cell Q9, enter a formula using the COUNTIF function to count the number of staff with Academic Technology training. Use the Academic Technology Training column (Staff[Academic Technology Training]) as the range in the formula and “Yes” as the formula criteria.

15.     In cell R9, enter a formula using the AVERAGEIF function to determine the average service years of staff members with Academic Technology training.

a.        Use the Academic Technology Training column (Staff[Academic Technology Training]) as the range in the formula.

b.        Use “Yes” as the formula criteria.

c.         Use the Service Years column (Staff[Service Years]) as the average_range argument.

16.     In cell R10, enter a formula using the AVERAGE function to determine the average Service years of all staff as shown in the Service Years column (Staff[Service Years]).

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