i just attached my word documents where you can find the instructions and excel documents. you would have to work on two excel documents this time .according to the instruction. its the same assignment but in two different excel files.
instructions.docx
shery.xlsx
barry.xlsx
Unformatted Attachment Preview
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”.
o
•
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.
o
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:
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 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).
4.
a.
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 base salary is tiered based on the number of years of
service, find an approximate match.
b.
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 Adam Moriarty received his last First Aid
New Perspectives Excel 2016 | Module 8: SAM Project 1a
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 (6th column, 1st 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.
7.
a.
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.
b.
The function should return the text Yes if the staff member’s age is
greater than or equal to 23.
c.
The function should return the text No if the staff member’s age is not
greater than or equal to 23.
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.
8.
a.
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.
b.
The function should return the text Yes if a staff member meets one or
both of those criteria.
c.
The function should return the text No if a staff member meets neither of
those criteria.
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.
a.
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.
b.
The function should return the text Yes if a staff member meets both of
those criteria.
New Perspectives Excel 2016 | Module 8: SAM Project 1a
c.
9.
The function should return the text No if a staff member meets none or
only one of those criteria.
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.
10.
a.
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.
b.
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.
c.
The formula should return the text Yes if the staff member’s Service
Years value is greater than 1.
d.
The formula should return the text No if the staff member’s Service Years
value is not greater than 1.
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.
12.
In cell N2, enter a formula using the IF function and a structured reference to
determine if Adam Moriarty is a Senior Staff member.
a.
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.
b.
The function should return the text Senior if the staff member’s Service
Years is greater than 3.
c.
The function should return the text Junior if the staff member’s Service
Years is not greater than 3.
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 4th column of the CBFStaff
table. Since the formula is looking for a specific staff member’s data, find an
exact match.
New Perspectives Excel 2016 | Module 8: SAM Project 1a
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
New Perspectives Excel 2016 | Module 8: SAM Project 1a
Final Figure 2: Staff Listing Worksheet – Columns O:U
Author:
Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from t
SAM website.
New Perspectives Excel 2016 | Module 8: SAM Project 1a
Camp Bright Firewood
WORKING WITH ADVANCED FUNCTIONS
SHEHAR YAR
edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the
SAM website.
Staff ID
1103
1053
1056
1035
1094
1066
1050
1054
1070
1040
1010
1073
1069
1060
1043
1034
1084
1078
1036
1074
1076
1009
1089
1022
1033
1065
1078
1048
1053
1064
Name
Adam Moriarty
Artie Jimenez
Bo Peterson
Claire Hunter
Eileen Randall
Flossie Frampton
Grace Richards
Hayley Jin
Hiro Marumi
Isaac Runyon
Isabella Thorne
James Gutierrez
Joey Stockton
Julio deSouza
Kira Weston
Liam Swanson
Maria Flatley
Melinda Masterson
Oscar Alberts
Philip Jones
Richard Tepper
Robert Miller
Sarah Winters
Sienna Shapiro
Stephanie Ito
Stephen McAllister
Sylvia Lee
Tanya Oldman
Tristan Reinholt
Walter Kaminsky
Age
Service Years
18
28
24
35
18
23
30
25
23
33
42
22
23
24
32
35
19
20
33
22
21
44
19
38
35
23
20
30
25
23
Base Salary
1
9
7
10
1
0
5
8
4
8
25
3
0
7
4
18
1
2
16
5
4
25
2
16
17
6
3
13
8
6
First Aid Certification Year
2017
2015
2013
2016
2013
2016
2017
2013
2013
2016
2017
2014
2015
2013
2015
2016
2017
2017
2015
2015
2014
2014
2015
2016
2016
2017
2013
2013
2014
2013
Years since First Aid
Certification
College
Graduate
No
Yes
Yes
No
No
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
No
No
No
No
Yes
No
No
Yes
No
Yes
Yes
No
No
Yes
Yes
Yes
Leadership Training
No
Yes
No
Yes
No
No
Yes
No
Yes
No
Yes
No
No
Yes
No
Yes
No
Yes
Yes
No
Yes
Yes
No
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Transportation
Group Leader
Bunk Leader
Candidate for Leadership
Training
Staff ID
Staff Name
Service Years
1036
Oscar Alberts
Total Staff
Leadership Trained Staff
All Staff
Service Years
Base Salary
Average Service Years
0
$
0
15.00 $
1
15.50
$
2
16.75 $
5
17.50 $
7
18.50
Author:
Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from t
SAM website.
New Perspectives Excel 2016 | Module 8: SAM Project 1a
Camp Bright Firewood
WORKING WITH ADVANCED FUNCTIONS
Barrington Walters
edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the
SAM website.
Staff ID
1103
1053
1056
1035
1094
1066
1050
1054
1070
1040
1010
1073
1069
1060
1043
1034
1084
1078
1036
1074
1076
1009
1089
1022
1033
1065
1078
1048
1053
1064
Name
Adam Moriarty
Artie Jimenez
Bo Peterson
Claire Hunter
Eileen Randall
Flossie Frampton
Grace Richards
Hayley Jin
Hiro Marumi
Isaac Runyon
Isabella Thorne
James Gutierrez
Joey Stockton
Julio deSouza
Kira Weston
Liam Swanson
Maria Flatley
Melinda Masterson
Oscar Alberts
Philip Jones
Richard Tepper
Robert Miller
Sarah Winters
Sienna Shapiro
Stephanie Ito
Stephen McAllister
Sylvia Lee
Tanya Oldman
Tristan Reinholt
Walter Kaminsky
Age
Service Years
18
28
24
35
18
23
30
25
23
33
42
22
23
24
32
35
19
20
33
22
21
44
19
38
35
23
20
30
25
23
Base Salary
1
9
7
10
1
0
5
8
4
8
25
3
0
7
4
18
1
2
16
5
4
25
2
16
17
6
3
13
8
6
First Aid Certification Year
2017
2015
2013
2016
2013
2016
2017
2013
2013
2016
2017
2014
2015
2013
2015
2016
2017
2017
2015
2015
2014
2014
2015
2016
2016
2017
2013
2013
2014
2013
Years since First Aid
Certification
College
Graduate
No
Yes
Yes
No
No
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
No
No
No
No
Yes
No
No
Yes
No
Yes
Yes
No
No
Yes
Yes
Yes
Leadership Training
No
Yes
No
Yes
No
No
Yes
No
Yes
No
Yes
No
No
Yes
No
Yes
No
Yes
Yes
No
Yes
Yes
No
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Transportation
Group Leader
Bunk Leader
Candidate for Leadership
Training
Staff ID
Staff Name
Service Years
1036
Oscar Alberts
Total Staff
Leadership Trained Staff
All Staff
Service Years
Base Salary
Average Service Years
0
$
0
15.00 $
1
15.50
$
2
16.75 $
5
17.50 $
7
18.50
…
Purchase answer to see full
attachment
