Read these examples of how to use these different spreadsheet functions in the workplace. The learning objectives review how to use each type of function; the tables describe the syntax for each command type in detail.
The AVERAGEIF function performs the same mathematical calculation as the AVERAGE function. However, like the COUNTIF function, it allows you to define criteria to choose cells in a range 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 defines the arguments in the AVERAGEIF and SUMIF functions.
Argument | Definition |
---|---|
Range | The cells evaluated using the criteria argument. |
Criteria | We use criteria to evaluate the range of cells in the Range argument. We define this argument with a cell location, formula, number, text, or logical test. Text and logical tests must be enclosed in quotation marks. |
[Average_range] or [Sum_range] | The range of cells 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. |
We will use the AVERAGEIF function in the Portfolio Summary
worksheet to calculate the average length of time for
each investment type held.
These steps explain how to add this function to the worksheet:
Figure 4 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 that define the Range and Average_range arguments. The function will evaluate the cells in the range A4:A18 using the value 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 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
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.