Documentation/Calc Functions/AVERAGEIF

Function name:
AVERAGEIF

Category:
Statistical Analysis

Summary:
Returns the arithmetic mean of all cells in a range that satisfy a given condition. The AVERAGEIF function sums up all the results that match the logical test and divides this sum by the quantity of selected values.

Syntax:
AVERAGEIF(Range; Criterion [; Average_Range ])

Returns:
Returns a real value which is the arithmetic mean of all numbers in the range Range that qualify the criteria "Criterion".

Arguments:
Range is an array, a name of named range or a label of a column or a row containing numbers for averaging or numbers or text for the condition.

Criterion is a condition in the form of expression or a cell reference with expression that defines what cells should be used to calculate the mean. The expression can contain text, numbers, regular expressions (if enabled in calculation options) or wildcards (if enabled in calculation options).

Average_Range is a range of values for calculating the mean.


 * If the Average_Range is not specified, Range is used for both, the calculation of the mean and the search according to the condition. If Average_Range is specified, the Range is used only for the condition test, while Average_Range is used for the average calculation.
 * If a cell in a range of values for calculating the mean is empty or contains text, function AVERAGEIF ignores this cell.
 * If the whole range is empty, contains only text or all values of the range do not satisfy the condition (or any combination of those), the function returns the division(#DIV/0!) error.
 * If no cell qualifies the Criterion then the function returns a division(#DIV/0!) error.
 * If the Criterion is not a valid one like passing just ">" sign or "!" exclamation mark then the function returns a division(#DIV/0!) error since no cell qualifies for the operation.

Additional details:

 * The search supports wildcards or regular expressions. With regular expressions enabled, you can enter "all.*", for example to find the first location of "all" followed by any characters. If you want to search for a text that is also a regular expression, you must either precede every character with a "\" character, or enclose the text into \Q...\E. You can switch the automatic evaluation of wildcards or regular expression on and off in.

Examples:
Consider the following table

In all examples below, ranges for calculation contain the row #6, which is ignored because it contains text.

Using wildcard characters
The wildcard characters question mark (?), asterisk(*), or tilde (~) can be used in criteria. A question mark (?) matches any one character and an asterisk (*) matches zero or more characters of any kind.

Reference to a cell as a criterion
If you need to change a criterion easily, you may want to specify it in a separate cell and use a reference to this cell in the condition of AVERAGEIF function.

Related LibreOffice functions:
AVERAGEIFS

AVEDEV

AVERAGE

AVERAGEA

COUNTIFS

MAXIFS

MINIFS

SUMIFS

ODF standard:
Section 6.18.5, part 2

Equivalent Excel functions:
AVERAGEIF