< Go Back

Excel DAVERAGE Function

Want to learn how to derive average for matching records in a given table? This post will give you an overview of how to do so using DAVERAGE function in Excel.

To get average from matching tables in a certain field using the DAVERAGE function, follow the procedures below:

  • Click on Formulas on the menu bar in Excel.
  • On the Function Library,  click on the drop-down arrow beside AutoSum
  • Next, click on More Function, then insert function “AVERAGE”
  • Click, Go.

The dialogue box will display as shown below:

Figure 1: How to access DAVERAGE formula in Excel

  • This database function is the cells range that includes field headers, which is C8:C15 in the example below
  • The field is the index to calculate for, which is Price in this case.
  • Criteria is the cell range with headers that match the ones in database, which is C5:F6
  • Click OK

It will show you a result displayed in the screenshot below

Figure 2: Result displayed by DAVERAGE

Figure 3: Result displayed by DAVERAGE

Interpretation of the Result

The DAVERAGE function calculates the average in a given table for another table that has elements matching the first table in the same file. The database covers from C8:F15 including the headers, the field to calculate for is “Price” while the criteria is the set of cells with headers that match the ones in the database. Based on the example above, you can calculate the average value for color green where the quantity is greater than 3, using the formula.=DAVERAGE(C8:F15,“Price”,C5:F6)  

Only the green color with quantity >3 are calculated. It can be easily verified by looking through the table for a green with a quantity greater than 3, adding the price and divide into average.  

Criteria Behavior
Green  matches “green” or “GREEN”
>3 Greater than 3

Note that, DAVERAGE function supports wildcards. Criteria can comprise more than a row under the headers. The name of the field can be written in double quotes or as a number representing the field.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar