Excel AVERAGEIF Function
This tutorial explains how to use the Excel AVERAGEIF function.
The Excel AVERAGEIF function calculate the average of all the cells in a range that meet single criteria.
There are other AVERAGES functions that you can use to calculate the average of a group of numbers (AVERAGE, AVERAGEA, and AVERAGEIFS).
- Use the Excel AVERAGE function if you want to calculate the average of a group of numbers.
- Use the Excel AVERAGEIFS function if you want to calculate the average of only the values that meet the multiple criteria.
- Use the Excel AVERAGEA function if you want to include logical values and text representations of numbers in a reference as part of the calculation.
=AVERAGEIF (range, criteria, [average_range])
- range: A range of cells.
- criteria: The criteria that defines which cells are averaged.
- average_range: [Optional]. A range of cells to average.
range can numbers, ranges, names, arrays, or cell references.
criteria can be a number, cell reference, expression, or text.
If average_range is omitted, range is a range of cells to average.
AVERAGEIF ignores the empty cells in range and average_range.
AVERAGEIF ignores the cells in range that contain TRUE or FALSE.
AVERAGEIF returns the error value (#DIV/0!) if there is no cells in the range meet the criteria.
AVERAGEIF allows the wildcard characters (*) and (?) in criteria.
average_range does not have to be the same size and shape as range.
Here is our sample data.
|1||Date||Code||Item Name||Quantity||Price ($)||Amount ($)||Employee|
|2||5/10/2017||Item01||Sony Desktop PC||2||225||450||James|
|3||5/10/2017||Item02||Dell Desktop PC||2||750||1500||James|
|4||5/10/2017||Item01||Sony Desktop PC||1||235||235||James|
|5||5/10/2017||Item03||HP Desktop PC||3||785||2355||Roland|
|6||5/11/2017||Item02||Dell Desktop PC||3||1050||3150||Roland|
|7||5/12/2017||Item01||Sony Desktop PC||1||275||275||James|
|8||5/12/2017||Item03||HP Desktop PC||4||290||1160||Roland|
In Example 1, we want to get the average of cells that sales amount greater than 1000 in the range F2:F8 . As you can see, there only two arguments in the formula and average_range is omitted. Thus, AVERAGE computes the average of cells in range. The output of the AVERAGE formula is 2041.25.
There is an only argument (F2:F8) in the AVERAGE formula above. AVERAGE returns the average of the numbers in the range F2:F8. The result is 741.6666667.
If we want to calculate the average of sales amount by an employee, we can do as the following formula.
In Example 2, the AVERAGEIF function calculates the average of the sales amount by Roland in column F . The AVERAGEIF returns 2221.66666666667.
Let’s another example below.
If we want to calculate the average of the sales amount by employee name that starts with J, we can use the formula below. Here we need to use the wildcard character (*).
In Example 3, The AVERAGEIF function calculates the average of the sales amount by employee name that starts with J. As you can see, we use * after J to match any employee names that starts with J. The result is 615.
Lest’s another example that uses (?) in criteria.
In Example 4, the AVERAGEIF calculates the average of the sales amount by all employees that name starts with Rola and ends with any two characters. The result is 2221.66666666667.
In this tutorial, you learned how to use the Excel AVERAGEIF function. We use the Excel AVERAGEIF function to calculate the average of cells in a range that meet single criteria.
Here is a full list of Microsoft Excel functions.