Contact
12 programs
Explore the site

AVERAGE Function (LibreOffice Calc)

Math Beginner LibreOffice Calc LibreOffice 3.0

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.

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 calculates the arithmetic mean of a set of numbers. It is one of the most commonly used statistical functions in LibreOffice Calc, ideal for analyzing datasets, summarizing performance metrics, or computing typical values in financial, scientific, or business contexts.

Native-tested visual example: Find the mean score

Quick answer: Calculate a mean from six independent readings. The following example was run in LibreOffice Calc 25.2.3.2, not simulated or inferred from a formula description.

Enter the sample data

The downloadable workbook uses these exact rows on its Example sheet. Enter the values or download the editable AVERAGE ODS practice workbook instead.

Sheet row A: Session B: Team C: Reading D: Status
5 S-01 Alpha 18 Valid
6 S-02 Alpha 20 Valid
7 S-03 Alpha 22 Valid
8 S-04 Alpha 24 Valid
9 S-05 Alpha 26 Valid
10 S-06 Alpha 28 Valid

Formula and result

Select E5 in the practice workbook. The formula is:

=AVERAGE(C5:C10)

Verified output: 23. The six readings sum to 138; dividing by six gives 23.

Genuine LibreOffice Calc 25.2 screenshot of AVERAGE using sample spreadsheet data; E5 shows 23 and the formula bar shows the tested formula

Screenshot captured from LibreOffice Calc 25.2.3.2. The formula, input values and E5 result are visible in the actual application.

Second example you can try

The workbook also includes this independently tested variation in E12:

=AVERAGE(C5:C7)

Verified output: 20. Change the related input cell and press Enter to see the result update.

Common mistake: Text and blank cells in the referenced range are ignored; zeros are included.

Official documentation: LibreOffice Calc reference for AVERAGE. Test scope: These two worked examples were recalculated in Calc 25.2.3.2; other historical formulas elsewhere on this page may still require separate testing. This file includes no generated or artificial application screenshot.

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)

Reproducible example (tested in LibreOffice Calc)

This standalone example was calculated in LibreOffice Calc 25.2.3.2 and matched the expected value. It is a practical cross-check of one usage, not proof that every example elsewhere on this page is correct.

=AVERAGE(4;6;8)

Expected result: 6.

Calculate a mean from three observations.

If the result differs, check the referenced cells, argument separators and number formatting. For more information, consult the LibreOffice Calc official help.