The AVERAGEIF function
The average, but only over the rows that meet a criterion: the average North basket, a class's average grade.
Syntax
=AVERAGEIF(range, criteria, [average_range])
In a French Excel: =MOYENNE.SI(plage; critère; [plage_moyenne]).
Arguments
- range: the column tested.
- criteria: as for SUMIF.
- average_range: the column to average; omitted, it is the tested range.
An example
=AVERAGEIF(A:A,"North",C:C)
The average of column C over the North rows; empty cells do not count.
Pitfalls
- No row matches: #DIV/0!.
- Numbers stored as text do not count.
Going further
- SUMIF.
- Automatic pivot table: every average per group.
Frequently asked questions
- Do empty cells count as zero?
- No: AVERAGEIF ignores them. Only cells containing a number enter the average.