10 Copy the formula down through G 16. on 9 On the Transactions worksheet, in cell H10, replace the static value by entering a VLOOKUP function to retrieve the item's price from the Product_List table and then multiply it by the quantity to calculate the gross revenue. Use the fill handle to copy the formula down through H30 10 On the Transactions worksheet, in cell J10, replace the static value by entering a HLOOKUP function to retrieve the discount value from the range B4:06 based on the coupon code used in the transaction. Use the fill handle to copy the formula down through J30. M N Reward Program Discounts Rewards: Rewards2 0.1 0.1 0.1 0.1 0.1 Club Member? Promoltem? GrossRevenue MemberDisc Coupon CashDisc 0 No NO $ 171.00 1 No Yes $ 65.90 12 Ves NO $ 129.00 7% 3 No Yes $ 177.00 14 NO No S 387.00 15% 15 You NO $ 46.35 7% 35% 16 Ves Yes S 451.00 7% 17 NO NO $ 12.00 11 NO NO $ 114.00 19 No Yes 5 1.257.00 JON 20 NO NO $ 2.00 10% 21 Yes NO 5 2.00 7% 15% 22 No Yes S 5 23 No NA S 17.85 5 24 No NO 114.00 15 25 No Ves 59:00 20 27 NetRevenue TotalDiscount 5% $ 136.80S 34.20 $ 65.90S $ 119.97 $ 9.03 $ 177.00 $ 5S 309.60 $ 77.40 $ 36.15$ 10.20 5 421.29 $ 31.71 5 12.00$ 55 108,308 5.70 $ 1,131.0 125,70 5 1.30 0.20 1565 0.44 $ 142 16 7:49 5 16.16 0.89 164,05 29.10 5 59.00 01 0.1 01 Employee Analysis Customer Analys Shipping com Transactions Product + 2 3 4 Weight (lbs) 5 0.5 6 2 7 4 8 6 Zone 1 $6.35 $6.35 $6.89 $7.29 $8.24 $8.95 Priority 5.5 Zone 2 Priority Mail Rates Zone 2 Zone 3 $6.35 $6.79 $6,35 $6.79 $6.89 $7.03 $7.29 $8.53 $8.24 $9.63 $8.95 $10.18 Zone 4 $6.89 $6.89 $7.18 $9.42 $10.99 $11.12 Zone 5 $6.98 $6.98 $7.91 $13.77 $16.84 $19.51 Shipping Method Weight (lbs) Zone Shipping Costs 9 8 10 10 11 12 0,5 13 14 Weight (lbs) 15 16 2 17 4 18 6 19 8 20 10 21 Zone 1 $7.62 $7.62 $8.27 $8.75 $9.89 $10.74 Express Mail Rates Zone 2 Zone 3 $7.62 $8.15 $7.62 $8.15 $8.27 $8.44 $8.75 $10.24 $9.89 $11.56 $10.74 $12.22 Zone 4 $8.27 $8.27 $8.62 $11.30 $13.19 $13.34 Zone 5 $8.38 $8.38 $9.49 $16.52 $20.21 $23,41 10 Copy the formula down through G 16. on 9 On the Transactions worksheet, in cell H10, replace the static value by entering a VLOOKUP function to retrieve the item's price from the Product_List table and then multiply it by the quantity to calculate the gross revenue. Use the fill handle to copy the formula down through H30 10 On the Transactions worksheet, in cell J10, replace the static value by entering a HLOOKUP function to retrieve the discount value from the range B4:06 based on the coupon code used in the transaction. Use the fill handle to copy the formula down through J30. M N Reward Program Discounts Rewards: Rewards2 0.1 0.1 0.1 0.1 0.1 Club Member? Promoltem? GrossRevenue MemberDisc Coupon CashDisc 0 No NO $ 171.00 1 No Yes $ 65.90 12 Ves NO $ 129.00 7% 3 No Yes $ 177.00 14 NO No S 387.00 15% 15 You NO $ 46.35 7% 35% 16 Ves Yes S 451.00 7% 17 NO NO $ 12.00 11 NO NO $ 114.00 19 No Yes 5 1.257.00 JON 20 NO NO $ 2.00 10% 21 Yes NO 5 2.00 7% 15% 22 No Yes S 5 23 No NA S 17.85 5 24 No NO 114.00 15 25 No Ves 59:00 20 27 NetRevenue TotalDiscount 5% $ 136.80S 34.20 $ 65.90S $ 119.97 $ 9.03 $ 177.00 $ 5S 309.60 $ 77.40 $ 36.15$ 10.20 5 421.29 $ 31.71 5 12.00$ 55 108,308 5.70 $ 1,131.0 125,70 5 1.30 0.20 1565 0.44 $ 142 16 7:49 5 16.16 0.89 164,05 29.10 5 59.00 01 0.1 01 Employee Analysis Customer Analys Shipping com Transactions Product + 2 3 4 Weight (lbs) 5 0.5 6 2 7 4 8 6 Zone 1 $6.35 $6.35 $6.89 $7.29 $8.24 $8.95 Priority 5.5 Zone 2 Priority Mail Rates Zone 2 Zone 3 $6.35 $6.79 $6,35 $6.79 $6.89 $7.03 $7.29 $8.53 $8.24 $9.63 $8.95 $10.18 Zone 4 $6.89 $6.89 $7.18 $9.42 $10.99 $11.12 Zone 5 $6.98 $6.98 $7.91 $13.77 $16.84 $19.51 Shipping Method Weight (lbs) Zone Shipping Costs 9 8 10 10 11 12 0,5 13 14 Weight (lbs) 15 16 2 17 4 18 6 19 8 20 10 21 Zone 1 $7.62 $7.62 $8.27 $8.75 $9.89 $10.74 Express Mail Rates Zone 2 Zone 3 $7.62 $8.15 $7.62 $8.15 $8.27 $8.44 $8.75 $10.24 $9.89 $11.56 $10.74 $12.22 Zone 4 $8.27 $8.27 $8.62 $11.30 $13.19 $13.34 Zone 5 $8.38 $8.38 $9.49 $16.52 $20.21 $23,41