DAVERAGE Function

Averages a column for the records that match your criteria.

Category

Database

Available in

All versions, Microsoft 365

Returns

A number

Want to practice DAVERAGE in a real spreadsheet? Open a blank sheet in our Practice Sandbox and try it with live data, with the AI Excel tutor beside you. No Excel needed — just a free account.Try it free in the Sandbox

Overview

DAVERAGE works like DSUM but returns the average. Only numbers in the field column are averaged; text and blanks are skipped.

If no record matches, it returns #DIV/0!.

Syntax

=DAVERAGE(database, field, criteria) Explain

Arguments

ArgumentRequired?What it does
databaseYesThe whole list, including the header row — e.g. A1:E7. Each row is a record, each column a field.
fieldYesWhich column to calculate: its header in quotes ("Sales"), a cell holding the header, or its position (5 = the fifth column).
criteriaYesA small range with at least one header copied exactly from the list and the conditions underneath, e.g. G1:G2.

Examples

Example 1 — Average units per Paper order

Three Paper orders: 40, 25 and 30 units.

G5fx=DAVERAGE(A1:E7,"Units",G1:G2)
ABCDEFG
1RegionRepProductUnitsSalesProduct
2NorthAmaraPaper40520Paper
3SouthBenToner12576
4NorthChloeToner8384Avg units
5EastDevPaper2532531.67
6NorthAmaraPens100120
7SouthEllaPaper30390
=DAVERAGE(A1:E7,"Units",G1:G2) Explain

Example 2 — Average sale in the North

Change G2 to any region and the average follows.

G5fx=DAVERAGE(A1:E7,5,G1:G2)
ABCDEFG
1RegionRepProductUnitsSalesRegion
2NorthAmaraPaper40520North
3SouthBenToner12576
4NorthChloeToner8384Avg sale
5EastDevPaper25325341.33
6NorthAmaraPens100120
7SouthEllaPaper30390
=DAVERAGE(A1:E7,5,G1:G2) Explain

Field 5 is the fifth column of the list — Sales.

Common mistakes

  • #DIV/0! Nothing matched the criteria. Wrap it: =IFERROR(DAVERAGE(…),"No orders").
  • #VALUE! or a total of 0. The field name or a criteria header doesn't match the list's header exactly — a stray space is enough. Copy the header rather than retyping it.

Notes

  • Criteria rules: conditions on the same row must all be true (AND); conditions on separate rows are alternatives (OR). Text such as Nor matches anything that begins with it — type ="=North" for an exact match. Use >500, <>East, or wildcards like *Paper*.
  • Modern equivalent: =AVERAGEIFS(E2:E7,A2:A7,"North").
  • Related functions: DSUM, DAVERAGE, DCOUNT, DCOUNTA, DGET, DMAX, DMIN, DPRODUCT, DSTDEV, DSTDEVP, DVAR, DVARP.
Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.