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**

**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
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.**WearEverShoes-06** - 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.

- Select cell
- 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**.

- Select cell
- 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**.

- Click the
- 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**.

- Select the
- 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.

- Select cell
- 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.

- Click the
- 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**.

- Right-click any worksheet tab, choose
- Save and close the workbook (Figure 6-98).