File Home Insert Draw Page Layout Formulas Data Review View Help B Share 11 fx A B C D E F G H K M N Cost of Amount of Discount Payment Method Gas Gas Option N N Analysis Criteria Rewards Express Miles Credit Card Cash Analysis Critieria Rewards Express Miles Credit Card Cash N N Y Y Number of Rentals N Y N 275 N Y Y 5 Auto Type 6 Green Collection 7 Compact/Midsize 8 Compact/Midsize 9 Green Collection 10 Fullsize/Standard 11 SUV/Minivan 12 SUV/Minivan 13 Fullsize/Standard 14 Green Collection 15 Compact/Midsize 16 Fullsize/Standard 17 Fullsize/Standard 18 Fullsize/Standard 19 SUV/Minivan 20 SUV/Minivan 21 Green Collection 22 Green Collection 23 Green Collection 24 Fullsize/Standard 25 SUV/Minivan 26 SUV/Minivan 27 Fullsize/Standard 28 Fullsize/Standard 29 Compact/Midsize 30 Compact/Midsize 31 SUV/Minivan 32 Green Collection RentalData Payment Method Credit Card Cash Express Miles Credit Card Credit Card Rewards Credit Card Credit Card Credit Card Cash Credit Card Credit Card Rewards Express Miles Cash Credit Card Express Miles Credit Card Rewards Express Miles Express Miles Credit Card Express Miles Credit Card Rewards Cash Express Miles Date Returned/or Auto Id Date Rented Expected Return #Days Rented 988 4/5/2022 4/8/2022 3 275 4/5/2022 4/10/2022 5 277 4/9/2022 4/10/2022 1 990 4/5/2022 4/6/2022 1 350 4/5/2022 4/8/2022 3 550 4/5/2022 4/9/2022 4 551 4/7/2022 4/10/2022 3 352 4/7/2022 4/9/2022 2 989 4/6/2022 4/9/2022 3 4/11/2022 4/18/2022 7 355 4/9/2022 4/16/2022 7 356 4/6/2022 4/8/2022 2 350 4/9/2022 4/13/2022 4 553 4/8/2022 4/14/2022 6 550 4/11/2022 4/17/2022 6 988 4/9/2022 4/15/2022 6 989 4/10/2022 4/16/2022 6 990 4/7/2022 4/10/2022 3 356 4/9/2022 4/10/2022 1 551 4/11/2022 4/15/2022 4 554 4/7/2022 4/9/2022 2 352 4/10/2022 4/11/2022 1 356 4/11/2022 4/17/2022 6 276 4/11/2022 4/14/2022 3 277 4/11/2022 4/15/2022 4 554 4/10/2022 4/12/2022 2 990 4/11/2022 4/18/2022 7 ClientData + Y Daily Rate $49.99 $76.49 $76.49 $49.99 $84.99 $104.99 $104.99 $84.99 $49.99 $76.49 $84.99 $84.99 $84.99 $104.99 $104.99 $49.99 $49.99 $49.99 $84.99 $104.99 $104.99 $84.99 $84.99 $76.49 $76.49 $104.99 $49.99 Total Cost $149.97 $382.45 $76.49 $49.99 $254.97 $419.96 $314.97 $169.98 $149.97 $535.43 $594.93 $169.98 $339.96 $629.94 $629.94 $299.94 $299.94 $149.97 $84.99 $419.96 $209.98 $84.99 $509.94 $229.47 $305.96 $209.98 $349.93 Y Y N N N N Y Y Y Y N N Y N Ready 30 Dougherty Gift Baskets 556.17 $ Wedding Summary 0.46 $ SpaServices Null Commission Wedding Financing SalesAnalysis Documentation + 3 3 3 Assigned a named range of RentalRates to cell range A37 B40. 4 4 In cell G6, use the appropriate lookup and reference function to retrieve the rental rate from the named range RentalRates. The function should look for an exact matching value from column A in the data. Copy the formula through cell G32 5 5 In cell B42, assign a named range of Discount In cell B44, assign a named range of Gas Cost 6 In cell H6, enter a formula to determine any discount that should be applied. If the payment method in column F was Rewards, the customer should receive the discount shown in B42, otherwise the formula should return a zero. Use the named range for cell B42, not the cell address, in this formula Copy the formula through cell range H7:H32 7 In cell J6, enter a formula to determine the cost of gas. If the value in column I is "Y", then assign the cost of gas in B44, or else the formula should return zero. Use the named range for cell B44 in this formula, not the cell reference. Copy the formula through cell range J7 J32. oo In cell N11, use the appropriate function to count the number of rentals in the data, using the data in column A. 9 In cell N6, determine the total number of times the criteria "Rewards" in cell M6 is used. Use absolute referencing so you can copy the formula through cell N9. 10 In cell Q6, enter a formula to determine the average total cost based on the payment method type in cell P6. Use absolute references where necessary to copy the formula through cell 09. 11 In cell R6, enter a formula to calculate the total cost per payment method based on the payment method in cell P6. Use absolute references so that you can copy the formula through cell R9. 12 On the ClientData worksheet, in cell B2, enter a formula to change the client's name to proper case. Copy the formula through cell B26