$2$ The Week worksheet contains data for the week of August $5.$

In cell D$7,$ insert the appropriate date function to calculate the number of days between the Date Arrived and Date Ordered. Copy the function to the range D$8$:D$35.$

$3$ You want to display the weekday for the arrival dates.

In cell E$7,$ insert the WEEKDAY function to identify an integer representing the weekday of the Date Arrived. Copy the function to the range E$8$:E$35.$

$4$ You need to format the WEEKDAY function results with a custom number format.

Select the range E$7$:E$35,$ apply the custom number format dddd$,$ and apply center horizontal alignment. $2$

$5$ Next, you want to display the city names that correspond with the city airport codes.

In cell G$7,$ insert the SWITCH function to evaluate the airport code in cell E$7.$ Include mixed cell references to the city names in the range G$2$:G$4.$ Use the airport codes as text for the Value arguments: AUS for Austin, DFW for Dallas$-$Fort Worth, and IAH for Houston. Copy the function to the range G$8$:G$35.$

$6$ Now you want to display the standard shipping costs by city.

In cell I$7,$ insert the IFS function to identify the shipping cost based on the airport code and the applicable shipping rates in the range H$2$:H$4.$ Enter the airport codes in this sequence: AUS, DFW$,$ IAH $($with their respective costs$).$ Use relative and mixed references correctly. Copy the function to the range I$8$:I$35.$

$7$ Finally, you want to calculate a partial shipping refund if two conditions are met.

In cell J$7,$ 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 greater than Total Days Delivery Goal $($cell C$3)$ and Order Total is equal to or greater than Order Total Threshold $($cell C$2).$ If both conditions are met, the refund is $50\%($cell C$4)$ of the Shipping Cost. Otherwise, the refund is $$0.$ Use mixed references as needed. Copy the function to the range J$8$:J$35.$

$8$ The Stats worksheet contains similar data. Now you want to enter summary statistics.

In cell B$2,$ insert the COUNTIF function to count the number of shipments for Austin $($cell B$1).$ Use appropriate mixed references to the range argument to keep the column letters the same. Copy the function to the range C$2$:D$2.$

$9$ In cell B$3,$ insert the SUMIF function to calculate the total orders for Austin $($cell B$1).$ Use appropriate mixed references to the range argument to keep the column letters the same. Copy the function to the range C$3$:D$3.$

$10$ In cell B$4,$ insert the AVERAGEIF function to calculate the average number of days for shipments from Austin $($cell B$1).$ Use appropriate mixed references to the range argument to keep the column letters the same. Copy the function to the range C$4$:D$4.$

$11$ Now you want to focus on shipments from Houston where the order was greater than $$1,000.$

In cell C$7,$ insert the COUNTIFS function to count the number of orders where the Airport Code is IAH $($Cell D$1)$ and the Order Total is greater than $$1,000.$

$12$ In cell C$8,$ insert the SUMIFS function to calculate the total orders where the Airport Code is IAH $($Cell D$1)$ and the Order Total is greater than $$1,000.$

$13$ In cell C$9,$ insert the MAXIFS function to return the highest order total where the Airport Code is IAH $($Cell D$1)$ and the Order Total is greater than $$1,000.$

$14$ On the Map worksheet, insert a map for the states and revenues. Cut and paste the map in cell C$1.$

$15$ Format the data series to show only regions with data and show all map labels.

$16$ Change the map title to August $5-9$ Gross Revenue.

$17$ Use the Loan worksheet to complete the loan amortization table.

In cell F$2,$ insert the IPMT function to calculate the interest for the first payment. Copy the function to the range F$3$:F$25.($The results will update after you complete the other functions and formulas.$)$

$18$ In cell G$2,$ insert the PPMT function to calculate the principal paid for the first payment. Copy the function to the range G$3$:G$25.$

$19$ In cell H$2,$ insert a formula to calculate the ending principal balance. Copy the formula to the range H$3$:H$25.$

$20$ Now you want to determine how much interest was paid during the first two years.

In cell B$10,$ insert the CUMIPMT function to calculate the cumulative interest after the first two years. Make sure the result is positive. $5$

$21$ In cell B$11,$ insert the CUMPRINC function to calculate the cumulative principal paid at the end of the first two years. Make sure the result is positive.

$22$ You want to perform a what$-$if analysis to determine the rate if the monthly payment is $$1,150$ instead of $$1,207\mathrm{.}87.$

In cell B$15,$ insert the RATE function to calculate the necessary monthly rate given the NPER, proposed monthly payment, and loan. Make sure the result is positive.

$23$ Finally, you want to convert the monthly rate to an APR.

In cell B$16,$ insert a formula to calculate the APR for the monthly rate in cell B$15.$