Print this chapterPrint this chapter

Statistical IF Functions

The AVERAGEIFS Function

The AVERAGEIFS function is similar to the COUNTIFS function in that multiple sets of criteria can be defined instead of one. However, the arguments for the AVERAGEIFS function are slightly different from those for the COUNTIFS function. Table 2 Arguments for the AVERAGEIFS and SUMIFS Functions provides definitions for the arguments of the AVERAGEIFS and the SUMIFS functions.

Argument Definition
Average_range or Sum_range Range of cells that contain values to be averaged when using the AVERAGEIFS function or summed when using the SUMIFS function. Note that the AVERAGEIFS or SUMIFS functions will only select values from the range used to define this argument if all criteria pairs are true.
Criteria_range1 Range of cells that will be evaluated based on the Criteria1 argument to determine which cells in the Average_range or Sum_range arguments will be included in the output of the function.
Criteria1 Criteria that will be used to evaluate the range of cells used to define the Criteria_range1 argument. This argument can be defined with a cell location, formula, number, text, or logical test. Note that text and logical tests must be enclosed in quotation marks.
Criteria_range2 Optional argument that defines a second range of cells that will be evaluated based on the Criteria2 argument to determine which cells in the Average_range or Sum_range arguments will be included in the output of the function. Additional Criteria_rangeN arguments can be defined as needed.
Criteria2 Criteria that will be used to evaluate the range of cells used to define the Criteria_range2 argument. This argument can be defined with a cell location, formula, number, text, or logical test. Note that text and logical tests must be enclosed in quotation marks. Additional CriteriaN arguments can be defined as needed.

Table 2 Arguments for the AVERAGEIFS and SUMIFS Functions


The AVERAGEIFS function will be used to calculate the average months of ownership for poor performing investments in the portfolio. The following steps explain how to add this function to the Portfolio Summary worksheet:

  1. Click cell C11 on the Portfolio Summary worksheet.
  2. Click the Formulas tab on the Ribbon.
  3. Click the More Functions button in the Function Library group of commands.
  4. Place the mouse pointer over the Statistical option from the drop-down list (see Figure 1 Selecting the COUNTIF Function from the Function Library).
  5. Click the AVERAGEIFS function near the top of the list of functions. This will open the Function Arguments dialog box.
  6. Click the Collapse Dialog button next to the Average_range argument on the Function Arguments dialog box (see Figure 11 Completed Arguments for the AVERAGEIFS Function).
  7. Click the Investment Detail worksheet tab.
  8. Highlight the range Q4:Q18 on the Investment Detail worksheet and press the ENTER key on your keyboard. Selected cells from this range will be averaged by the function based on the defined criteria in ensuing arguments.
  9. Click in the Average_range argument input box and place an absolute reference on the range Q4:Q18. This is done by typing a dollar sign ($) in front of the column letter and row number for both cells in the range.
  10. Click the Collapse Dialog button next to the Criteria_range1 argument on the Function Arguments dialog box (see Figure 11 Completed Arguments for the AVERAGEIFS Function).
  11. Click the Investment Detail worksheet tab.
  12. Highlight the range A4:A18 on the Investment Detail worksheet and press the ENTER key on your keyboard. Once the Criteria_range1 argument is defined, the Criteria1 argument will appear in the Function Arguments dialog box.
  13. Click in the Criteria_range1 argument input box and place an absolute reference on the range A4:A18. This is done by typing a dollar sign ($) in front of the column letter and row number for both cells in the range.
  14. Press the TAB key on your keyboard to advance to the Criteria1 argument and type the cell location A11. The criteria for the function will be the investment type entered into cell A11 on the Portfolio Summary worksheet. You will notice that as you define this argument, the Criteria_range2 argument will appear on the Function Arguments dialog box.
  15. Click the Collapse Dialog button next to the Criteria_range2 argument on the Function Arguments dialog box (see Figure 11 Completed Arguments for the AVERAGEIFS Function).
  16. Click the Investment Detail worksheet tab.
  17. Highlight the range W4:W18 on the Investment Detail worksheet and the press the ENTER key on your keyboard.
  18. Click in the Criteria_range2 argument input box and place an absolute reference on the range W4:W18. This is done by typing a dollar sign ($) in front of the column letter and row number for both cells in the range.
  19. Press the TAB key on your keyboard to advance to the Criteria2 argument.
  20. Type an open quotation mark followed by the logical test <−1% and then type a closing quotation mark (“<−1%”). The second criterion for this function is a logical test that will identify cell locations where the value is less than −1%. For the purposes of this exercise, a poor performing investment is one that is below the target growth rate by more than 1%.
  21. Click the OK button at the bottom of the Function Arguments dialog box.

  22. Copy the function in cell C11 and paste it into the range C12:C14 using the Paste Formulas command.

    Figure 11 Completed Arguments for the AVERAGEIFS Function shows the defined arguments for the AVERAGEIFS function. Notice that two sets of criteria arguments are defined. Values that meet all criteria defined in the function will be selected from the range Q4:Q18.

    Completed Arguments for the AVERAGEIFS Function

    Figure 11 Completed Arguments for the AVERAGEIFS Function

  23. Enter an AVERAGEIF function in cell C15 by typing an equal sign (=) followed by the function name AVERAGEIF followed by an open parenthesis (().
  24. Click the Investment Detail worksheet tab and highlight the range W4:W18 to define the Range argument. Type a comma.
  25. Type the following to define the criteria argument: “<–1%”. Type a comma.
  26. Highlight the range Q4:Q18 to define the [Average_range] argument. Then type a closing parenthesis ()) and press the ENTER key on your keyboard.


Figure 12 Results of the AVERAGEIFS Function shows the results of the AVERAGEIFS function in the Portfolio Summary worksheet. The function shows the average months of ownership for the poor performing investments by investment type.

 

Results of the AVERAGEIFS Function

Figure 12 Results of the AVERAGEIFS Function

Skill Refresher: AVERAGEIFS Function

  1. Type an equal sign: =
  2. Type the function name AVERAGEIFS followed by an open parenthesis: (
  3. Define the Average_range argument with a range of cells that contain values to be averaged.
  4. Type a comma.
  5. Define the Criteria_range1 argument with a range of cells that will be evaluated based on the criteria used to define the Criteria1 argument.
  6. Type a comma.
  7. Define the Criteria1 argument with a cell location, number, text, or logical test. Text and logical tests must be enclosed in quotation marks.
  8. Repeat steps 4, 5, 6, and 7 to define as many pairs of criteria arguments as needed.
  9. Type a closing parenthesis: )
  10. Press the ENTER key on your keyboard.