Contact

AVERAGE Function (LibreOffice Calc)

Math Beginner LibreOffice Calc Introduction not established

The AVERAGE function in LibreOffice Calc calculates the arithmetic mean of numbers, ranges, or mixed arguments. See syntax, worked examples and checks for unexpected results.

Verification status: Retained workbook, screenshot and formula-test records support selected examples in LibreOffice Calc 25.2.3.2 on Linux. No new application run was performed for this evidence-label update; other examples, releases and locales are not covered. How formula examples are checked

Availability and version history

  • Introduced: Documented by LibreOffice 26.8.1.1; original introduction not established.
  • Availability: Supported in LibreOffice 26.8.1.1, verified against its function registry. This identifies a supported release, not a final working version.
  • Scope: Desktop Calc, using the English function name. Availability in other spreadsheet applications is separate.
  • Checked: 2026-10-14 — function reference; versioned support. Documentation/source check; not a new native test. Existing example-specific test claims below retain their stated scope.

The AVERAGE function calculates the arithmetic mean of a set of numbers. The examples below cover sample readings, multiple ranges and references across sheets.

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)

Syntax

AVERAGE(number1; number2; ...)
The examples here use semicolons (;) between arguments. Calc lets you configure argument separators under LibreOffice Calc → Formula in Options (Preferences on macOS); your installation may use commas instead.
Before adapting a formula copied from Excel, check the configured argument separator and reference syntax. Do not replace every comma blindly: commas may also occur inside quoted text, numeric values or array expressions. See the separator and syntax guide and official Formula Options.

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

Use three adjacent sheets in this order: Sheet1, Sheet2, Sheet3, with numeric values 4, 6, 8 in their respective A1 cells. Put the result in Sheet1.B1, outside the input range. In Calc A1 reference syntax, include the sheet and cell at both endpoints:

=AVERAGE(Sheet1.A1:Sheet3.A1)

Independently calculated expectation: 6, because (4 + 6 + 8) / 3 = 6. The range includes sheets between its endpoints, so check tab order and do not insert unrelated sheets inside it. This is a documentation-based example with an arithmetic cross-check, not a new native Calc test. See LibreOffice’s multi-sheet reference guide and Formula Options for the reference-syntax setting.

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 an argument separator that does not match your Formula Options setting

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: Check which entries should actually be numbers before converting anything. Formatting a cell as Number alone does not establish that its stored value is numeric.

  1. Work on a copy, keeping the original unchanged. Select only the intended numeric-text cells, not identifiers or an entire mixed-data column. Keep adjacent cells to the right empty in the test area: splitting can replace them.
  2. If using Data → Text to Columns, inspect the preview before accepting. Disable unwanted separators or fixed-width breaks; a decimal comma must not accidentally split one measurement into two cells. Cancel if the preview is wrong.
  3. Match the import Language/locale to the source’s decimal and thousands separators. Review each preview column’s type: Standard allows numeric interpretation; Text preserves identifiers and leading zeros. Leave special-number/date detection, scientific-notation detection and formula evaluation off unless deliberately needed for trusted data; disabling special-number detection still permits decimal conversion.
  4. After a small test conversion, compare values and adjacent cells with the untouched copy. Check numeric types with ISNUMBER, check the expected numeric count with COUNT, and independently compare the sum and mean before converting more data.

These precautions follow LibreOffice’s Text Import help and the safe Text to Columns guide. Labels and options can vary by release and platform; this is documentation-based guidance, not a newly tested native UI sequence.

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.

Retained evidence scope

The evidence label was reconciled on October 24, 2026 (America/Toronto) without running Calc again. The linked ODS retains both example formulas and cached values (23 and 20); its generator metadata identifies LibreOffice 25.2.3.2 on Linux x86-64, build marker LibreOffice_project/520$Build-2. The separate retained formula-test record reports =AVERAGE(4;6;8) as 6. These are saved artifacts and historical test records, not fresh execution evidence. The exact locale was not recorded in the evidence inspected here; English function names and semicolon examples do not establish locale. See the practice workbook and retained screenshot.