Need an Excel expert. Having some trouble with this one. Instructions and template are attached.

A Skills Approach: Excel 2013 Chapter 6: Exploring Advanced Functions skill review 6.2 In this project, you will create a worksheet to analyze inventory and financial data for a sporting goods store. Skills needed to complete this project: Step 1 Download start file Calculating Totals with SUMPRODUCT Finding the Middle Value with MEDIAN Finding the Most Common Value(s) with MODE.SNGL and MODE.MULT Using SUMIF and SUMIFS Using AVERAGEIF and AVERAGEIFS Using COUNTIF and COUNTIFS Using Database Functions Finding Data with MATCH and INDEX Managing Errors with the IFERROR Function Analyzing Complex Formulas Using Evaluate Formula Calculating Future Value with the FV Function Calculating the Number of Payments with NPER Rounding with Functions Using NPV to Calculate Present Value when Payments Are Variable Creating a Depreciation Schedule 1. Open the start file EX2013-SkillReview-6-2. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor, and save it. 2. If the workbook opens in Protected View, click the Enable Editing button in the Message Bar at the top of the workbook. 3. Start with 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. a. Click cell B2. b. Type =SUMPRODUCT( c. Click the Inventory sheet tab, and then click and drag to select cells G4:G26. d. Type , and then click and drag to select cells H4:H26. e. Type ) and press Enter. f. The formula should look like this: =SUMPRODUCT(Inventory!G4:G26,Inventory!H4:H26) 4. Enter a formula to calculate the average selling price. a. Click cell D2. b. Type =AVERAGE( c. Click the Inventory sheet tab, and then click and drag to select cells G4:G26. d. Press Enter. The formula should look like this: =AVERAGE(Inventory!G4:G26) 1 | Page skill review 6.2 Last Updated 4/30/15 A Skills Approach: Excel 2013 Chapter 6: Exploring Advanced Functions 5. Enter a formula to find the middle selling price. a. Click cell E2. b. Type =MEDIAN( c. Click the Inventory sheet tab, and then click and drag to select cells G4:G26. d. Press Enter. The formula should look like this: =MEDIAN(Inventory!G4:G26) 6. Enter a formula to find the most common selling price. a. Click cell F2. b. Type =MODE.SNGL( c. Click the Inventory sheet tab, and then click and drag to select cells G4:G26. d. Press Enter. The formula should look like this: =MODE.SNGL(Inventory!G4:G26) 7. 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. a. Select cells G2:G5. b. Type =MODE.MULT( c. Click the Inventory sheet tab, and then click and drag to select cells G4:G26. d. Press Ctrl+Shift+Enter. The formula should look like this: {=MODE.MULT(Inventory!G4:G26)} 8. Use the SUMIFS function to calculate the total number of red shoes in inventory. Use the values in the Cost column as the Sum_range argument. Use the values in the Item Description column as the Criteria_range1 argument and use the criteria *shoes to 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 red. a. Click cell B3. b. On the Formulas tab, in the Function Library group, click the Math & Trig button and select SUMIFS. c. In the Function Arguments dialog, verify that the cursor is in the Sum_range argument box. Click the Inventory sheet tab, and click and drag to select cells H4:H26. d. In the Function Arguments dialog, press Tab to move to the Criteria_range1 argument box. e. Click the Inventory sheet tab, and click and drag to select cells A4:A26. f. In the Function Arguments dialog, press Tab to move to the Criteria1 argument box. g. Type "*shoes" to find any text string that ends with the word shoes. h. Press Tab. Verify that the cursor is in the Criteria_range2 argument box. 2 | Page i. Click the Inventory sheet tab, and click and drag to select cells C4:C26. j. In the Function Arguments dialog, press Tab to move to the Criteria2 argument box. skill review 6.2 Last Updated 4/30/15 A Skills Approach: Excel 2013 Chapter 6: Exploring Advanced Functions k. Type: "red" l. Click OK. m. The final formula should look like this: =SUMIFS(Inventory!H4:H26,Inventory!A4:A26,"*shoes",Inventory!C4:C26,"red") 9. Use the AVERAGEIFS function to find the average selling price of red shoes in inventory. Use the values in the Selling Price column as the Average_range argument. Use the values in the Item Description column as the Criteria_range1 argument and use the criteria *shoes to 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 red. a. Click cell B4. b. On the Formulas tab, in the Function Library group, click the More Functions button, point to Statistical, and select AVERAGEIFS. c. In the Function Arguments dialog, verify that the cursor is in the Average_range argument box. Click the Inventory sheet tab, and click and drag to select cells G4:G26. d. In the Function Arguments dialog, press Tab to move to the Criteria_range1 argument box. e. Click the Inventory sheet tab, and click and drag to select cells A4:A26. f. In the Function Arguments dialog, press Tab to move to the Criteria1 argument box. g. Type "*shoes" to find any text string that ends with the word shoes. h. Press Tab. Verify that the cursor is in the Criteria_range2 argument box. i. Click the Inventory sheet tab, and click and drag to select cells C4:C26. j. In the Function Arguments dialog, press Tab to move to the Criteria2 argument box. k. Type: "red" l. Click OK. m. The final formula should look like this: =AVERAGEIFS(Inventory!G4:G26,Inventory!A4:A26,"*shoes",Inventory!C4:C26,"red") 10. Use the COUNTIFS function to find the number of red shoe inventory items. Use the values in the Item Description column as the Criteria_range1 argument and use the criteria *shoes to 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 red. a. Click cell B5. b. On the Formulas tab, in the Function Library group, click the More Functions button, point to Statistical, and select COUNTIFS. c. In the Function Arguments dialog, verify that the cursor is in the Criteria_range1 argument box. d. Click the Inventory sheet tab, and click and drag to select cells A4:A26. e. In the Function Arguments dialog, press Tab to move to the Criteria1 argument box. f. Type "*shoes" to find any text string that ends with the word shoes. g. Press Tab. Verify that the cursor is in the Criteria_range2 argument box. 3 | Page skill review 6.2 Last Updated 4/30/15 A Skills Approach: Excel 2013 Chapter 6: Exploring Advanced Functions h. Click the Inventory sheet tab, and click and drag to select cells C4:C26. i. In the Function Arguments dialog, press Tab to move to the Criteria2 argument box. j. Type: "red" k. Click OK. l. The final formula should look like this: =COUNTIFS(Inventory!A4:A26,"*shoes",Inventory!C4:C26,"red") 11. Now use database functions to analyze inventory data. This method gives you more flexibility in your analysis. Once you set up the formulas, you can change the criteria in the worksheet without changing the formulas. The InventoryDB named range has been created for you to use as the Database argument. It references A3:H26 on the Inventory worksheet. Notice this named range includes the label row. 12. Use the DAVERAGE database function to calculate the average selling price for all items with the word basketball in the item description. Use the wildcard character * before and after the word basketball to find all item descriptions with basketball anywhere in the text. Use the column label Selling Price as the Field argument. Remember to enclose the column label in quotation marks. a. Set up the criteria range. i. In cell D8, type: Item Description ii. In cell D9, type: *Basketball* b. Click cell B9 where you will enter the formula. c. On the Formulas tab, in the Function Library group, click the Insert Function button. d. If necessary, expand the Or select a category list, and select Database. e. Double-click DAVERAGE to open the Function Arguments dialog. f. In the Database argument box, type the range name: InventoryDB g. In the Field argument box, type the column label: "Selling Price" h. Click in the Criteria argument box and then click and drag to select cells D8:D9. i. Click OK. j. The formula should look like this: =DAVERAGE(InventoryDB,"Selling Price",D8:D9) 13. Use the DCOUNT database function to calculate the number of items in stock where the item description includes the word Football and the selling price is less than $30. Use the wildcard character * before and after the word Football to find all item descriptions with Football anywhere in the text. Remember to put quotation marks around the criteria expression. Use the column label Stock as the Field argument. a. Set up the criteria range. 4 | Page i. In cell D11, type: Item Description ii. In cell D12, type: *Football* iii. In cell E11, type: Selling Price iv. In cell E12, type: