Browse

DAVERAGE Function (LibreOffice Calc)

Database Intermediate LibreOffice Calc LibreOffice 3.0

The DAVERAGE function calculates the average of numeric values in a database column that match a set of criteria. It is part of the database function family and supports structured, criteria-based filtering.

The **DAVERAGE** function calculates the average of numeric values in a database column that match a set of criteria. It is part of Calc’s database function family, which uses structured criteria ranges to filter data before performing calculations. DAVERAGE is ideal for conditional averaging in tabular datasets without needing AVERAGEIFS or array formulas.

This guide provides syntax, examples, edge cases, and best practices, making it one of the most complete DAVERAGE references available for LibreOffice Calc.

What the DAVERAGE Function Does

  • Averages numeric values in a specified database column
  • Applies criteria-based filtering using a criteria range
  • Supports multiple criteria columns
  • Supports AND/OR logic via criteria layout
  • Works with structured database ranges

It is designed to be precise, structured, and ideal for database-style averaging.

Syntax

DAVERAGE(database; field; criteria)

Arguments

  • database:
    A range where the first row contains column labels.

  • field:
    The column to average.

    • Use column label in quotes (recommended)
    • Or use column index (1 = first column)

  • criteria:
    A range containing column labels and one or more criteria rows.

Basic Examples

Assume a table in A1:C6:

A (Name) B (Age) C (Score)
John 25 80
Mary 30 90
Alex 22 70
John 28 85
Mary 35 88

Criteria in E1:F2:

Age Score
>25 >80

Average Score values matching criteria

=DAVERAGE(A1:C6; "Score"; E1:F2)

Returns 87.666… (average of 90, 85, 88).

Average Age values matching criteria

=DAVERAGE(A1:C6; "Age"; E1:F2)

Returns 31 (average of 30, 28, 35).

Average using field index

=DAVERAGE(A1:C6; 3; E1:F2)

Averages numeric values in column 3 (Score).

Advanced Examples

Average Score where Name = “John”

Criteria:

Name
John

Formula:

=DAVERAGE(A1:C6; "Score"; E1:E2)

Average Age between 25 and 30

Criteria:

Age
>=25
<=30

Formula:

=DAVERAGE(A1:C6; "Age"; E1:E3)

OR logic (multiple rows)

Criteria:

Age Score
>30
>85

Formula:

=DAVERAGE(A1:C6; "Score"; E1:F3)

Average Score where Name begins with “M”

Criteria:

Name
M*

Formula:

=DAVERAGE(A1:C6; "Score"; E1:E2)

Average with dynamic criteria

=DAVERAGE(A1:C6; "Score"; H1:I2)

Average only positive values

Criteria:

Score
>0

Formula:

=DAVERAGE(A1:C6; "Score"; E1:E2)

Average where Score is non-blank

Criteria:

Score
<>""

Formula:

=DAVERAGE(A1:C6; "Score"; E1:E2)

Edge Cases and Behavior Details

DAVERAGE averages only numeric values

Text, blanks, and errors are ignored.

field can be:

  • Column label
  • Column index
  • Cell containing label

criteria must include column labels

Exact match required.

criteria supports:

  • Comparison operators
  • Wildcards
  • Multiple rows (OR)
  • Multiple columns (AND)

Empty criteria → averages all numeric values

If no numeric values match → #DIV/0!

DAVERAGE of an error in database → error ignored

DAVERAGE of an error in criteria → error returned

Criteria rows:

  • Each row = OR
  • Each column = AND

Common Errors and Fixes

DAVERAGE returns 0 or #DIV/0! unexpectedly

Cause:

  • No numeric values match criteria
  • Criteria labels don’t match database labels
  • Criteria misaligned
  • Field name misspelled

Fix:

  • Ensure labels match exactly
  • Ensure criteria range includes labels
  • Ensure numeric values exist in the target field

Err:502 — Invalid argument

Occurs when:

  • field is invalid
  • database range malformed

Criteria not applied

Cause:

  • Criteria labels not identical to database labels

Best Practices

  • Use column labels instead of index numbers
  • Keep criteria ranges small and clearly labeled
  • Use wildcards for flexible text matching
  • Use multiple criteria rows for OR logic
  • Use multiple criteria columns for AND logic
  • Use DAVERAGE instead of AVERAGEIFS when you need database-style criteria
  • Use FILTER (modern Calc) for dynamic extraction
DAVERAGE is your structured averaging engine — perfect for database-style filtering, multi-criteria logic, and clean, readable conditional averages.
  • Use DSUM for summation
  • Use DCOUNT or DCOUNTA for counting
  • Use DMAX and DMIN for conditional extrema
  • Use DGET for retrieving a single matching record
  • Use AVERAGEIFS for simpler criteria without database structure
  • Use FILTER for dynamic row extraction

By mastering DAVERAGE and its companion database functions, you can build powerful, structured, and criteria-driven data workflows in LibreOffice Calc.