Unit 6 AS2: Mastering Excel Attached Fles: 06 Mastering Instructions.docx (449.198 KB) 06. Mastering Starting file.xlsx (19.029 KB) The purpose of this assignment is to exhibit the skills learned in this week's readings, lectures and assignment. You will create a summary of a large dataset utilizing the advance tools in Excel Follow the attached instructions Brevard County Swim Clubs SUMMARIZING YOUR DATA WITH PIVOTABLES . 08 GETTING STARTED Open the file U6 Mastering Excel Starting file.xlsx Save the file as U6 Mastering Excel AS2.xlsx 10 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. Sh needs your help completing the workbook and analyzing the data, Switch to the All Employees worksheet. In cell E3, enter a formula using the HLOOK function as follows to determine an employee's potential pay rate, which is based on their years of experience: Use a structured reference to look up the value in the Years of Experience colun Retrieve the value in row 2 of the table in the range P14:015, using an absolu 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 counse at one of the summer camps, an employee must be at least 21 years old. Brittany was to determine how many employees are eligible to be camp counselors. In cell 33, ente 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 true and No if false. a. C HI experience, find an approximate match. b. Fill the formula into the range E4:E32, Y 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 13, 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 14:132, 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 and structured references as follows to determine if Tiffany Adams can work with the swim team: 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: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 L3, 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, a. Paragraph Styles 7. In cell N3, enter a formula using the IF function and structured references as follows to determine which employment level Tiffany Adams is qualified for: 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 04, nest the existing VLOOKUP function in an IFERROR function. If the VLOOKUP function retums 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 09, enter a formula using the COUNTIF function and structured references to count the number of employees who can work as lifeguards. 10. In cell R9, enter a formula using the AVERAGEIF function and structured references to determine the average number of years of experience for lifeguards. 11. In cell R10, enter a formula using the AVERAGE function and structured references 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 and structured references to display the value in the first row and first column of the Swim Teams table. 13. In cell B16, use the SUMIF function and structured references to display the total wins for teams in the Youth league. Brittany wants to display the Melbourne swim teams data by league and year. She asks you to create a Pivot Table to better manipulate and filter the data. Switch to the a. C. 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: 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". 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 Pivot Table worksheet. Insert a PivotChart using the Stacked Column chart type and format it as follows: 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. Filter the PivotChart so that only 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 c. Unit 6 AS2: Mastering Excel Attached Fles: 06 Mastering Instructions.docx (449.198 KB) 06. Mastering Starting file.xlsx (19.029 KB) The purpose of this assignment is to exhibit the skills learned in this week's readings, lectures and assignment. You will create a summary of a large dataset utilizing the advance tools in Excel Follow the attached instructions Brevard County Swim Clubs SUMMARIZING YOUR DATA WITH PIVOTABLES . 08 GETTING STARTED Open the file U6 Mastering Excel Starting file.xlsx Save the file as U6 Mastering Excel AS2.xlsx 10 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. Sh needs your help completing the workbook and analyzing the data, Switch to the All Employees worksheet. In cell E3, enter a formula using the HLOOK function as follows to determine an employee's potential pay rate, which is based on their years of experience: Use a structured reference to look up the value in the Years of Experience colun Retrieve the value in row 2 of the table in the range P14:015, using an absolu 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 counse at one of the summer camps, an employee must be at least 21 years old. Brittany was to determine how many employees are eligible to be camp counselors. In cell 33, ente 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 true and No if false. a. C HI experience, find an approximate match. b. Fill the formula into the range E4:E32, Y 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 13, 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 14:132, 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 and structured references as follows to determine if Tiffany Adams can work with the swim team: 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: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 L3, 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, a. Paragraph Styles 7. In cell N3, enter a formula using the IF function and structured references as follows to determine which employment level Tiffany Adams is qualified for: 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 04, nest the existing VLOOKUP function in an IFERROR function. If the VLOOKUP function retums 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 09, enter a formula using the COUNTIF function and structured references to count the number of employees who can work as lifeguards. 10. In cell R9, enter a formula using the AVERAGEIF function and structured references to determine the average number of years of experience for lifeguards. 11. In cell R10, enter a formula using the AVERAGE function and structured references 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 and structured references to display the value in the first row and first column of the Swim Teams table. 13. In cell B16, use the SUMIF function and structured references to display the total wins for teams in the Youth league. Brittany wants to display the Melbourne swim teams data by league and year. She asks you to create a Pivot Table to better manipulate and filter the data. Switch to the a. C. 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: 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". 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 Pivot Table worksheet. Insert a PivotChart using the Stacked Column chart type and format it as follows: 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. Filter the PivotChart so that only 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 c