Contact
12 programs
Explore the site

AVERAGE Function (OpenOffice Calc)

Statistical Beginner OpenOffice Calc OpenOffice.org 3.0

The AVERAGE function in OpenOffice Calc calculates the arithmetic mean of numbers in a range. Learn syntax, examples, 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 AVERAGE function in OpenOffice Calc calculates the arithmetic mean of a set of numbers. It is one of the most widely used statistical functions, ideal for analyzing performance metrics, financial data, scientific measurements, and any dataset where central tendency matters.

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")