The Wear-Ever Shoes company maintains inventory data and customer survey results in your workbook. You use *Lookup & Reference*, *Database*, 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.)*

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.

- 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.
- Click the
**Inventory** sheet tab.
- Select cells
**A3:I39**, click the **Name** box, type **Inventory** as the range name, and press **Enter**.
- Select cell
**L5** and type **WE006**.
- Create a nested function with
*INDEX* and *MATCH* to display inventory for a product.
- Select cell
**L6**.
- 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**.
- For the
*Array* argument, press **F3** (**FN**+**F3**) and select **Inventory**.
- 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.
- Click cell
**L5** for the *Lookup_value* argument.
- Click the
**Lookup_array** box and select cells **A3:A39**. This *MATCH* function finds the row that matches cell L5 in column A.
- Click the
**Match_type** argument and type **0**.
- Click
**INDEX** in the *Formula* bar. (Click **OK** if the argument list opens.)
- Click the
**Column_num** argument, click the **Name** box arrow, and choose **MATCH** (Figure 6-92).**Figure 6-92** **MATCH** **is nested twice**
- Type
**quantity** in the *Lookup_value* box.
- 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.
- Click the
**Match_type** box and type **0**. The formula is **=INDEX(Inventory,MATCH(L5,A3:A39,0),MATCH(“quantity”,A3:I3,0))**.
- Click
**OK**. The result is 2.
- Click cell
**L5**, type **WE015**, and press **Enter**. The quantity is updated.

- Use
*SUMIFS* to calculate total pairs in stock by specific criteria.
- Select cell
**M13**.
- Click the
**Math & Trig** button [*Formulas* tab, *Function Library* group] and choose **SUMIFS**.
- Select cells
**E4:E39** for the *Sum_range* argument and press **F4** (**FN**+**F4**) to make the references absolute.
- Click the
**Criteria_range1** box, select cells **C4:C39**, the “Color” field, and press **F4** (**FN**+**F4**).
- Click the
**Criteria1** box and select cell **K13**. Leave this as a relative reference.
- Click the
**Criteria_range2** box, select cells **D4:D39**, and make the references absolute.
- Click the
**Criteria2** box and select cell **L13**. The criteria specifies the number of black pairs, size 8 (Figure 6-93).**Figure 6-93** **SUMIFS** **to calculate number by color and size**
- Click
**OK**. The result is 7.
- Copy the formula in cell
**M13** to cells **M14:M21**.

- Click the
**Satisfaction Survey** worksheet tab and review the data.
- Select cells
**A4:H40** and name the range as **Survey**. Note that the “Comfort” field is the fifth column and that the other attributes follow in the sixth, seventh, and eighth columns.
- Use
*DAVERAGE* to summarize customer survey data.
- Click the
**Criteria** sheet tab.
- Select cell
**B2** and type **rug***, criteria for the Rugged Hiking Boots.
- Click the
**Average Ratings** worksheet tab and select cell **C5**.
- Click the
**Insert Function** button [*Formulas* tab, *Function Library* group].
- Choose
**Database** in the *Or select a category* list.
- Select
**DAVERAGE** and click **OK** to calculate an average comfort rating for the boots.
- Press
**F3** (**FN**+**F3**), choose **Survey** for the *Database* argument, and click **OK**.
- Click the
**Field** box and select cell **C4**.
- Click the
**Criteria** box, select the **Criteria** sheet tab, select cells **B1:B2**, and make the references absolute (Figure 6-94).**Figure 6-94** **DAVERAGE** **for comfort rating**
- Click
**OK**. The result is 7.75.
- Copy the formula in cell
**C5** to cells **D5:F5**.

- Use
*DAVERAGE* to summarize survey data.
- Select the
**Criteria** sheet tab and select cell **B5**. Type the criteria as shown here for the shoe styles.The table lists the criteria to be entered on the Criteria sheet.**CellCriteriaB5com*B8laz*B11ser*B14gli***
- Click the
**Average Ratings** sheet tab and select cell **C6**.
- Click the
**Recently Used** button [*Formulas* tab, *Function Library* group] and select **DAVERAGE**.
- Press
**F3** (**FN**+**F3**) and choose **Survey** for the *Database* argument.
- Click the
**Field** argument box and select cell **C4**.
- Click the
**Criteria** box, select cells **B4:B5** on the **Criteria** sheet, and press **F4** (**FN**+**F4**).
- Click
**OK**. The result is 7.5.
- Copy the formula in cell
**C6** to cells **D6:F6**.

- Build
*DAVERAGE* functions for the remaining shoe styles on the **Average Ratings** sheet.
- Select cells
**G5:G9** on the **Average Ratings** sheet, click the **AutoSum** arrow [*Home* tab, *Editing* group], and choose **Average**.
- Create an
*IFS* function.*Note: If your version of Excel does not include the IFS function, build the following nested IF function* **=IF(G5>=9,$J$5,IF(G5>=8,$J$6,IF(G5>=5,$J$7,$J$8)))** *to show the ratings*.
- Select cell
**H5**, click the **Logical** button [*Formulas* tab, *Function Library* group], and choose **IFS**.
- Click the
**Logical_test1** argument, select cell **G5**, and type **>=9**.
- Click the
**Value_if_true1** box, click cell **J5**, and press **F4** (**FN**+**F4**) to make the reference absolute.
- Click the
**Logical_test2** box, click cell **G5**, and type **>=8**.
- Click the
**Value_if_true2** box, click cell **J6**, and press **F4** (**FN**+**F4**).
- Click the
**Logical_test3** box, click cell **G5**, and type **>=5**.
- Click the down scroll arrow to reveal the
*Value_if_true3* box, click cell **J7**, and press **F4** (**FN**+**F4**).
- Click the down scroll arrow to reveal the
*Logical_test4* box, click cell **G5**, and type **<5**.
- Click the down scroll arrow to reveal the
*Value_if_true4* box, click cell **J8**, and press **F4** (**FN**+**F4**) (Figure 6-95). The complete formula is:**=IFS(G5>=9,$J$5,G5>=8,$J$6,G5>=5,$J$7,G5<5,$J$8)****Figure 6-95** **IFS** **function with multiple logical tests**
- Click
**OK** and copy the formula to cells **H6:H9**.
- Format column
**H** to be **13.57 (100 pixels)** wide.

- Calculate depreciation for an asset using a
*Financial* function.
- Click the
**Depreciation** sheet tab and select cell **C11**. Depreciation is the decrease in the value of an asset as it ages. The **DB** function calculates the loss in value over a specified period of time at a fixed rate.
- Click the
**Financial** button [*Formulas* tab, *Function Library* group] and choose **DB**.
- Select cell
**C6** for the *Cost* argument, and press **F4** (**FN**+**F4**) to make the reference absolute. This is the initial cost of the equipment.
- Click the
**Salvage** box, select cell **C7**, and press **F4** (**FN**+**F4**). This is the expected value of the equipment at the end of its life.
- Click the
**Life** box, select cell **C8**, and press **F4** (**FN**+**F4**). This is how long the equipment is expected to last.
- Click the
**Period** box and select cell **B11**. The first formula calculates depreciation for the first year (Figure 6-96).**Figure 6-96** **DB** **function to calculate asset depreciation**
- Click
**OK**. The first year depreciation is $39,900.00.
- Copy the formula in cell
**C11** to cells **C12:C18**. Each year’s depreciation is less than the previous year’s.
- Select cell
**C19** and use **AutoSum**. The total depreciation plus the salvage value is approximately equal to the original cost. It is not exact due to rounding.

- Use
*CONCAT* to build an email address. (If your version of Excel does not include *CONCAT*, use *CONCATENATE*.)
- Right-click any worksheet tab, choose
**Unhide**, select **E-Mail**, and click **OK**.
- Select cell
**C5**, type **=con**, and press **Tab**. The *text1* argument is first.
- Select cell
**A5** and type a comma (,) to move to the *text2* argument.
- Select cell
**B5** and type a comma (,) to move to the *text3* argument.
- Type
**“@weshoes.org”** including the quotation marks (Figure 6-97).**Figure 6-97** **CONCAT** **references and typed data**
- Type the closing parenthesis (
**)**) and press **Enter**.
- Copy the formula in cell
**C5** to cells **C6:C8**.

- Save and close the workbook (Figure 6-98).