Working with advanced functions in excel assignment

User Generated

ornhohpmxb

Business Finance

Description

Need somebody to log onto my canvas account and complete my excel assignment. Only 16 steps. Shouldn't take any longer than 30 minutes to an hour. It's due by 11:59 tonight!

User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

I have uploaded a copy of the file I completed (NP_EX16_8a_BeauBuczko_2.xlsx) and a copy of the grade report showing you received 100% on the assignment (NP_EX16_8a_BeauBuczko_Report_3.xlsx)

New Perspectives Excel 2016 Module 8: SAM Project 1a

Beau Buczko

SUBMISSION #3 | SCORE IS: 100 OUT OF 100

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.)
Create a Find Duplicates conditional formatting rule.
Update a value in a cell.
2. Delete the conditional formatting rule applied to the range D2:D31.
Delete a conditional formatting rule.
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).
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.
Create a formula using structured references.
Copy a formula using structured references to a range.
4. 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 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.
Create a formula using structured references.
Copy a formula using structured references to a range.
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).
Edit a conditional formatting rule.
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.
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.
Create a formula using structured references.
Copy a formula using structured references to a range.
7. 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.
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.

Create a formula using structured references.
Copy a formula using structured references to a range.
8. 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.
c. The function should return the text No if a staff member meets none or only one of those criteria.

Create a formula using structured references.
Copy a formula using structured references to a range.
9. 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 fun...


Anonymous
Just what I needed…Fantastic!

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Related Tags