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.
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.
|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.