How to Use the DAVERAGE Function in Excel

By Stephen L. Nelson, E. C. Nelson

The DAVERAGE function calculates an average for values in an Excel list. The unique and truly useful feature of DAVERAGE is that you can specify that you want only list records that meet specified criteria included in your average.

If you want to calculate a simple average, use the AVERAGE function.

The DAVERAGE function uses the following syntax:

=DAVERAGE(database,field,criteria)

where database is a range reference to the Excel table that holds the value you want to average, field tells Excel which column in the database to average, and criteria is a range reference that identifies the fields and values used to define your selection.

The field argument can be a cell reference holding the field name, the field name enclosed in quotation marks, or a number that identifies the column (1 for the first column, 2 for the second column, and so on).

As an example of how the DAVERAGE function works, suppose that you’ve constructed this worksheet. Notice that the worksheet range holds a small table. Row 1 predictably stores field names: Name, State, and Donation. Rows 2–11 store individual records.

image0.jpg

Excel database functions analyze information from Excel tables, so you need to know how tables work in order to easily use database functions.

Rows 14 and 15 store the criteria range. The criteria range typically duplicates the row of field names. The criteria range also includes at least one other row of labels or values or Boolean logic expressions that the DAVERAGE function uses to select list records. Note the Boolean expression in cell C15, , which tells the function to include only records where the field shows a value less than 500.

The DAVERAGE function, which appears in cell F3, is

=DAVERAGE(A1:C11,"Donation",A14:C15)

and it returns the average donation amount shown in the database list, excluding the donation from Jeannie in California because that amount isn’t less than 500. The actual function result is 63.88889.

Each row in your criteria range is used to select records for the function. For example, if you use the criteria range below, you select records using two criteria. The criterion in row 15 tells the DAVERAGE function to select records where the donation is less than 500.

The criterion in row 16 tells the DAVERAGE function to select records where the state is California. The DAVERAGE function, then, uses every record in the list because every record meets at least one of the criteria. The records in the list don’t have to meet both criteria; just one of them.

image1.jpg

To combine criteria — suppose that you want to calculate the DAVERAGE for donations from California that are less than 500 — you put both the criteria into the same row, as shown in row 15.

image2.jpg