Excel Guided Project 6-3

The Wear-Ever Shoes company maintains inventory data and customer survey results in your workbook. You use Lookup & ReferenceDatabase, and Logical functions to complete the data. You also use a Financial function to calculate depreciation and a Text function to enter email addresses.

[Student Learning Outcomes 6.1, 6.2, 6.3, 6.5, 6.6, 6.7]

File Needed: WearEverShoes-06.xlsx (Available from the Start File link.)

Save your time - order a paper!

Get your paper written from scratch within the tight deadline. Our service is a reliable solution to all your troubles. Place an order on any task and we will take care of it. You won’t have to worry about the quality and deadlines

Order Paper Now

Completed Project File Name: [your name]-WearEverShoes-06.xlsx

Skills Covered in This Project

  • Nest INDEX and MATCH functions.
  • Use SUMIFS from the Math & Trig category.
  • Use DAVERAGE.
  • Create an IFS formula.
  • Use a Text function to concatenate text strings.
  • Calculate depreciation with the DB function.
  1. Open the WearEverShoes-06 start file. 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. Click the Inventory sheet tab.
  3. Select cells A3:I39, click the Name box, type Inventory as the range name, and press Enter.
  4. Select cell L5 and type WE006.
  5. Create a nested function with INDEX and MATCH to display inventory for a product.
    1. Select cell L6.
    2. Click the Lookup & Reference button [Formulas tab, Function Library group] and choose INDEX. Select the first argument list array, row_num, column_num and click OK.
    3. For the Array argument, press F3 (FN+F3) and select Inventory.
    4. Click the Row_num box and click the Name box arrow. Choose MATCH in the list or choose More Functions to find and select MATCH. The INDEX function uses this MATCH statement to find the row.
    5. Click cell L5 for the Lookup_value argument.
    6. Click the Lookup_array box and select cells A3:A39. This MATCH function finds the row that matches cell L5 in column A.
    7. Click the Match_type argument and type 0.
    8. Click INDEX in the Formula bar. (Click OK if the argument list opens.)
    9. Click the Column_num argument, click the Name box arrow, and choose MATCH (Figure 6-92).Both the row_num and col_num arguments are MATCH functions.Figure 6-92 MATCH is nested twice
    10. Type quantity in the Lookup_value box.
    11. Click the Lookup_array box and select cells A3:I3. This MATCH function finds the cell in the “Quantity” column after the row is located by the first MATCH function.
    12. Click the Match_type box and type 0. The formula is =INDEX(Inventory,MATCH(L5,A3:A3

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *