Step Instructions Points Possible 1 Start Excel. Open the downloaded Excel file named e03 grader a1 Fitness.xlsx. Save the fle with the name e03_grader_a1 Fitness LastFirst replacing LastFirst with your last and first name. 2 Copy the Enrollment worksheet to the end of the workbook to make a backup copy of the data. Rename the worksheet EnrollmentBackup 1 On the InputData worksheet, select range B4 H8 Create named ranges using the top row as the name for your named ranges. Each range indudes fitness class details With B4 H8 still selected, enter ClassInfo into the Narne Box to name the table. 6 4 Select range B11H14 Create named ranges using the top row as the name for your named ranges. Each range is the list of instructors that can teach individual classes. 6 5 On the Enrollment worksheet, in cell A13, insert a table using range A13 D 106 and the top row as the headers 5 6 In cell D14, enter an HLOOKUP function that will look up the class number in cell B14 and will use the ClassInfo table to return the Fee from the fifth row of the table with an exact match 7 In cell E13, create a new column header named Class Name. 7 In cell E14, enter an HLOOKUP function that will look up the class number in cell B14 and use the Classinfo table to return the Class Category name from the second row of the table with an exact match 8 9 Select A13 E 106, and then name the cell range Enrollment. With A13 E 106 still selected, create named ranges using the top row as the name for the named ranges Copy range A13:E13, and then paste in range A1:E1 to set up the advanced filter criteria area. Enter the following filter criteria. In cell C2, type F In cell E2 type Yoga 9 In cell Bo create a DCOUNTA function to count the Student ID field in the Enrollment table that meet the filter criteria specified in range A1E2 9 9 In cell B6, create a DCOUNTA function to count the Student_ID field in the Enrollment table that meet the filter criteria specified in range A1:E2 In cell B7, create a DAVERAGE function to average the Fee field in the Enrollment table that meet the filter criteria specified in range A1:E2. This will average all yoga fees collected from females. In cell B8, create a DSUM function to sum the Fee field in the Enrollment table that meet the filter criteria specified in range A1E2. This will total all yoga fees collected from females. Created On: 07/05/2019 1 YO16_XL_BUO3_GRADER_PS3 AS - F Grader - Instructions Excel 201 Step Instructions Poi Pos On the Report worksheet, create calculations that will help hotel employees manage the fitness class enrollments. The user will put an "x" in range E4 E 10, indicating which class to report on and an in range H4 H5 if employees want a report on a specific gender In cell A4 use a MATCH function nested in an INDEX function to retrieve the Class that was selected in E4 E10. The MATCH should find the row where the x is located and would be used within the INDEX to pull the associated Class value from the same row within range F4:F10 A1 B D E H 1 2. 3 223 J49 Jazzercise Y23 Yoga 10 $39 Swimmercize Zumba 4. Class ID 5 Class Category 6 Target Class Size 7 Can Split 8 Fee T22 Ti Chi A22 Step Aerobics 30 20 20 10 A39 Aerobics 30 Y $30.00 20 Y Y N Y Y N $75.00 $50.00 $35.00 $40.00 $25.00 $25.00 9 10 11 Jazzercise Joe Jill Yoga_1 Shanti Zumba Joe Jill Alesha Tai Chi Shanti Step Aerobics I swimmercize 1 Joe Francine Bill 12 Aerobics Joe Bill Alesha 13 14 15 16 17 18 19 20 21 22 23 24 25 + 26 27 Enrollment InputData Report 11 Ready BH o Type here to search D G H 1 2 3 Class Gender Enrolled Report Class Z23 Report Gender F 4 X 5 149 M Y23 A22 539 X 6 7 Max Enrollment 8 Class Type 9 10 NOTES 11 Availability 12 Number of Instructors 13 Options 14 A39 T22 15 16 118 IFT 21 THE 22 PH 23 24 + 25 126 27 Enrollment InputData Report B 82 14894623 A22 F Z23 83 T5799423 84 X6421623 S39 85 T7814G23 Y23 A22 86 T9540G23 87 X3322623 T22 88 X4187623 A22 S39 89 T9348G23 90 T3659G23 91 X9070G23 T22 S39 92 X8475G23 Z23 93 X1573G23 S39 T22 3 TT 3 3 3 + 3 + 3 3 3 3 3 3 3 3 3 Z23 Z23 T22 T22 A22 94 T1443G23 95 X2114623 96 X9420G23 97 X6557623 98 X2280G23 99 X6273G23 100 X5727423 101 T3358G23 102 X3760G23 103 X6692623 104 X8828G23 105 X5411623 106 X6548G23 107 108 -- Z23 A22 Y23 Y2B A22 $39 $39 Enrollment InputData Report