Menu

AVERAGE Function (LibreOffice Calc)

Math Beginner LibreOffice Calc Introduced in LibreOffice 3.0
arithmetic statistics basic-functions

The AVERAGE function in LibreOffice Calc calculates the arithmetic mean of numbers, ranges, or mixed arguments. This guide covers syntax, examples, edge cases, errors, and expert best practices.

Compatibility

â–¾

What the AVERAGE Function Does â–¾

  • Computes the arithmetic mean
  • Accepts numbers, cell references, and ranges
  • Ignores empty cells automatically
  • Ignores text values unless using AVERAGEA
  • Works across sheets
  • Supports mixed arguments (numbers + ranges)
  • Handles large datasets efficiently

AVERAGE is designed to be fast, reliable, and mathematically consistent, even when working with thousands of rows.

Syntax â–¾ 

AVERAGE(number1; number2; ...)
LibreOffice Calc uses semicolons (;) to separate arguments, not commas.
If you copy formulas from Excel, replace commas with semicolons to avoid Err:508 or Err:504.

Basic Examples â–¾

Average a range of cells

=AVERAGE(A1:A10)

Calculates the mean of values from A1 through A10.

Average multiple ranges

=AVERAGE(A1:A10; C1:C10)

Useful when your data is separated into blocks.

Average specific values

=AVERAGE(5; 12; 19)

You can also mix numbers and cell references:

=AVERAGE(A1:A10; 25; C5)
AVERAGE can accept up to 255 arguments, including ranges, values, and references.

Advanced Examples â–¾

Average values across sheets

=AVERAGE(Sheet1.A1:A10; Sheet2.B1:B10)

Average values using 3D references

LibreOffice supports 3D ranges:

=AVERAGE(Sheet1:Sheet5.A1)

This computes the mean of cell A1 across five sheets.

Average only visible cells (filtered data)

LibreOffice Calc does not have an AVERAGEVISIBLE function, but you can use:

=SUBTOTAL(101; A1:A10)

Function code 101 means AVERAGE.

Average values with conditions (AVERAGEIF)

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

Averages values in B1:B10 where A1:A10 is greater than 50.

Average values with multiple conditions (AVERAGEIFS)

=AVERAGEIFS(C1:C100; A1:A100; "North"; B1:B100; ">1000")

Common Errors and Fixes â–¾

Err:508 — Missing parenthesis

Usually caused by:

  • Missing )
  • Using commas instead of semicolons

Err:504 — Parameter error

Occurs when:

  • A text value is passed incorrectly
  • A range reference is malformed

AVERAGE returns #DIV/0! or Err:532

This happens when:

  • All referenced cells are empty
  • All referenced cells contain text
  • The function receives no valid numeric input

Fix: Ensure at least one numeric value exists in the referenced range.

AVERAGE returns unexpected results

Possible causes:

  • Cells formatted as text
  • Hidden apostrophes ('123)
  • Imported CSV values not converted to numbers

Fix: Convert text to numbers:
Data → Text to Columns → OK

Best Practices â–¾

  • Use ranges instead of long lists of individual cells
  • Keep numeric data formatted as numbers, not text
  • Use SUBTOTAL when working with filtered data
  • Use AVERAGEIFS instead of complex IF + AVERAGE combinations
  • Name ranges for cleaner formulas
Named ranges make formulas easier to read and maintain.
Example: =AVERAGE(Sales_Q1)

Related Functions

Copyright 2026. All rights reserved.