Brevard County Swim Clubs

SUMMARIZING YOUR DATA WITH PIVOTTABLES

GETTING STARTED

$$ Save the file NP$\_$EX$365\_2021\_7$b$\_$FirstLastName$\_1.$xlsx as NP$\_$EX$365\_2021\_7$b$\_$FirstLastName$\_2.$xlsx

o Edit the file name by changing $1$ to $2.$

o If you do not see the $.$xlsx file extension, do not type it$.$ The file extension will be added for you automatically.

$$ With the file NP$\_$EX$365\_2021\_7$b$\_$FirstLastName$\_2.$xlsx open, ensure that your first and last name is displayed in cell B$6$ of the Documentation worksheet.

o If cell B$6$ does not display your name, delete the file and download a new copy.

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 E$3,$ enter a formula using the XLOOKUP 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. Find the matching value in the range P$14$:U$14,$ and retrieve the value in the range P$15$:U$15,$ using absolute references to both ranges. Return nothing if the lookup value isn't found, and since hourly pay rate is tiered based on the number of years of experience, find the next smallest match.

b$.$ Fill the formula into the range E$4$:E$32,$ 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 J$3,$ enter a formula using the IF function and structured references 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 J$4$:J$32,$ 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 K$3$ enter a formula using the IF and OR functions and structured references 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 K$4$:K$32,$ 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 L$3,$ enter a formula using the IF and AND functions and structured references 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 L$4$:L$32,$ 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 M$3,$ enter a formula using a nested IF function and structured references 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 M$4$:M$32,$ 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 N$2,$ enter the text Level as the column heading.

$7.$ In cell N$3,$ enter a formula using the IF function and structured references 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 N$4$:N$32,$ 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 Q$4,$ nest the e