Instructions for Widget Works

Excel Expert $365$ Project

Complete the following steps on the Sales worksheet:

In range $P3$:$P6,$ use a function to determine the number of orders placed for each product.

In range Q$3$:Q$6,$ use a function to determine the total number of units sold for each product.

In range R$3$:R$6,$ use a function to determine the average revenue for each product.

In range S$3$:S$6,$ use a function to determine the highest amount sold for each widget.

In range T$3$:T$6,$ use a function to determine the lowest amount sold for each widget.

In range P$10$:R$14,$ use a function to determine the number of orders placed, total number of units sold, and average revenue for Widget A based on the salesperson.

In range P$17$:R$21,$ use a function to determine the number of orders placed, total number of units sold, and average revenue for Widget $B$ based on the salesperson.

In range P$24$:R$28,$ use a function to determine the number of orders placed, total number of units sold, and average revenue for Widget C based on the salesperson.

In range P$31$:R$35,$ use a function to determine the number of orders placed, total number of units sold, and average revenue for Widget D based on the salesperson.

In cell Q$37$ use a function to input the current date and time to reflect when calculations were completed.

In cell W$2,$ use function$($s$)$ to display the revenue based on the invoice number is cell V$2.$

Complete the following steps on the Sales by Region worksheet:

In cell A$3,$ use a function to show only data for sales in the region listed in cell B$1.$

In cell $A24,$ use a function to sort the Sales table putting them in order from largest to smallest.

Complete the following steps on the Delivery Schedule worksheet:

In the Business Days column, use a function to determine the date of delivery assuming all shipments must be delivered in $5$ business days with no holidays.

In the Weekday column, use a function to determine the day of the week each delivery was made. Use Monday $=1$ and Sunday$=7.$

In the On Time column, use a function to display "Early" if the shipment was delivered before the expected date, $"$On Time" if the shipment was delivered on the expected date, and "Late" if delivered after the expected delivery date,

In the Contact Customer column, use function that displays "Contact" if the shipment was late, otherwise is blank.

In cell K$2,$ use a function to pull the Product Category based on the Product ID in cell K$1.$

In cell K$3,$ use a function to pull the Salesperson based on the Product ID in cell K$1.$ Use a different function than used in Task $5.$

In cell K$5,$ use a function to enter today's date. The date should not display time.