This post is about the DAVERAGE function in Google Sheets, which is a database aggregation function.

The DAVERAGE function in Google Sheets will be handy for average calculations if your data is structured.

Structured data means data arranged like a database table. In other words, a table containing labels for each column.

In structured data, there shouldn’t be any merged cells in the range. Also, the columns mustn’t be mixed data type.

If your data is structured, you can replace AVERAGE, AVERAGEIF, or even AVERAGEIFS functions with the function DAVERAGE.

The database functions can ensure more effective data processing and analysis. So, if possible, make your data structured. Also, for aggregation and lookup in your such database, try the relevant database functions.

Syntax of the Function DAVERAGE in Sheets:

DAVERAGE(database, field, criteria)

Arguments:

Database – The range containing the data to consider. It must be structured as detailed above. I mean, each column must contain unique labels in the first row.

Field – The column number or column label of the column that contains the values to be extracted and operated on.

Criteria – The condition to filter the database values before operating.

How to Use the DAVERAGE Function in Google Sheets

I will try to include different types of criteria usage tips in my example formulas below. So take your own time to complete all the examples on your Sheet. Here we go!

DAVERAGE Formula With Single Criteria

Let me start with a basic example of the DAVERAGE function in Google Sheets.

All the database functions in Google Sheets take three arguments and DAVERAGE is no exception. They are database, criteria, and field.

I have already explained this above. In addition, to give you a more clear picture, I have marked the same on the screenshots below.

In the below example I want to find the average of the “qty” in column 4.

You can use either of the below formula to find the average of the product “Beige Gravel 20-40 mm”.

=DAVERAGE(A1:D13,4,F1:F2)

or

=DAVERAGE(A1:D13,"qty",F1:F2)

What is the difference between these two formulas?

In the second formula, you can see the field label, but in the first it’s the field number instead.

The above DAVERAGE formula is equal to the below AVERAGEIF formula. This is just for your information.

=averageif(B2:B13,F2,D2:D13)

Again I am telling you, I highly recommend the use of the database functions in structured data.

Must Read: Google Sheets Functions Guide.

For your quick reference here is the Syntax of AVERAGEIF in Google Sheets.

AVERAGEIF(criteria_range, criterion, [average_range])

In DAVERAGE in Google Sheets, as said above, you must use all the three arguments. They are database, field, and criteria. That can possibly arise a genuine question!

I just want to find the AVERAGE of the “qty” column. I don’t want the formula to filter the data using any criterion. What’s the solution then?

The following example shows how to use the DAVERAGE function without criteria in Google Sheets.

DAVERAGE Formula Without Criteria

Needless to say, you must use all three arguments in the formula. So, the formula will be the same as above. But one thing you must take care of. Just keep the criteria cell blank.

Here is the equivalent AVERAGE formula.

=average(D2:D13)

DAVERAGE Formula With Multiple Criteria

I like the way DAVERAGE handles multiple criteria. It’s pretty simple to use and easy to read on a future date.

See how I am going to include multiple criteria in the formula.

The multiple criteria are from the same field. So you just need to enter one criterion below another.

If you are looking for an alternative formula to the above, I have one suggestion. It’s an Average + Filter formula combo.

=average(filter(D1:D13,(B1:B13=F2)+(B1:B13=F3)))

You can’t use AVERAGEIFS when the criteria are from the same field.

Comparison Operators in DAVERAGE Function in Google Sheets

With the help of the below example, I am trying to explain how to use the comparison operators in DAVERAGE. Also, please note that there are multiple criteria from two different fields.

=DAVERAGE(A1:D13,4,F1:G2)

Since the criterion are from two different fields, you can use AVERAGEIFS too.

=AVERAGEIFS(D2:D13,A2:A13,F2,B2:B13,G2)

I know one more example is necessary. So that you can fully understand the use of comparison operators in Docs Sheets DAVERAGE formula.

Please take a look at the image below. I am not going into further details or formula explanation.

How to Use Criteria Within the Formula in DAVERAGE

I am not in favor of using criteria within the formula in database functions because it makes the formula complex. But you must know that use too.

With Curly Brackets we can create virtual arrays in Google Sheets. If you know that, things will be pretty much easier for you. Assume, below is the criteria that you want to use in DAVERAGE (of course it’s from the example above).

You can crate this criteria range as a virtual array using the Curly Braces as below.

={"date_of_supply","date_of_supply","material_description";">= 20/3/19","< 21/3/19","Beige Gravel 20-40 mm"}

The comma separator in the formula splits the values to columns and the semicolon to the rows.

I am going to use this virtual criteria range in one formula.

In the first formula, the criteria are in the range F1:H2.

=DAVERAGE(A1:D13,4,F1:H2)

Now see the same formula but this time with the virtual array.

=DAVERAGE(A1:D13,4,{"date_of_supply","date_of_supply","material_description";">= 20/3/19","< 21/3/19","Beige Gravel 20-40 mm"})

I don’t prefer this type of criteria usage in the DAVERAGE function in Google Sheets.