DAVERAGE in Excel: Conditional Means with Auditable Criteria

=DAVERAGE(database, field, criteria)

The database range includes the header row. Field identifies the column to calculate: use a quoted heading such as “Units”, or its one-based position within the database. Here Units is field 3, even if the database is later moved to another part of the sheet. Criteria is a separate range containing matching field headers and at least one conditions row. All three arguments are required.

Enter this numeric database in A1:D7:

Put Team in F1 and Product in G1. In F2 enter ="=North"; in G2 enter ="=A". These formulas produce the exact-match criteria text =North and =A. The range F1:G2 selects the three North/A records, whose Units values are 2, 4 and 6 and Sales values are 100, 200 and 300.

Conditions in the same criteria row act together with AND. Alternative criteria rows act with OR. Keep the criteria and output cells outside the database, and leave room for the data to grow.

The matching Units values total 2+4+6=12, with three numeric observations. Dividing by three gives 4. The field-number form =DAVERAGE(A1:D7,3,F1:G2) has the same expected value because Units is the database’s third column.

Selecting Sales instead gives =DAVERAGE(A1:D7,"Sales",F1:G2), expected 200. The criteria still select the same records; field chooses which values those records contribute.

Put Team in J1 and ="=North" in J2. The formula =DAVERAGE(A1:D7,"Units",J1:J2) has expected value 5.25, from 2, 9, 4 and 6. Changing the criteria to include Product B changed the population summarized.

The North/A mean is 4 across three observations, while the one North/B value is 9. The simple mean of those subgroup means is 6.5, which is wrong for all North records. Weight them by their observation counts: (3*4+1*9)/4 gives 5.25.

Make a separate copy of the three selected records and change their Sales to 100, 180 and 240, leaving Units 2, 4 and 6. Put headers Units, Sales and UnitPrice in A12:C12, followed by those three rows. In C13 use =B13/A13 and fill down.

The row unit prices are 50, 45 and 40. Their unweighted average is 45. Total sales divided by total units is 520/12, approximately 43.333333333. These are different questions: average price per record versus price weighted by units.

DAVERAGE does not infer the weighting the user intended. Choose the target field and aggregation logic explicitly; a correct database average can still answer the wrong business question.

Put Team in J4 and Product in K4. Put ="=North" in J5 with K5 blank; put ="=A" in K6 with J6 blank. =DAVERAGE(A1:D7,"Units",J4:K6) selects North records or Product A records, giving 6.2 from 2, 9, 10, 4 and 6.

A record meeting both alternatives is still one database record in the selected union. Do not add two overlapping subgroup totals and divide by a duplicated count.

A meaningful mean requires numeric observations. The dedicated reference used here does not provide a complete error table for empty selections and mixed errors, so exact native error displays are not asserted. Diagnose the selected count before using IFERROR to replace an invalid or missing average with zero.

Use the complete header-and-condition range, not just the condition cells. Check the field heading for spelling and spacing, and verify that a numeric field index still points to the intended column after a layout change. A worksheet’s visible rows are not a substitute for the explicit database range and criteria used in the formula.

Do not include an accidental empty conditions row in the criteria range: an unrestricted alternative can broaden selection. Microsoft’s DAVERAGE reference documents the shared AND/OR, exact-text and case-insensitive matching rules. These examples avoid wildcard, case-sensitive-formula and numeric-text complications.

The database functions do not validate whether the selected records are the right records for the question. Keep a matching-record or numeric-field count beside an important result. Native blank/formula-empty handling, coercion, extreme precision and error propagation not explicitly documented in this guide remain unverified.

• Current documentation: the dedicated Microsoft reference lists Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel 2024, Excel 2024 for Mac, Excel 2021, Excel 2021 for Mac, Excel 2019, Excel 2016.
• Introduced: the first introduction version/build is not established by the dedicated reference used here.
• Last functioning version: unknown; a current help listing is not a last-working-build test.
• Evidence checked: October 15, 2026. Official documentation was freshly retrieved; examples were independently calculated. No native Excel, Mac or web-client execution was performed.

• Microsoft DAVERAGE reference.
• Microsoft function catalog and introduction-marker explanation.
• Editorial testing standards.

Examples use English function names and comma separators. Expected numbers are rounded independent mathematical calculations; documented error expectations are labeled as such. They are not captured Excel output. All illustrative data and policies are fictional.