Browse

AVERAGEIF Function (LibreOffice Calc)

Math Intermediate LibreOffice Calc LibreOffice 3.0

The AVERAGEIF function in LibreOffice Calc calculates the average of values that meet a single condition. This guide explains syntax, operators, wildcards, examples, 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 **AVERAGEIF** function calculates the average of values that meet a **single condition**. It is widely used in financial analysis, reporting, and data filtering where conditional averaging is required.

What the AVERAGEIF Function Does

  • Averages values that match a single condition
  • Supports numeric, text, date, and wildcard criteria
  • Allows separate criteria and average ranges
  • Ignores empty cells automatically
  • Works efficiently on large datasets
  • Ideal for conditional reporting and analytics

AVERAGEIF is the conditional version of AVERAGE.

Syntax

AVERAGEIF(range; criteria; average_range)
If average_range is omitted, Calc averages the range itself.
Criteria containing operators or text must be in quotes.

Basic Examples

Average values greater than 50

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

Average values in B1:B10 where A1:A10 equals “North”

=AVERAGEIF(A1:A10; "North"; B1:B10)

Average values equal to a specific number

=AVERAGEIF(A1:A10; 100)

Average values not equal to text

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

Text & Wildcard Examples

Average values where text starts with “A”

=AVERAGEIF(A1:A10; "A*"; B1:B10)

Average values where text contains “car”

=AVERAGEIF(A1:A10; "*car*"; B1:B10)

Average values where text ends with “ing”

=AVERAGEIF(A1:A10; "*ing"; B1:B10)

Average values with exactly 5 characters

=AVERAGEIF(A1:A10; "?????"; B1:B10)
Wildcards work only with text conditions.

Advanced Examples

Use a cell reference as the criteria

=AVERAGEIF(A1:A10; "=" & D1; B1:B10)

Average values between 50 and 100

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

Average dates before a specific date

=AVERAGEIF(A1:A10; "<" & DATE(2025;1;1); B1:B10)

Average values where another column is not blank

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

Case‑sensitive conditional average

AVERAGEIF is case‑insensitive.
For case‑sensitive logic, use SUMPRODUCT:

=SUMPRODUCT((EXACT(A1:A10; "Apple")) * B1:B10) / SUMPRODUCT(EXACT(A1:A10; "Apple"))

Average across sheets

=AVERAGEIF(Sheet1.A1:A10; ">0"; Sheet1.B1:B10)
+ AVERAGEIF(Sheet2.A1:A10; ">0"; Sheet2.B1:B10)

Average errors (rare but possible)

=AVERAGEIF(A1:A10; "#N/A"; B1:B10)

Common Errors and Fixes

AVERAGEIF returns 0 unexpectedly

Possible causes:

  • Criteria missing quotes
  • Numbers stored as text
  • Hidden spaces or non‑breaking spaces
  • Wildcards used incorrectly
  • range and average_range sizes differ

Fix:
Check with:
=LEN(A1)

Err:504 — Parameter error

Occurs when:

  • Ranges are different sizes
  • Criteria is malformed
  • Semicolons are incorrect

AVERAGEIF returns #DIV/0!

Occurs when:

  • No values match the condition
  • All matching values are empty

AVERAGEIF is slow on large datasets

Use:

  • Named ranges
  • Helper columns
  • AVERAGEIFS (more optimized)

Best Practices

  • Always quote criteria containing operators
  • Use AVERAGEIFS for multi‑condition logic
  • Use cell references for dynamic criteria
  • Clean imported data before applying criteria
  • Ensure range and average_range are the same size
  • Use wildcards for flexible text matching
AVERAGEIF is essential for conditional reporting, KPI analysis, and financial modeling.

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.

Use this small practice sheet for the ranges referenced in the formula:

Row A (Quantity) B (Amount) C (Category)
1 1 10 apple
2 2 20 pear
3 3 30 apple
4 4 40 pear
5 5 50 apple 
=AVERAGEIF(C1:C5;"apple";B1:B5)

Expected result: 30.

Find the average amount for matching category labels.

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