Cities Austin Dallas-Forth Worth Houston Shipping $ 24 $ 20 $ 30 Shipping Shipping Cost Refund Shipped From 1,055 1 Input Area 2 Order Total Threshold $ 1,000 3 Total Days Delivery Goal 10 4 Shipping Refund Rate 50% 5 Date Date Date Airport 6 Ordered Shipped Arrived Total Days Code 7 4/5/2021 4/6/2021 4/9/2021 3 AUS 8 4/5/2021 4/7/2021 4/15/2021 8 AUS 9 4/5/2021 4/7/2021 4/16/2021 9 IAH 10 4/5/2021 4/7/2021 4/12/2021 5 DFW 11 4/5/2021 4/9/2021 4/16/2021 7 IAH 12 4/5/2021 4/5/2021 4/12/2021 7 DFW 13 4/5/2021 4/8/2021 4/16/2021 AUS 14 4/6/2021 4/8/2021 4/15/2021 7 DFW 15 4/6/2021 4/6/2021 4/9/2021 3 DFW 16 4/6/2021 4/8/2021 4/19/2021 11 AUS 17 4/6/2021 4/8/2021 4/14/2021 6 IAH 18 4/6/2021 4/9/2021 4/19/2021 10 DFW 19 4/7/2021 4/12/2021 4/16/2021 4 IAH 20 4/7/2021 4/9/2021 4/16/2021 7 AUS 21 4/7/2021 4/9/2021 4/14/2021 5 DFW 22 4/7/2021 4/24/2021 4/30/2021 6 DFW 23 4/7/2021 4/12/2021 4/16/2021 4 IAH 24 4/8/2021 4/9/2021 4/14/2021 5 IAH 25 4/8/2021 4/9/2021 4/16/2021 7 DFW 26 4/8/2021 4/8/2021 4/12/2021 4 DFW 27 4/8/2021 4/9/2021 4/13/2021 4 IAH 28 4/8/2021 4/12/2021 4/16/2021 4 AUS 29 4/8/2021 4/13/2021 4/19/2021 6 IAH 30 4/8/2021 4/12/2021 4/19/2021 7 DFW 31 4/9/2021 4/12/2021 4/19/2021 7 AUS 32 4/9/2021 4/12/2021 4/16/2021 4 IAH 33 4/9/2021 4/12/2021 4/16/2021 4 DFW 34 4/9/2021 4/9/2021 4/20/2021 11 AUS 35 4/9/2021 4/9/2021 4/20/2021 11 IAH 36 37 38 39 Order Total $ 975 $ $ 1,075 $ 1,000 $ 2,535 $ 1,890 $ 950 $ 1,485 $ 550 $ 1,250 $ 1,600 $ 2,500 $ 1,425 $ 800 $ 1,000 $ 1,125 S 1,625 $ 1,345 $ 2,100 $ 1,475 S 1,825 S 2,055 $ 1,625 $ 1,000 $ 1,005 $ 1.745 S 2,100 $ 1,005 $ 960 Week Stats Map Loan + Display Settings 3 100% 9:36 PM 5 Next, you want to display the city names that correspond with the city airport codes. In cell F7, insert the SWITCH function to evaluate the airport code in cell E7. Include mixed cell references to the city names in the range F2:F4. Use the airport codes as text for the Value arguments. Copy the function to the range F8:F35. 4. Now you want to display the standard shipping costs by city. 5 In cell H7, insert the IFS function to identify the shipping cost based on the airport code and the applicable shipping rates in the range G2:G4. Use relative and mixed references correctly. Copy the function to the range H8:H35. 5 5 Finally, you want to calculate a partial shipping refund if two conditions are met. In cell 17, insert an IF function with a nested AND function to determine shipping refunds. The AND function should ensure both conditions are met: Total Days is grater than Total Days Delivery Goal (cell C3) and Order Total is equal to or greater than Order Total Threshold (cell C2). If both conditions are met, the refund is 50% (cell C4) of the Shipping Cost. Otherwise, the refund is $0. Use mixed references as needed. Copy the function to the range 18:135. 6 5 The Stats worksheet contains similar data. Now you want to enter summary statistics. In cell B2, insert the COUNTIF function to count the number of shipments for Austin (cell B1). Use appropriate mixed references to the range argument to keep the column letters the same. Copy the function to the range C2:D2. 7 5 In cell B3, insert the SUMIF function to calculate the total orders for Austin (cell B1). Use appropriate mixed references to the range argument to keep the column letters the same. Copy the function to the range C3:D3. 8 5 In cell B4, insert the AVERAGEIF function to calculate the average number of days for shipments from Austin (cell B1). Use appropriate mixed references to the range argument to keep the column letters the same. Copy the function to the range C4:04. Cities Austin Dallas-Forth Worth Houston Shipping $ 24 $ 20 $ 30 Shipping Shipping Cost Refund Shipped From 1,055 1 Input Area 2 Order Total Threshold $ 1,000 3 Total Days Delivery Goal 10 4 Shipping Refund Rate 50% 5 Date Date Date Airport 6 Ordered Shipped Arrived Total Days Code 7 4/5/2021 4/6/2021 4/9/2021 3 AUS 8 4/5/2021 4/7/2021 4/15/2021 8 AUS 9 4/5/2021 4/7/2021 4/16/2021 9 IAH 10 4/5/2021 4/7/2021 4/12/2021 5 DFW 11 4/5/2021 4/9/2021 4/16/2021 7 IAH 12 4/5/2021 4/5/2021 4/12/2021 7 DFW 13 4/5/2021 4/8/2021 4/16/2021 AUS 14 4/6/2021 4/8/2021 4/15/2021 7 DFW 15 4/6/2021 4/6/2021 4/9/2021 3 DFW 16 4/6/2021 4/8/2021 4/19/2021 11 AUS 17 4/6/2021 4/8/2021 4/14/2021 6 IAH 18 4/6/2021 4/9/2021 4/19/2021 10 DFW 19 4/7/2021 4/12/2021 4/16/2021 4 IAH 20 4/7/2021 4/9/2021 4/16/2021 7 AUS 21 4/7/2021 4/9/2021 4/14/2021 5 DFW 22 4/7/2021 4/24/2021 4/30/2021 6 DFW 23 4/7/2021 4/12/2021 4/16/2021 4 IAH 24 4/8/2021 4/9/2021 4/14/2021 5 IAH 25 4/8/2021 4/9/2021 4/16/2021 7 DFW 26 4/8/2021 4/8/2021 4/12/2021 4 DFW 27 4/8/2021 4/9/2021 4/13/2021 4 IAH 28 4/8/2021 4/12/2021 4/16/2021 4 AUS 29 4/8/2021 4/13/2021 4/19/2021 6 IAH 30 4/8/2021 4/12/2021 4/19/2021 7 DFW 31 4/9/2021 4/12/2021 4/19/2021 7 AUS 32 4/9/2021 4/12/2021 4/16/2021 4 IAH 33 4/9/2021 4/12/2021 4/16/2021 4 DFW 34 4/9/2021 4/9/2021 4/20/2021 11 AUS 35 4/9/2021 4/9/2021 4/20/2021 11 IAH 36 37 38 39 Order Total $ 975 $ $ 1,075 $ 1,000 $ 2,535 $ 1,890 $ 950 $ 1,485 $ 550 $ 1,250 $ 1,600 $ 2,500 $ 1,425 $ 800 $ 1,000 $ 1,125 S 1,625 $ 1,345 $ 2,100 $ 1,475 S 1,825 S 2,055 $ 1,625 $ 1,000 $ 1,005 $ 1.745 S 2,100 $ 1,005 $ 960 Week Stats Map Loan + Display Settings 3 100% 9:36 PM 5 Next, you want to display the city names that correspond with the city airport codes. In cell F7, insert the SWITCH function to evaluate the airport code in cell E7. Include mixed cell references to the city names in the range F2:F4. Use the airport codes as text for the Value arguments. Copy the function to the range F8:F35. 4. Now you want to display the standard shipping costs by city. 5 In cell H7, insert the IFS function to identify the shipping cost based on the airport code and the applicable shipping rates in the range G2:G4. Use relative and mixed references correctly. Copy the function to the range H8:H35. 5 5 Finally, you want to calculate a partial shipping refund if two conditions are met. In cell 17, insert an IF function with a nested AND function to determine shipping refunds. The AND function should ensure both conditions are met: Total Days is grater than Total Days Delivery Goal (cell C3) and Order Total is equal to or greater than Order Total Threshold (cell C2). If both conditions are met, the refund is 50% (cell C4) of the Shipping Cost. Otherwise, the refund is $0. Use mixed references as needed. Copy the function to the range 18:135. 6 5 The Stats worksheet contains similar data. Now you want to enter summary statistics. In cell B2, insert the COUNTIF function to count the number of shipments for Austin (cell B1). Use appropriate mixed references to the range argument to keep the column letters the same. Copy the function to the range C2:D2. 7 5 In cell B3, insert the SUMIF function to calculate the total orders for Austin (cell B1). Use appropriate mixed references to the range argument to keep the column letters the same. Copy the function to the range C3:D3. 8 5 In cell B4, insert the AVERAGEIF function to calculate the average number of days for shipments from Austin (cell B1). Use appropriate mixed references to the range argument to keep the column letters the same. Copy the function to the range C4:04