Statistical IF Functions
Read these examples of how to use these different functions in business. The learning objectives review each type of function's use, and the tables describe the syntax for each type of command in detail.
The AVERAGEIF Function
The AVERAGEIF function performs the identical mathematical calculation as the regular AVERAGE function. However, similar to the COUNTIF function, it allows you to define criteria that will select cells in a range that will be used in the function output. The AVERAGEIF function differs from the COUNTIF function in that it allows you to define two cell ranges instead of one. The first range pertains to the criteria that will be used to select cells for the function output. The second range contains the values that will be used to calculate the arithmetic mean. Table 1 Arguments for the AVERAGEIF and SUMIF Functions provides definitions for the arguments contained in the AVERAGEIF and SUMIF functions.
Argument | Definition |
---|---|
Range | Range of cells that will be evaluated by the criteria argument. |
Criteria | Criteria that will be used to evaluate the range of cells that is used to define the Range 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. |
[Average_range] or [Sum_range] | Range of cells that will be used to calculate the average when using the AVERAGEIF function, or the sum when using the SUMIF function. This argument is enclosed in brackets because it does not always need to be defined. If this argument is omitted, the function will use the range of cells in the Range argument to calculate the output. |
Table 1 Arguments for the AVERAGEIF and SUMIF Functions
The AVERAGEIF function will be used in the Portfolio Summary worksheet to calculate the average length of time that investments for each investment type are held. The following steps explain how to add this function to the worksheet:
- Click cell C4 on the Portfolio Summary worksheet.
- Click the Formulas tab on the Ribbon.
- Click the More Functions button in the Function Library group of commands.
- Place the mouse pointer over the Statistical option from the drop-down list (see Figure 1 Selecting the COUNTIF Function from the Function Library).
- Click the AVERAGEIF function, which will be near the top of the list of functions. This opens the Function Arguments dialog box.
- Click the Collapse Dialog button next to the Range argument on the Function Arguments dialog box (see Figure 4 Defined Arguments for the AVERAGEIF Function).
- Click the Investment Detail worksheet tab.
- Highlight the range A4:A18 on the Investment Detail worksheet and press the ENTER key on your keyboard.
- Click in the Range 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.
- Press the TAB key on your keyboard to advance to the Criteria argument and type the cell location A4. The criteria for the function will be the investment type entered into cell A4 on the Portfolio Summary worksheet.
- Click the Collapse Dialog button next to the Average_range argument on the Function Arguments dialog box (see Figure 4 Defined Arguments for the AVERAGEIF Function).
- Click the Investment Detail worksheet tab.
- Highlight the range Q4:Q18 on the Investment Detail worksheet and press the ENTER key on your keyboard.
- 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.
- Click the OK button at the bottom of the Function Arguments dialog box.
- Copy the function in cell C4 and paste it into the range C5:C7 using the Paste Formulas option.
- Type an equal sign (=) in cell C8.
- Click the Investment Detail worksheet tab. Then click cell Q19 and press the ENTER key on your keyboard.
Figure 4 Defined Arguments for the AVERAGEIF Function shows the Function Arguments dialog box for the AVERAGEIF function that will be input into cell C4. Notice that absolute references are placed on the cell locations used
to define the Range and Average_range arguments. The function will evaluate the cells in the range A4:A18 using the value that exists in cell A4 on the Portfolio Summary worksheet. When a cell
in the range A4:A18 meets the criteria, the function will pull the cell location in the same row from the range Q4:Q18 and include it in the average calculation.
Figure 4 Defined Arguments for the AVERAGEIF Function
Figure 5 AVERAGEIF Function Output on the Portfolio Summary Worksheet shows the output of the AVERAGEIF function in the Average Months Owned column on the Portfolio Summary worksheet. The function calculates
the average months owned in Column Q on the Investment Detail worksheet where the investment type is equal to the description entered in the range A4:A7 on the Portfolio Summary worksheet.
Figure 5 AVERAGEIF Function Output on the Portfolio Summary Worksheet
Integrity Check
Matching Row Numbers for the Range and Average_range (or Sum_range) Arguments
When defining the Average_range argument for the AVERAGEIF function or the Sum_range argument for the SUMIF function, it is good practice to make sure the row numbers match the row numbers used in the Range argument. For example, if the Range argument is defined with the range A4:A12, the range used to define the Average_range or Sum_range argument should begin with Row 4 and end with Row 12. If the row numbers in these two arguments do not match, Excel will include the values only in the rows used to define the Range argument. For example, if the Range argument is defined with the range A4:A12 and the Average_range (or Sum_range) argument is defined with the range D4:D20, only the values in cells D4:D12 will be included in the function output.
Skill Refresher: AVERAGEIF Function
- Type an equal sign: =
- Type the function name AVERAGEIF followed by an open parenthesis: (
- Define the range argument with a range of cells that will be evaluated using the criteria argument.
- Type a comma.
- Define the criteria argument with a cell location, number, text, or logical test. Text and logical tests must be enclosed in quotation marks.
- Type a comma.
- Define the Average_range argument with a range that contains values to be averaged. Excel will use the range argument to calculate the average if this argument is omitted.
- Type a closing parenthesis: )
- Press the ENTER key on your keyboard.