How to Use Excel's DAVERAGE Function - dummies

By Ken Bluttman

The DAVERAGE function in Excel lets you find the average, or mean, of a field for just the rows that match the criteria. For this example, you’ll work with a student database.

The figure shows a worksheet in which the average grade for each course has been calculated by DAVERAGE. For example, cell G22 shows the average grade for Masters of Philosophy. Here is the formula:

Calculating the average grade for each course.
Calculating the average grade for each course.
=DAVERAGE(Students,"Final Grade",F14:G15)

Each calculated average uses a different criteria area. Each area filters the result by a particular course. In all cases, the criteria area for the Teacher is left blank and, therefore, has no effect on the results.

For the sake of comparison, DAVERAGE is also used in cell G24 to show the overall average for all courses. Because a criterion is a required function argument, the calculation in cell G24 is set to look at an empty cell. None of the Class criteria cells is free, so the function looks to the Teacher criterion in cell G3. Because this cell has no particular teacher entered as a criterion, all of the records in the database are used to create this average — just what you want. Here is the formula in cell G24:

=DAVERAGE(Students,"Final Grade",G2:G3)

It doesn’t matter which field header you use in the criterion when you’re getting a result based on all records in a database. What does matter is that there is no actual criterion below the header.