AVERAGEIF Function (OpenOffice Calc)
The AVERAGEIF function in OpenOffice Calc calculates the average of values that meet a single condition. Learn syntax, examples, wildcard rules, common errors, and best practices.
Verification status: Consult the documentation and examples below. Native application testing has not been documented for this guide. How formula examples are checked
The AVERAGEIF function in OpenOffice Calc calculates the average of values that meet a single condition. It supports numeric comparisons, text matching, wildcard patterns, and logical expressions. When you need to compute averages for filtered categories, thresholds, or text patterns, AVERAGEIF is one of the most useful conditional functions in OpenOffice Calc.
What the AVERAGEIF Function Does
- 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.
Syntax
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
average_range is omitted, OpenOffice Calc averages the range itself.
Wildcard Rules
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
Basic Examples
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.
Advanced Examples
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.
Common Errors and Fixes
AVERAGEIF returns 0 unexpectedly
Possible causes:
- No values meet the condition
- All matching values are text
- Text numbers not converted to numeric
- Hidden spaces in cells
AVERAGEIF returns Err:502 (Invalid argument)
Occurs when:
- A malformed range is used
- A text criterion is missing quotes
average_rangeandrangediffer in size
AVERAGEIF includes values you expected it to ignore
AVERAGEIF includes:
- Dates
- Times
- Numeric results of formulas
AVERAGEIF excludes values you expected it to include
AVERAGEIF ignores:
- Text numbers (
"123") - Empty cells
- Logical values (TRUE/FALSE)
- Errors
Err:508 — Missing parenthesis
Usually caused by:
- Missing
) - Using commas instead of semicolons
Best Practices
- 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
=AVERAGEIF(A1:A100; "<>0")