In the Enrollment sheet, in cell E2, enter a lookup function that will look up the fee from the Eeelookup Table based on the Class in cell B2. Fill down. Hint The Eeel.okunTable can be found on the Class Report workbook, and the range name has already been created for you. Be sure to look at the table to figure out which type of lookup you will need, and whether you will need an exact match or not. In the Enrollment sheet, in F2, create an IF statement that will calculate the commission rate. If the Clothes Purchase amount is zero, the commission rate is 4%. If the Clothes Purchase amount is greater than zero, but the student is male, the commission rate is 3%. If the student is female, the commission rate is 2%. Hint Just put the commission rate, you don't have to calculate the commission. On the Enrollment worksheet, create named ranges using the top row as the name for your named ranges. In other words, Student.ID should be a range from A2:A94, Class should be a range from B2:B94, etc. DO NOT name each one individually but create all the names at one time from the selection. Hint: You will be using these ranges in the following instructions. You should be able to do this with two mouse clicks if you choose the right menu. In the Class Report sheet, in cell B3, enter a function that will count the number of students in the class listed in A3 (i.e. ZUM23). Make sure to use the Class Range name. Enter a function in cell C3 that sums the Clothes Purchase for the class listed in A3 (1.e. ZUM23). Again, be sure to use the range name. Fill both down so that the counts and sums appear in the rest of the cells from B4 to C9. B C G 1 Student ID 2 X2396G23 3 X6260623 4 X2637423 Gender F D Clothes Purchase Fee $25.00 $35.00 M M F M F F $75.00 $15.00 $25.00 $25.00 M M $25.00 $35.00 5 X6649G23 6 X3307623 7 T4742623 8 T1011623 9 T6528623 10 T2068G23 11 X8696G23 12 T9032623 13 T5701623 14 X6613G23 15 T5406623 16 T5178423 17 X5420G23 18 T4814623 19 X9593G23 20 T1993G23 21 T4370G23 22 X3127G23 22 VAG Class ZUM23 STPA22 STPA22 YOG23 SWM39 ZUM23 ZUM23 STPA22 ZUM23 AER39 TJQ22 JA249 ZUM23 AER39 TJQ22 SWM39 STPA22 ZUM23 AER39 TJQ22 JAZ49 CAMINADO Faculty Data F F F M E F Commission Rate $20.00 $30.00 $30.00 $10.00 $10.00 $20.00 $20.00 $30.00 $20.00 $30.00 $20.00 $20.00 $20.00 $30.00 $20.00 $10.00 $30.00 $20.00 $30,00 $20.00 $20.00 cinn Enrollment Class Report Personal Loan $25.00 $25.00 F M M F M M $25.00 $15.00 $35.00 $76.00 $60.00 $25.00 $25.00 C15 Currency Exchange F M Student Sales Enrollment Analysis Surn Clothes Enrollment Count per Purchase per Class Class Type Type Class ZUM23 JAZ49 YOG23 STPA22 SWM39 AER39 TJQ22 Count of Student ID Maximum Clothes Purchase Average Fas 4 Totalleer 5 Count Males taking T1Q22 5 Sum clothes purchase for Females taking STPA22 18 19 20 21 22 23 24 Class 10 25 Class Category 26 Target Class Sie 27 Can Split 28 Fee Lookup Table for Fees ZUM23 Zumba 20 Y $50.00 AERJ9 Aerobics JAZAS Jazercise 20 Y $35.00 YOG23 Yoga 10 N $40.00 STPA22 Step Aerobics 30 SWM39 Swimmerize 10 Y 525.00 TIQ22 Tai Chi 20 N $75.00 Y Y $25.00 $30.00 Class Category Target Class Size Can Split Fee Lookup Table for Fees ZUM23 Zumba 20 Y $50.00 JAZ49 Jazercise 20 Y $35.00 YOG23 Yoga 10 N STPA22 Step Aerobics 30 Y $25.00 SWM39 Swimmercise 10 Y $25.00 AER39 Aerobics 30 Y $30.00 TJQ22 Hihi 20 N $75.00 $40.00 In the Enrollment sheet, in cell E2, enter a lookup function that will look up the fee from the Eeelookup Table based on the Class in cell B2. Fill down. Hint The Eeel.okunTable can be found on the Class Report workbook, and the range name has already been created for you. Be sure to look at the table to figure out which type of lookup you will need, and whether you will need an exact match or not. In the Enrollment sheet, in F2, create an IF statement that will calculate the commission rate. If the Clothes Purchase amount is zero, the commission rate is 4%. If the Clothes Purchase amount is greater than zero, but the student is male, the commission rate is 3%. If the student is female, the commission rate is 2%. Hint Just put the commission rate, you don't have to calculate the commission. On the Enrollment worksheet, create named ranges using the top row as the name for your named ranges. In other words, Student.ID should be a range from A2:A94, Class should be a range from B2:B94, etc. DO NOT name each one individually but create all the names at one time from the selection. Hint: You will be using these ranges in the following instructions. You should be able to do this with two mouse clicks if you choose the right menu. In the Class Report sheet, in cell B3, enter a function that will count the number of students in the class listed in A3 (i.e. ZUM23). Make sure to use the Class Range name. Enter a function in cell C3 that sums the Clothes Purchase for the class listed in A3 (1.e. ZUM23). Again, be sure to use the range name. Fill both down so that the counts and sums appear in the rest of the cells from B4 to C9. B C G 1 Student ID 2 X2396G23 3 X6260623 4 X2637423 Gender F D Clothes Purchase Fee $25.00 $35.00 M M F M F F $75.00 $15.00 $25.00 $25.00 M M $25.00 $35.00 5 X6649G23 6 X3307623 7 T4742623 8 T1011623 9 T6528623 10 T2068G23 11 X8696G23 12 T9032623 13 T5701623 14 X6613G23 15 T5406623 16 T5178423 17 X5420G23 18 T4814623 19 X9593G23 20 T1993G23 21 T4370G23 22 X3127G23 22 VAG Class ZUM23 STPA22 STPA22 YOG23 SWM39 ZUM23 ZUM23 STPA22 ZUM23 AER39 TJQ22 JA249 ZUM23 AER39 TJQ22 SWM39 STPA22 ZUM23 AER39 TJQ22 JAZ49 CAMINADO Faculty Data F F F M E F Commission Rate $20.00 $30.00 $30.00 $10.00 $10.00 $20.00 $20.00 $30.00 $20.00 $30.00 $20.00 $20.00 $20.00 $30.00 $20.00 $10.00 $30.00 $20.00 $30,00 $20.00 $20.00 cinn Enrollment Class Report Personal Loan $25.00 $25.00 F M M F M M $25.00 $15.00 $35.00 $76.00 $60.00 $25.00 $25.00 C15 Currency Exchange F M Student Sales Enrollment Analysis Surn Clothes Enrollment Count per Purchase per Class Class Type Type Class ZUM23 JAZ49 YOG23 STPA22 SWM39 AER39 TJQ22 Count of Student ID Maximum Clothes Purchase Average Fas 4 Totalleer 5 Count Males taking T1Q22 5 Sum clothes purchase for Females taking STPA22 18 19 20 21 22 23 24 Class 10 25 Class Category 26 Target Class Sie 27 Can Split 28 Fee Lookup Table for Fees ZUM23 Zumba 20 Y $50.00 AERJ9 Aerobics JAZAS Jazercise 20 Y $35.00 YOG23 Yoga 10 N $40.00 STPA22 Step Aerobics 30 SWM39 Swimmerize 10 Y 525.00 TIQ22 Tai Chi 20 N $75.00 Y Y $25.00 $30.00 Class Category Target Class Size Can Split Fee Lookup Table for Fees ZUM23 Zumba 20 Y $50.00 JAZ49 Jazercise 20 Y $35.00 YOG23 Yoga 10 N STPA22 Step Aerobics 30 Y $25.00 SWM39 Swimmercise 10 Y $25.00 AER39 Aerobics 30 Y $30.00 TJQ22 Hihi 20 N $75.00 $40.00