In this article, we will learn about how to use the DAVERAGE function in Excel.
DAVERAGE function is built-in database function in Excel. The DAVERAGE function returns the AVERAGE of the values from the database having multiple conditions. Conditions to the function is given using a table, which must have the same fields as on the database.
DAVERAGE function returns the AVERAGE of the field based on given multiple criteria. The DAVERAGE function takes criteria as a table having the same fields as on the database.
Syntax:
| =DAVERAGE (database, field, criteria) |
Database : database
Field : the required field, call by name or text in quotes.
Criteria : one or more conditions taken as table array.
EXAMPLE:
This all might be confusing to you so lets gear up and understand the DAVERAGE function using it in an example.
Database
Table
Here we have a list of Products bought with their respective details & We need to get the AVERAGE of Quantity based on criterias mentioned in the table.
There are some things you have to take care of when using this function
First condition is City : Los Angeles
Second condition is Total price : <100
So the function must return the Average of the Quantity bought from Los Angeles whose Total Price is less than 100.
Use the formula for the above condition:
| =DAVERAGE (A1 : F15, "Quantity", A17 : F18) |
A1 : F15 : database
"Quantity" : field by text in quotes or field can be input as 4 (4th column from the database).
A17 : F18 : criteria as table array
The AVERAGE of the values comes out to be 47.5.
DAVERAGE function can have multiple criteria on one field by adding rows in the table
Now we will take an example taking multiple criteria on the same database.
Here we have multiple conditions on the field Quantity i.e. less than 100 & greater than 50 units bought from Los Angeles. We need the average of TotalPrice having the conditions stated in the snapshot.
Use the formula for the above condition:
| =DAVERAGE (A1:F15, "TotalPrice", A17:F19) |
A1 : F15 : database
"TotalPrice" : field by text in quotes or field can be input as 6 (6th column from the database).
A17 : F19 : multiple criteria by adding one row in table array
The AVERAGE of the values comes out to be 92.203.
You don’t need to edit the formula every time as criteria field gets updated as soon as you provide the input to table fields.
Notes:
As you can see the DAVERAGE function in excel returns values from the table field.
Hope you understood How to use the DAVERAGE function in Excel. Explore more articles on Mathematical functions like AVERAGEIF and AVERAGEIFS functions here. Please state your query or feedback in the comment box below.
Related Articles
How to use the DCOUNT function in Excel
How to use the DSUM function in Excel
How to use the DAVERAGE Function in Excel
How to use the DMAX Function in Excel
How to use the DMIN Function in Excel
How to use the DPRODUCT Function in Excel
Popular Articles
50 Excel Shortcut to Increase Your Productivity
If with conditional formatting
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
