0 ), match (“ quantity ”, a3 B u s i n e s s F i n a n c e

0 ), match (“ quantity ”, a3 B u s i n e s s F i n a n c e

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.

  1. Create a nested function with INDEX and MATCH to display inventory for a product.
  2. Use SUMIFS to calculate total pairs in stock by specific criteria.

    1. Both the row_num and col_num arguments are MATCH functions.
      Figure 6-92 MATCH is nested twice

    2. The sum and criteria ranges must have the same dimension.
      Figure 6-93 SUMIFS to calculate number by color and size
  3. Use DAVERAGE to summarize customer survey data.

    1. DAVERAGE ignores values that do not match the criteria.
      Figure 6-94 DAVERAGE for comfort rating
  4. Use DAVERAGE to summarize survey data.
  5. 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.

    1. The Logical_test1 argument is scrolled out of view
      Figure 6-95 IFS function with multiple logical tests
  6. Calculate depreciation for an asset using a Financial function.

    1. DB stands for declining balance depreciation.
      Figure 6-96 DB function to calculate asset depreciation

    2. CONCAT was CONCATENATE in earlier versions of Excel.
      Figure 6-97 CONCAT references and typed data
  7. Use CONCAT to build an email address. (If your version of Excel does not include CONCAT, use CONCATENATE.)