To average a range of values that meet a given criteria or condition, you use the AVERAGEIF function.
Syntax
AVERAGEIF(range,criteria,average_range)
Range : The group of cells to be evaluated for criteria.
Criteria: is the criteria in the form of a number, expression, cell reference, or text that defines which cells are averaged.
Average_range: is the actual set of cells to be averaged. If omitted, range is used.
Example:
In the example below we want to average the total sales for a particular salesman (Jim). So our range is B2:B13 and our criteria is “Jim”. The range to be averaged is C2:C13. The formula will be:
=AVERAGEIF(B2:B13;”Jim”;C2:C13)
The result is 1005, i.e. (2300 + 1100 + 200 + 420) / 4
Notes:
1. Cells in range that contain TRUE or FALSE are ignored.
2. If a cell in average_range is an empty cell, AVERAGEIF ignores it.
3. If range is a blank or text value, AVERAGEIF returns the #DIV0! error value.
4. If a cell in criteria is empty, AVERAGEIF treats it as a 0 value.
5. If no cells in the range meet the criteria, AVERAGEIF returns the #DIV/0! error value.
6. You can use the wildcard characters, question mark (?) and asterisk (*), in criteria. In the previous example if you use “J*” in criteria then both “Jim” and “John” will be included in the average.
Related posts: