please answer 1-15

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 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 34:332, 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: 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 Y es 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 coeches if they are at leest 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 cosch: 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 retum 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 ellgible to take a CPR course at the club'' expense. Employees who can work as camp counselers are eligible for the course. In cell M3, 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 Counseior 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 fobs. in cell N2, enter the text Level es the column heading. 7. In cell N3, enter a formula using the IF function and structured references as follows to determine which employment level Tiffany Adams is quarfied 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 N4N3N, if necessary. 8. Beittany created a formula with the VLOOKUP function to look up an employee names by their Employee ID. She also wents 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. Britany wants to determine several totals and averages for active employees. In cell Q9, 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. New Perspectives Excel 2019 | Module 7: SAM Project lb 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 SwimTeams table. 13. In cell B16, use the sUMIF function and structured references to ditglay the total wins for teams in the Youth league. 14. Brittany wants to display the Melboume swim teams data by league and year. She asks you to create a PivotTable to better manipulate and filter the data. Switch to the Melboume 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 PlvotTable name to: MelbournePivotTable b. Add the League field and the Team Name field (in that order) to the flows 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 Greup. 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 o 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 PlvorTable 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. Fiter the table so that only teams in the Youth league are displayed. 19. Continue to filter the Pivotfable as follows: a. Create a sticer based on the Level field value. b. Resize the slicer so that it has a height of 2.3and a width of 1.51. c. Move the slicer so that its upper-left corner appears within cell F3 and its lowerright corner appears within cell GI4. d. Use the slicer to filter the PlvotTable so that only Level 4 teams groups are visible. (8) Update Available We've made some fixes and improvements. To complete the process, the app needs to testart. Updato fivailable We've made some tixes and improvements. To complete the procesis, the app needs to restar. = = M = en 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 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 34:332, 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: 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 Y es 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 coeches if they are at leest 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 cosch: 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 retum 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 ellgible to take a CPR course at the club'' expense. Employees who can work as camp counselers are eligible for the course. In cell M3, 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 Counseior 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 fobs. in cell N2, enter the text Level es the column heading. 7. In cell N3, enter a formula using the IF function and structured references as follows to determine which employment level Tiffany Adams is quarfied 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 N4N3N, if necessary. 8. Beittany created a formula with the VLOOKUP function to look up an employee names by their Employee ID. She also wents 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. Britany wants to determine several totals and averages for active employees. In cell Q9, 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. New Perspectives Excel 2019 | Module 7: SAM Project lb 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 SwimTeams table. 13. In cell B16, use the sUMIF function and structured references to ditglay the total wins for teams in the Youth league. 14. Brittany wants to display the Melboume swim teams data by league and year. She asks you to create a PivotTable to better manipulate and filter the data. Switch to the Melboume 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 PlvotTable name to: MelbournePivotTable b. Add the League field and the Team Name field (in that order) to the flows 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 Greup. 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 o 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 PlvorTable 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. Fiter the table so that only teams in the Youth league are displayed. 19. Continue to filter the Pivotfable as follows: a. Create a sticer based on the Level field value. b. Resize the slicer so that it has a height of 2.3and a width of 1.51. c. Move the slicer so that its upper-left corner appears within cell F3 and its lowerright corner appears within cell GI4. d. Use the slicer to filter the PlvotTable so that only Level 4 teams groups are visible. (8) Update Available We've made some fixes and improvements. To complete the process, the app needs to testart. Updato fivailable We've made some tixes and improvements. To complete the procesis, the app needs to restar. = = M = en