0 ), match (“ quantity ”, a3 B u s i n e s s F i n a n c e
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.
NOTE: If group titles are not visible on your
Ribbon in Excel for Mac, click the Excel menu and select Preferences to open the Excel Preferences dialog box. Click the View button and check the Group Titles check box under In Ribbon, Show. Close the Excel Preferences dialog box.
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, type 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. Important: There is a known bug in Excel for Mac that places plus signs + instead of commas (,) between arguments when using the Formula Builder. If this is the case in your Excel for Mac version, replace the + with , and be sure that the formula string looks like the one in step 5.l.
Click cell L5 for the Lookup_value argument.
Click the Lookup_array box and type 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, and then click the Insert Function (Fx) button.
Click the Column_num argument, click the Name box arrow, and choose MATCH (Figure 6-92).
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 Done. 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.
Figure 6-92 MATCH is nested twice
Click the + button to reveal the Criteria_range1 box, select cells C4:C39, the “Color” field, and press F4 (FN+F4).
Click the + button to reveal the Criteria1 box and select cell K13. Leave this as a relative reference.
Click the + button to reveal the Criteria_range2 box, select cells D4:D39, and make the references absolute.
Click the + button to reveal 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 Done. 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].
Scroll down the list in the Formula Builder pane to find the Database functions category.
Select DAVERAGE and click Insert Function in the Formula Builder to calculate an average comfort rating for the boots.
Type Survey for the Database argument.
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 Done. 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.
Click the Average Ratings sheet tab and select cell C6.
Click the Recently Used button [ Formulas tab, Function Library group] and select DAVERAGE.
Use the named range 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 Done. 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 + button to reveal the Value_if_true1 box, click cell J5, and press F4 (FN+F4) to make the reference absolute.
Click the + button to reveal the Logical_test2 box, click cell G5, and type >= 8.
Click the + button to reveal the Value_if_true2 box, click cell J6, and press F4 (FN+F4).
Click the + button to reveal the Logical_test3 box, click cell G5, and type >= 5.
Click the + button to reveal the Value_if_true3 box, click cell J7, and press F4 (FN+F4).
Click the + button to reveal the Logical_test4 box, click cell G5, and type < 5.
Click the + button 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 Done and copy the formula to cells H6:H9.
Format column H to be 13.57 (86 pixels) wide. Calculate depreciation for an asset using a
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 Done. The first year depreciation is $39,900.00.
Figure 6-97 CONCAT references and typed data
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).
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).
Upload and save your project file.
Submit project for grading.