New Perspectives Excel 2019  Module 7: SAM Project 1b Brevard County Swim Clubs SUMMARIZING YOUR DATA WITH PIVOTTABLES GETTING STARTED ? Open the file NP_EX19_7b_ FirstLastName _1

Subject:MS ExcelPrice:19.87 Bought3

Share With

New Perspectives Excel 2019  Module 7: SAM Project 1b

Brevard County Swim Clubs

SUMMARIZING YOUR DATA WITH PIVOTTABLES

GETTING STARTED

?

Open the file

NP_EX19_7b_

FirstLastName

_1.xlsx

, available for download from the

SAM website.

?

Save the file as

NP_EX19_7b_

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_EX19_7b_

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.

Brittany Lambert is a volunteer for the Brevard County Swim Clubs in Melbourne, Florida,

and has offered to compile data on the swim club employees and teams. She needs your

help completing the workbook and analyzing the data.

Switch to the

All Employees

worksheet. In cell E3, enter a formula using the

HLOOKUP

function as follows to determine an employee's potential pay rate, which is based on

their years of experience:

a.

Use a structured reference to look up the value in the Years of Experience column.

Retrieve the value in row

2

of the table in the range

P14:U15

, using an absolute

reference. Because hourly pay rate is tiered based on the number of years of

experience, find an approximate match.

b.

Fill the formula into the range E4:E32, if necessary.

2.

Each summer, the swim clubs host a camp for young swimmers. To work as a counselor

at one of the summer camps, an employee must be at least 21 years old. Brittany wants

to determine how many employees are eligible to be camp counselors. In cell J3, enter a

formula using the

IF

function as follows to determine if Tiffany Adams can work as a

camp counselor:

a.

The function should use a reference to the Age column to determine if the

employee's age is

greater than or equal to 21

, and should return the text

Yes

if

true and

No

if false.

b.

Fill the formula into the range J4:J32, if necessary.

3.

To be eligible to work with the swim team, an employee must have at least two years of

experience or have completed CPR training. In cell K3 enter a formula using the

IF

and

OR

functions as follows to determine if Tiffany Adams can work with the swim team:

a.

The IF function should determine if the employee's Years of Experience is

greater

than or equal to 2

OR if the employee's CPR trained status is

"Yes"

, returning the

text

Yes

if an employee meets one or both of those criteria or the text

No

if an

employee meets neither of those criteria.

b.

Fill the formula into the range K4:K32, if necessary.

4.

Employees may serve as swim team coaches if they are at least age 23 and have at

least three years of experience. In cell L3, enter a formula using the

IF

and

AND

functions as follows to determine if Tiffany Adams is eligible to serve as a swim team

coach:

a.

The IF function should determine if the employee's age is

greater than or equal

to 23

AND the employee's years of experience are

greater than or equal to 3

,

and should return the text

Yes

if an employee meets both of those criteria or the

text

No

if an employee meets none or only one of those criteria.

b.

Fill the formula into the range L4:L32, if necessary.

5.

Brittany wants to identify employees who are eligible to take a CPR course at the clubs'

expense. Employees who can work as camp counselors are eligible for the course. In cell

M3, enter a formula using a nested

IF

function as follows to determine first if an

employee has already been trained in CPR, and if not, whether that employee meets the

qualifications to take the course:

a.

If the value in the CPR Trained column is equal to the text

"Yes"

, the formula

should display

Trained

as the text.

b.

Otherwise, the formula should determine if the value in the Camp Counselor

column is equal to the text

"Yes"

and return the text

Yes

if true and

No

if false.

c.

Fill the formula into the range M4:M32, if necessary.

6.

Employees who work at the swim clubs are also considered for employment at the

Brevard County Parks and Recreation Department. Employees with more than four years

of experience are qualified for more complex Level 2 jobs.

In cell N2, enter the text

Level

as the column heading.

7.

In cell N3, enter a formula using the

IF

function as follows to determine which

employment level Tiffany Adams is qualified for:

a.

The IF function should determine if the employee's Years of Experience is

greater

than or equal to 4

, and return the value

2

if true or the value

1

if false.

b.

Fill the formula into the range N4:N32, if necessary.

8.

Brittany created a formula with the VLOOKUP function to look up an employee names by

their Employee ID. She also wants to alert users when they enter an incorrect ID number.

In cell Q4, 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.

9.

Brittany wants to determine several totals and averages for active employees.

In cell Q9, enter a formula using the

COUNTIF

function to count the number of

employees who can work as lifeguards.

10.

In cell R9, enter a formula using the

AVERAGEIF

function to determine the average

number of years of experience for lifeguards.

11.

In cell R10, enter a formula using the

AVERAGE

function to determine the average

number of years of experience of all employees as shown in the Years of Experience

column.

12.

Switch to the

Melbourne Swim Teams

worksheet. In cell B14, use the

INDEX

function to

display the value in the first row and first column of the SwimTeams table.

13.

In cell B16, use the

SUMIF

function to display the total wins for teams in the

Youth

league.

14.

Brittany wants to display the Melbourne swim teams data by league and year. She asks

you to create a PivotTable to better manipulate and filter the data. Switch to the

Melbourne PivotTable

worksheet, and then create a PivotTable in cell A1 based on the

SwimTeams table. Update the PivotTable as follows so that it matches Final Figure 3:

a.

Change the PivotTable name to:

MelbournePivotTable

b.

Add the League field and the Team Name field (in that order) to the Rows area.

c.

Add the 2020, 2021, and 2022 fields (in that order) to the Values area.

d.

Change the display of subtotals to

Show all Subtotals at Bottom of Group

.

e.

Change the report layout to

Show in Outline Form

.

f.

Update the Sum of 2020 field in the Values area to display the name

2020 Wins

with the

Number

number format with

0

decimal places.

g.

Update the Sum of 2021 field in the Values area to display the name

2021 Wins

with the

Number

number format with

0

decimal places.

h.

Update the Sum of 2022 field in the Values area to display the name

2022 Wins

with the

Number

number format with

0

decimal places.

15.

Brittany wants to summarize data for all teams in a PivotTable. To do so, she must first

update the AllTeams table.

Switch to the

All Teams

worksheet then edit the record for the Brevard Blue Wave (row

26) to use

10

as the 2020 field value.

16.

Switch to the

All Teams PivotTable

worksheet. Refresh the PivotTable data, then verify

that the 2020 Wins value for the Brevard Blue Wave reflects the change you made in the

previous step.

17.

Apply the

Lavender, Pivot Style Medium 13

PivotTable style to the PivotTable.

18.

Add the League field to the Filters area of the Pivot Table. Filter the table so that only

teams in the Youth league are displayed.

19.

Continue to filter the PivotTable as follows:

a.

Create a Slicer based on the

Level

field value.

b.

Resize the slicer so that it has a height of

2.3"

and a width of

1.5"

.

c.

Move the slicer so that its upper-left corner appears within cell F3 and its lower-

right corner appears within cell G14.

d.

Use the slicer to filter the PivotTable so that only Level 4 teams groups are visible.

20.

Brittany also wants to summarize and compare the performance of the swim teams by

level. Switch to the

Swim Levels PivotTable

worksheet. Insert a PivotChart using the

Stacked Column

chart type and format it as follows:

a.

Resize and reposition the PivotChart so that the upper-left corner is located within

cell E2 and the lower-right corner is located within cell L22.

b.

Add the chart title

Wins by Level

to the PivotChart using the Above Chart option.

c.

Filter the PivotChart so that only Youth wins in Levels 4, 5, and 6 are displayed.

Your workbook should look like the Final Figures on the following pages. Save your changes, close

the workbook, and then exit Excel. Follow the directions on the SAM website to submit your

completed project.

Final Figure 1: All Employees Worksheet

Final Figure 2: Melbourne Swim Teams Worksheet

Final Figure 3: Melbourne PivotTable Worksheet

Final Figure 4: All Teams Worksheet

Final Figure 5: All Teams PivotTable Worksheet

Final Figure 6: Swim Levels PivotTable Worksheet