Start with the Inventory worksheet. Use the LEFT function to extract the category ID from the inventory number. Click in cell F3.
Type: =LEFT(
Click cell B3.
Type: ,2)
The formula should look like this: =LEFT(B3,2)
Copy the formula to cells F4:F25.
Switch to the Analysis worksheet. Use the SUMPRODUCT function to calculate the total value of the inventory. Use the values in the Selling Price column and the corresponding values in the Stock column as the Array arguments. Click cell B2.
Type: =SUMPRODUCT(
Click the Inventory sheet tab, and then click and drag to select cells H3:H25.
Type , and then click and drag to select cells I3:I25.
Type ) and press Enter.
The formula should look like this:=SUMPRODUCT(Inventory!H3:H25,Inventory!I3:I25)
Enter a formula to calculate the average selling price. Click cell D2.
Type: =AVERAGE(
Click the Inventory sheet tab, and then click and drag to select cells H3:H25.
Press Enter. The formula should look like this: =AVERAGE(Inventory!H3:H25)
Enter a formula to find the middle selling price. Click cell E2.
Type: =MEDIAN(
Click the Inventory sheet tab, and then click and drag to select cells H3:H25.
Press Enter. The formula should look like this: =MEDIAN(Inventory!H3:H25)
Enter a formula to find the most common selling price. Click cell F2.
Type: =MODE.SNGL(
Click the Inventory sheet tab, and then click and drag to select cells H3:H25.
Press Enter. The formula should look like this:=MODE.SNGL(Inventory!H3:H25)
What if there are multiple selling prices that are the most common? Enter an array formula in cells G2:G5 to find up to four most common selling prices. Select cells G2:G5.
Type: =MODE.MULT(
Click the Inventory sheet tab, and then click and drag to select cells H3:H25.
Press Control Shift Enter. The formula should look like this:{=MODE.MULT(Inventory!H3:H25)}
Use the SUMIFS function to calculate the total number of blue shoes in inventory. Use the values in the Stock column as the Sum_range argument. Use the values in the Item Description column as the Criteria_range1 argument and use the criteria *shoesto find all items that end in the word shoes. Use the values in the Color column as the Criteria_range2 argument and use the criteria blue. Click cell B3.
On the Formulas tab, in the Function Library group, click the Math
