Averaging and Extracting With DAVERAGE and DGET

This lesson covers two final database functions:

• DAVERAGE - the average of a numeric field for matching records.
• DGET - pulls a single value from the one row that matches your criteria.

Both reuse the same (database, field, criteria) pattern you already know, so you are mostly learning what they return, not new syntax.

DAVERAGE(database, field, criteria) takes the mean of the field column across rows that match the criteria range.

• database - full table with headers.
• field - a numeric column to average.
• criteria - the matching rules block.

It is like AVERAGEIFS, but reads conditions from cells.

With sales data in A1:C13 (headers Region, Rep, Amount), put Region in E1 and East in E2.

The formula returns the average Amount across East rows. If East orders are 1200, 800, and 1000, the average is 1000. Switch E2 to West and the average updates automatically.

DAVERAGE accepts the full criteria toolkit. To average only large East orders, build a two-column criteria range: Region and Amount headers, then East and >1000 beneath them.

Same-row conditions are AND, so the function averages amounts from East rows above 1000. Blank fields are ignored - only numeric values feed the average.

Unlike DSUM, which returns 0, DAVERAGE returns a #DIV/0! error when no rows match - because you cannot average zero values.

Guard against it by checking the count first, or wrap it in IFERROR to show a friendly message instead of an error code.

DGET is different: it returns a single value from the field of the one row that matches your criteria.

Use it like a lookup. If you have a unique key - say an Order ID - DGET fetches one field from that exact record. Its power and its danger come from insisting on exactly one match.

Suppose your table has unique reps and you want the Amount for rep 'Sara'. Put Rep in E1 and Sara in E2.

DGET finds Sara's single row and returns her Amount. Because it can match on several criteria columns at once, DGET handles multi-key lookups that a plain VLOOKUP cannot.

DGET is strict about matching exactly one row:

• If no rows match, it returns #VALUE!.
• If more than one row matches, it returns #NUM!.

These errors are actually useful - they warn you that your key is missing or not unique. Tighten the criteria until exactly one record qualifies.

To guarantee a single match, add more conditions. Want the Amount for Sara in the East region? Use a two-column criteria range: Rep and Region headers, then Sara and East beneath.

The AND logic narrows results to one row, and DGET returns its Amount. This is how DGET acts as a clean multi-key lookup.

Because DGET errors on zero or multiple matches, wrap it for a smoother experience. IFERROR turns either error into a readable message.

If you specifically want to flag a duplicate differently from a miss, you can test the count with DCOUNTA first and branch your message accordingly.

A quick guide to picking the right tool:

• Need a total of matching rows? Use DSUM.
• Need a count of matching rows? Use DCOUNT or DCOUNTA.
• Need the average of matching rows? Use DAVERAGE.
• Need one value from a single matching row? Use DGET.

All four share one criteria range, so you can build a report that totals, counts, averages, and looks up - all driven by the same set of condition cells.

Your DGET criteria match three rows in the table. What does DGET return?

You finished the D-function family:

• DAVERAGE averages a numeric field for matching rows; no matches gives #DIV/0!.
• DGET returns one value from exactly one matching row; zero matches gives #VALUE! and multiple matches gives #NUM!.
• Both share the (database, field, criteria) pattern and the AND/OR criteria-range rules.
• Wrap them in IFERROR for clean, professional output.

Together with DSUM and DCOUNT, you can now run full criteria-based reports straight from a structured table.