DAVERAGE

What it does

DAVERAGE(database, field, criteria)

DAVERAGE(database, field, criteria) selects the records of database that satisfy criteria, reads one column of those records — the column named by field — and returns the arithmetic mean of the numeric values it finds there.

Written out: let R be the set of record rows selected by the criteria mechanism below, let c be the resolved field column, and let

V = [ v : v = value of cell (r, c) for r in R, in record order, where v is a Number ]

Then the result is (Σ V) / |V|. Non-numeric cells in the field column — text, logicals, blanks — are not in V at all: they are neither counted in the denominator nor treated as zero. If V is empty the quotient is undefined and the function is in error territory (see Result and edge cases).

Two things this definition deliberately makes explicit, because Microsoft's one-line description ("Returns the average of selected database entries") does not:

The quoted one-line description is Microsoft's text, from its support article https://support.microsoft.com/en-us/office/daverage-function-a6a2d5ac-4b4b-48cd-a1d8-7b37834e5aee.

1. Selection and aggregation are separate stages. The criteria mechanism picks rows; the field argument picks a column; the aggregate runs over the intersection. Every one of the twelve D-functions shares the first two stages exactly and differs only in the third.
2. The denominator is a count of numbers, not of records. A record can match the criteria and still contribute nothing, if its cell in the field column is blank or non-numeric.

Arguments

Three arguments, all required at the call site; the reference implementation declares an arity of exactly 3.

• database — the header-plus-records range or array. A scalar is not a table and fails.
• field — the column selector: header text or 1-based column index within database. For DAVERAGE the field is genuinely required; the omitted-field convenience exists only for DCOUNT and DCOUNTA.
• criteria — the header-plus-conditions range or array described above.

Note the interaction with the call model: leaving the middle slot empty, as in DAVERAGE(A1:C9,,E1:F2), still passes three arguments. The empty slot delivers the Missing marker described in the value universe, not a shorter call, which is why an arity of exactly 3 and an "omitted field" behaviour are not in conflict.

Try it

Quirks

The criteria-range mechanism

This section is the family's shared explanation. The other eleven database functions — DCOUNT, DCOUNTA, DGET, DMAX, DMIN, DPRODUCT, DSTDEV, DSTDEVP, DSUM, DVAR, DVARP — link here instead of repeating it.

The database grid

database is a rectangular range or array. Its first row is a header row: each cell of it names a field. Every row below the first is one record. The header row is never a record and never participates in matching as data — it participates only as a set of column names.

Header cells are label-valued: text is used as written; a number or a logical in the header row is turned into its label form for matching purposes. A blank header cell leaves that column unnameable, and an error value anywhere in the header row is a hard failure rather than a skipped column.

The field argument

field names one column of database, in either of two forms:

• as text — the header label of the column, e.g. "Yield";
• as a number — the 1-based column index counting from the left edge of database, so 1 is the leftmost column of the database range, not of the worksheet.

Both forms are documented by Microsoft. The index form is the position within the given range, which is the argument position most often misread when the database range does not start at column A.

The criteria grid

criteria is a rectangular range or array with its own header row and one or more rows of conditions beneath it. It is a small second table whose column headers say which database field each condition applies to. Its shape is independent of the database's: it may name a subset of the fields, in any order, and may name the same field more than once.

The matching rule has four parts, and they compose in exactly this order:

1. Header matching. For each column of the criteria grid that holds a condition, the criteria header is matched against the database's header labels to find the target column. The criteria header is what binds a condition to a field — position is irrelevant.
2. AND across a row. Within one criteria row, every populated condition cell must hold. A record matches that row only if it satisfies all of them.
3. OR down the rows. A record is selected if it satisfies any criteria row. Multiple rows are alternatives.
4. Blank means no condition. An empty criteria cell contributes no predicate. This has a consequence readers hit in practice: a criteria row that is entirely blank imposes nothing at all, so it matches every record and, by rule 3, makes the whole criteria range vacuous. An accidental blank row inside the criteria range silently turns a filtered aggregate into an unfiltered one. Likewise, a criteria range consisting of a header row with no condition rows beneath it selects every record.

How a criteria cell is parsed

A populated criteria cell is compiled into a comparison operator plus an operand:

The re-parse of the remainder is ordered: it is tried as a number first, then as the logical words TRUE/FALSE, and only then kept as text. So ">=100" is a numeric comparison and ">=abc" is a text comparison, decided by what the right-hand side looks like, not by the column's contents.

Three consequences worth stating separately:

• Bare text is a prefix match, not an equality match. A criteria cell containing Nor selects Norway and Northwind. Writing ="Nor" — an explicit = operator — asks for whole-value equality instead. This is the single most common surprise in the family, and it is documented behaviour on Microsoft's side.
• * and ? are wildcards in equality and inequality comparisons on text, with ~ as the escape prefix for a literal * or ?.
• The blank operand is how you test emptiness. A cell containing just = selects records whose field cell is blank; a cell containing just <> selects records whose field cell is not blank.

Numeric comparison is not exact double comparison

What is outside the described mechanism

Result and edge cases

Returns a Number.

• Text that looks numeric in the field column is not averaged. The field-column scan admits only cells whose value kind is Number; a cell holding the text "3" is skipped. This follows the range-scan side of the direct-versus-scan asymmetry described in coercion and lifting, and the family is registered there as carrying its own policy row.
• Logicals in the field column are skipped for the same reason.
• Blank cells in the field column are skipped — they do not pull the mean toward zero.
• Arrays: an inline array literal is accepted wherever a range is, because both resolve to a grid. DAVERAGE is not a lift kernel — an array argument is consumed whole, never mapped elementwise.

Errors

How to implement

Notes for implementers

1. Resolve in the documented order. Parse the database grid, then the criteria grid, then the field index, then match. The field index must be resolved against the database headers, which means a bad field argument is detectable before any row is examined — and the order determines which error wins when more than one argument is wrong.
2. Do not use == on doubles for numeric criteria. See the comparison note above.
3. Reduction order is observable. The family declares a sequential left fold; summing the matched values in record order and dividing at the end is not the same in the last bits as any reassociated or pairwise sum. Two implementations that disagree only in summation order will disagree on long, badly-scaled columns.
4. The empty criteria grid is a real input. A criteria range with only a header row selects every record. So does a criteria row whose cells are all blank. Both are easy to get wrong by treating "no predicates" as "no matches".

Rust code

1 daverage "DAVERAGE" (3, 3) [R "database", V "field", R "criteria"] |s, a, sv| { database(s, a, sv, Kind::Average) }

No implementation has been published with a Handbook test suite for this function.

Version differences

No comparison between specific Excel builds is recorded here yet. This does not establish that all builds behave alike.

Alternatives

Relationships

• Siblings: the twelve-member database family, all sharing the mechanism above and differing only in the aggregate. See the family list at the head of that section.
• Nearest modern equivalent: AVERAGEIFS, which expresses the same filtered-average idea with inline condition arguments instead of a criteria grid. It is not a replacement — Excel supports both, and the D-functions remain the only members of this group that take criteria as a worksheet-resident table, which is what makes them useful when the conditions are meant to be edited by the user rather than by the formula author.
• Confused with: AVERAGE (no selection stage), AVERAGEA (counts text and logicals), and SUBTOTAL/AGGREGATE (filter-aware, but by hidden rows rather than by criteria).

Named variants

No named variants are recorded yet.