AVERAGE Function (OpenOffice Calc)
The AVERAGE function in OpenOffice Calc calculates the arithmetic mean of numbers in a range. Learn syntax, examples, common errors, and best practices.
Compatibility
▾| Excel | ✔ |
| Gnumeric | ✔ |
| Google_sheets | ✔ |
| Libreoffice | ✔ |
| Numbers | ✖ |
| Onlyoffice | ✔ |
| Openoffice | ✔ |
| Wps | ✔ |
| Zoho | ✔ |
What the AVERAGE Function Does ▾
- Calculates the arithmetic mean of numeric values
- Ignores text and empty cells
- Includes dates and times (because they are numeric)
- Works across sheets
- Handles large datasets efficiently
- Useful for statistical analysis and reporting
AVERAGE is essential for summarizing numeric datasets and identifying trends.
Syntax ▾
AVERAGE(number1; number2; ...)
Arguments:
- number1, number2, … — Individual values, cell references, or ranges
AVERAGE ignores text, empty cells, and logical values, but includes dates and times.
Basic Examples ▾
Average a range of numbers
=AVERAGE(A1:A10)
Average multiple ranges
=AVERAGE(A1:A10; C1:C10)
Average a list of values
=AVERAGE(10; 20; 30)
Result: 20
Average values mixed with text
If A1:A5 contains: 10, "Text", 5, "", 20:
=AVERAGE(A1:A5)
Result: 11.666…
(Text and empty cells are ignored.)
Advanced Examples ▾
Average across sheets
=AVERAGE(Sheet1.A1:A100)
Average only visible rows (filtered data)
Use SUBTOTAL instead:
=SUBTOTAL(1; A1:A100)
Function code 1 = AVERAGE.
Average with conditions (AVERAGEIF)
=AVERAGEIF(A1:A100; ">50"; B1:B100)
Average with multiple conditions (AVERAGEIFS)
=AVERAGEIFS(C1:C100; A1:A100; "North"; B1:B100; ">1000")
Average excluding zeros
=AVERAGEIF(A1:A100; "<>0")
Average dates or times
If A1:A5 contains times:
=AVERAGE(A1:A5)
Result is a valid time value.
Average with error‑handling
To ignore errors:
=AVERAGE(IF(ISERROR(A1:A10); ""; A1:A10))
Confirm with Ctrl+Shift+Enter.
Common Errors and Fixes ▾
AVERAGE returns Err:502 (Invalid argument)
Occurs when:
- A non‑numeric argument is passed incorrectly
- A malformed range is used
- A text argument is not quoted properly
AVERAGE returns 0 unexpectedly
Possible causes:
- All values are text
- All values are empty
- Imported numbers stored as text
Fix: Convert text to numbers:
Data → Text to Columns → OK
AVERAGE includes values you expected it to ignore
AVERAGE includes:
- Dates
- Times
- Numeric results of formulas
AVERAGE excludes values you expected it to include
AVERAGE 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 AVERAGE for numeric datasets
- Use AVERAGEIF/AVERAGEIFS for conditional averaging
- 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
If you need to average only non‑zero values, use:
=AVERAGEIF(A1:A100; "<>0")