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. 2. 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. 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 33, enter a formula using the IF function and structured references as follows to determine if Tiffany Adams can work as a camp counselor: a. b. 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. Fill the formula into the range 34:332, if necessary. File Default H D10 Home Insert Draw Page Layout Formulas. Keep B 2 Name 3 Tiffany Adams 4 Hassan Bachim 5 Megan Beckman 6 Catherine Coleman 7 Carrie Faulkner 8 Paul Gomez Exit New Options 13 Jeff Newman 14 Crystal Olneck 15 Yolanda Padilla 9 Bryant Grossburg 0 Marilyn Jesberger 11 Jonathan Lampert 12 Gina Milota 19 Ryan Quinlan 20 Andrea Shelby 21 Anna Tran 16 Michael Pienkowski 17 Keith Pienkowski 18 James Quinlan Sheet View 22 Tracy Uphoff 23 Javier Vazquez 24 Jose Vazquez 25 Alexa Vanderson 26 Dale Wong 27 Julie Weiss 28 Mary Winslow 29 Brandon Waddell 30 Aaron Yee 31 Kay Yamashita 32 Richard Young X fx C Age 22 19 19 22 24 24 18. 21 25 21 21 23 18 25 24 24 20 26 19 23 18. 22 18 28 24 24 21 20 19 22 3 Documentation D Years of Experience 4 1 1 4 6 6 0 3 5 3 3 5 0 7 6 4 2 8 2 5 0 4 0 4 6 6 3 2 1 4 Page Layout Normal Page Break Custom Views Preview All Employees 7 Y Y Y T T T T T Y T Y 7 T F T T T T T Y Y Y T T T Data Review Workbook Views E Pay Rate F Cert. Year 2018 2022 2022 2017 2015 2018 2022 2019 2019 2020 2020 2018 2022 2017 2018 2020 2020 2017 2021 2017 2022 2018 2022 2018 2017 2018 2019 2019 2021 2018 Melbourne Swim Teams View Help G CPR Trained Yes No Navigation Gridlines Headings Yes Yes Yes Yes No No Yes Yes No Yes No Yes Yes Yes Yes Yes No Yes No Yes No Yes Yes Yes Yes Table Design 11 Ruler Show H Lifeguard Yes No No No Yes Yes No No Yes No No No No Yes Yes No No Yes No Yes No No No Yes Yes Yes No No No Yes Formula Bar Yes Yes Yes Melbourne PivotTable I Instructor Yes No No Yes No No No No Yes No No No No Yes No Yes Yes No Yes No No No No No Yes Yes Yes No Yes No All Teams Zoom 100 100% Zoom to Selection. + Zoom Camp Counselor Yes No No Yes Yes Yes No Yes Yes Yes Yes Yes No Yes Yes Yes No Yes No Yes No Yes No Yes Yes Yes Yes No No Yes 4 1 C K Swim Team Yes No Yes Yes Yes Yes No Yes Yes New Window Arrange All Freeze Panes Yes Yes Yes No Yes Yes Yes Yes Yes Yes Yes No Yes No Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes L Team Coach Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Split Hide Yes Yes No Unhide Window 9 3 2 Switch Windows M CPR Course No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No 1 1 T || 11 1 Comments 11 2 1 N Columul 1 2 2 2 1 1 2 1 1 2 1 2 2 2 1 2 1 2 1 2 1 2 2 2 1 1 1 Mac 2 Mac 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. 2. 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. 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 33, enter a formula using the IF function and structured references as follows to determine if Tiffany Adams can work as a camp counselor: a. b. 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. Fill the formula into the range 34:332, if necessary. File Default H D10 Home Insert Draw Page Layout Formulas. Keep B 2 Name 3 Tiffany Adams 4 Hassan Bachim 5 Megan Beckman 6 Catherine Coleman 7 Carrie Faulkner 8 Paul Gomez Exit New Options 13 Jeff Newman 14 Crystal Olneck 15 Yolanda Padilla 9 Bryant Grossburg 0 Marilyn Jesberger 11 Jonathan Lampert 12 Gina Milota 19 Ryan Quinlan 20 Andrea Shelby 21 Anna Tran 16 Michael Pienkowski 17 Keith Pienkowski 18 James Quinlan Sheet View 22 Tracy Uphoff 23 Javier Vazquez 24 Jose Vazquez 25 Alexa Vanderson 26 Dale Wong 27 Julie Weiss 28 Mary Winslow 29 Brandon Waddell 30 Aaron Yee 31 Kay Yamashita 32 Richard Young X fx C Age 22 19 19 22 24 24 18. 21 25 21 21 23 18 25 24 24 20 26 19 23 18. 22 18 28 24 24 21 20 19 22 3 Documentation D Years of Experience 4 1 1 4 6 6 0 3 5 3 3 5 0 7 6 4 2 8 2 5 0 4 0 4 6 6 3 2 1 4 Page Layout Normal Page Break Custom Views Preview All Employees 7 Y Y Y T T T T T Y T Y 7 T F T T T T T Y Y Y T T T Data Review Workbook Views E Pay Rate F Cert. Year 2018 2022 2022 2017 2015 2018 2022 2019 2019 2020 2020 2018 2022 2017 2018 2020 2020 2017 2021 2017 2022 2018 2022 2018 2017 2018 2019 2019 2021 2018 Melbourne Swim Teams View Help G CPR Trained Yes No Navigation Gridlines Headings Yes Yes Yes Yes No No Yes Yes No Yes No Yes Yes Yes Yes Yes No Yes No Yes No Yes Yes Yes Yes Table Design 11 Ruler Show H Lifeguard Yes No No No Yes Yes No No Yes No No No No Yes Yes No No Yes No Yes No No No Yes Yes Yes No No No Yes Formula Bar Yes Yes Yes Melbourne PivotTable I Instructor Yes No No Yes No No No No Yes No No No No Yes No Yes Yes No Yes No No No No No Yes Yes Yes No Yes No All Teams Zoom 100 100% Zoom to Selection. + Zoom Camp Counselor Yes No No Yes Yes Yes No Yes Yes Yes Yes Yes No Yes Yes Yes No Yes No Yes No Yes No Yes Yes Yes Yes No No Yes 4 1 C K Swim Team Yes No Yes Yes Yes Yes No Yes Yes New Window Arrange All Freeze Panes Yes Yes Yes No Yes Yes Yes Yes Yes Yes Yes No Yes No Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes L Team Coach Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Split Hide Yes Yes No Unhide Window 9 3 2 Switch Windows M CPR Course No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No 1 1 T || 11 1 Comments 11 2 1 N Columul 1 2 2 2 1 1 2 1 1 2 1 2 2 2 1 2 1 2 1 2 1 2 2 2 1 1 1 Mac 2 Mac