AVERAGEIF Function (OpenOffice Calc)

• Calculates the average of values that meet one condition
• Supports numeric, text, date, and logical criteria
• Allows comparison operators (>, <, >=, <>, etc.)
• Supports wildcard matching (* and ?)
• Works across sheets
• Ignores text and empty cells in the average range

AVERAGEIF is ideal for conditional statistical analysis.

AVERAGEIF(range; criteria; average_range)

Arguments:

• range — The cells to evaluate
• criteria — The condition to apply
• average_range — The cells to average when the condition is met

AVERAGEIF supports:

• * — matches any sequence of characters
• ? — matches any single character
• ~ — escapes literal * or ?

Examples:

• "A*" matches any text starting with A
• "*Service*" matches text containing “Service”
• "???" matches any 3‑character string
• "~*" matches a literal asterisk

Average values greater than a number

=AVERAGEIF(A1:A10; ">50"; B1:B10)

Average values equal to text

=AVERAGEIF(A1:A10; "North"; B1:B10)

Average values matching a wildcard

=AVERAGEIF(A1:A10; "Jan*"; B1:B10)

Average values in the same range

=AVERAGEIF(A1:A10; ">0")

Averages only positive numbers in A1:A10.

Average values across sheets

=AVERAGEIF(Sheet1.A1:A100; ">100"; Sheet1.B1:B100)

Average using cell‑based criteria

=AVERAGEIF(A1:A10; C1; B1:B10)

If C1 contains ">=500", the formula uses that condition.

Average values not equal to something

=AVERAGEIF(A1:A10; "<>North"; B1:B10)

Average values with partial text match

=AVERAGEIF(A1:A100; "*Service*"; B1:B100)

Average values using numeric thresholds

=AVERAGEIF(A1:A100; "<=100"; B1:B100)

Average values within a date range (workaround)

OpenOffice Calc does not support BETWEEN directly:

=AVERAGEIFS(B1:B100; A1:A100; ">=" & DATE(2025;1;1); A1:A100; "<=" & DATE(2025;12;31))

Average excluding zeros

=AVERAGEIF(A1:A100; "<>0")

Average with error‑handling

To ignore errors:

=AVERAGE(IF(ISERROR(A1:A10); ""; A1:A10))

Confirm with Ctrl+Shift+Enter.

• Use AVERAGEIF for single‑criteria conditional averaging
• Use AVERAGEIFS for multi‑criteria analysis
• Use SUBTOTAL for filtered data
• Convert imported text numbers to real numbers
• Avoid mixing text and numbers in the same column
• Use named ranges for cleaner formulas