The Pro Shop sells a variety of items from numerous categories. Aleeta would like to promote all items that are NOT accessories. On the Transactions worksheet, in cell G10, replace the static value by entering a formula that will return Yes if the category of the Item sold is not Accessories, otherwise return No. Use the fill handle to copy the formula down through G30. To provide incentives for employees to sell more goods, Aleeta has set sales goals for each employee for items belonging to various categories. On the Employee Analysis worksheet, you will use an INDEX function to retrieve the correct sales goal based on the Employeeld and the desired Category. Because INDEX functions need to be told what row and column number to go to, you will use MATCH functions to do the counting an determine the correct row and column numbers for the INDEX to use. On the Employee Analysis worksheet, in cell J23, enter a MATCH function that will return the row number of whichever category is selected in cell H20. Use the fill handle to copy the formula down through J27. In cell K23, enter a MATCH function that will return the column number of the Employeeld in cell F23. Use the fill handle to copy the formula down through K27. In cell H23, enter an INDEX function, using the range G5:K9 as the array, and referencing the values in J23 and K23 for the row and column numbers. Use the fill handle to copy the formula down through H27. Change the category in cell H20 to Accessories to make sure the function retrieves the correct values. 14 3 Aleeta would like to reward the customer who spends the most money at the Pro Shop and needs to be able to easily retrieve their CustomerlD, CustomerName, SalesVolume, and TotalSales for the customer with the largest TotalSales value. On the Customer Analysis worksheet, in cell B20, enter an INDEX function that will retrieve the CustomerlD of the customer with the highest TotalSales from the array A4:16. Use the fill handle to copy the formula down through B23. 15 3 Shipping costs vary depending on method of shipping and the zone number of the destination. Creating a dynamic retrieval function leveraging the INDIRECT function can make it easily to calculate shipping costs for customers. On the Shipping Costs worksheet, create a Priority named range for cells A5:F10 and then create an Express named range for cells A15:F20. In cell 17, enter a VLOOKUP function to retrieve the correct shipping costs based on the weight and zone. Use the INDIRECT function for the table array, so that the customer can choose Express or Priority shipping. Change the shipping method from Priority to Express to ensure the correct shipping cost is retrieved. The Pro Shop sells a variety of items from numerous categories. Aleeta would like to promote all items that are NOT accessories. On the Transactions worksheet, in cell G10, replace the static value by entering a formula that will return Yes if the category of the Item sold is not Accessories, otherwise return No. Use the fill handle to copy the formula down through G30. To provide incentives for employees to sell more goods, Aleeta has set sales goals for each employee for items belonging to various categories. On the Employee Analysis worksheet, you will use an INDEX function to retrieve the correct sales goal based on the Employeeld and the desired Category. Because INDEX functions need to be told what row and column number to go to, you will use MATCH functions to do the counting an determine the correct row and column numbers for the INDEX to use. On the Employee Analysis worksheet, in cell J23, enter a MATCH function that will return the row number of whichever category is selected in cell H20. Use the fill handle to copy the formula down through J27. In cell K23, enter a MATCH function that will return the column number of the Employeeld in cell F23. Use the fill handle to copy the formula down through K27. In cell H23, enter an INDEX function, using the range G5:K9 as the array, and referencing the values in J23 and K23 for the row and column numbers. Use the fill handle to copy the formula down through H27. Change the category in cell H20 to Accessories to make sure the function retrieves the correct values. 14 3 Aleeta would like to reward the customer who spends the most money at the Pro Shop and needs to be able to easily retrieve their CustomerlD, CustomerName, SalesVolume, and TotalSales for the customer with the largest TotalSales value. On the Customer Analysis worksheet, in cell B20, enter an INDEX function that will retrieve the CustomerlD of the customer with the highest TotalSales from the array A4:16. Use the fill handle to copy the formula down through B23. 15 3 Shipping costs vary depending on method of shipping and the zone number of the destination. Creating a dynamic retrieval function leveraging the INDIRECT function can make it easily to calculate shipping costs for customers. On the Shipping Costs worksheet, create a Priority named range for cells A5:F10 and then create an Express named range for cells A15:F20. In cell 17, enter a VLOOKUP function to retrieve the correct shipping costs based on the weight and zone. Use the INDIRECT function for the table array, so that the customer can choose Express or Priority shipping. Change the shipping method from Priority to Express to ensure the correct shipping cost is retrieved
