9 5 In the Enrollment sheet, in cell E2, enter a lookup function that will look up the fee from the FeeLookup Table based on the Class in cell B2. Fill down. Hint: The FeeLookup Table 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. 10 7 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. E2 xv fx B D E F G Class Fee Commission Rate Gender F M M F Clothes Purchase $25.00 $35.00 M F ZUM23 STPA22 STPA22 YOG23 SWM39 ZUM23 ZUM23 STPA22 ZUM23 AER39 TJQ22 JAZ49 ZUM23 AER39 $75.00 $15.00 $25.00 $25.00 F M M F F $25.00 $35.00 F $25.00 $25.00 TJQ22 SWM39 M F M M F M A 1 Student_ID 2 X2396G23 3 X6260623 4 X2637623 5 X6649623 6 X3307623 7 T4742623 8 T1011623 9 T6528G23 10 T2068G23 11 X8696423 12 T9032623 13 T5701623 14 X6613623 15 T5406623 16 T5178G23 17 X5420623 18 T4814623 19 X9593G23 20 T1993G23 21 T4370623 22 X3127623 23 X2204623 24 T431323 25 T3335623 26 T4327623 27 X8027623 28 X1934623 29 X5554623 30 T5210623 31 X5585623 32 X7651623 33 T3628G23 34 X9847623 35 T4087623 36 T4297623 37 T2084623 38 X7597623 39 T3720G23 40 T4413623 41 T3335623 42 T3994623 $25.00 $15.00 $35.00 $76.00 $60.00 $25.00 $25.00 $15.00 M F STPA22 ZUM23 AER39 TJQ22 JAZ49 SWM39 STPA22 SWM39 ZUM23 AER39 YOG23 M F F F $15.00 F F F M $35.00 $15.00 $25.00 $45.00 $35.00 $15.00 JAZ49 SWM39 STPA22 SWM39 ZUM23 ZUM23 JAZ49 AER39 YOG23 YOG23 YOG23 SWM39 SWM39 TJQ22 M F F M F F M F M M F F F $25.00 $25.00 $35.00 $15.00 $15.00 $15.00 $68.00 StudentSales Currency Exchange Enrollment Class Report Personal Loan