The DAVERAGE function in Excel is a powerful tool used to compute the average of values that meet specified criteria within a defined database. This function is particularly useful when working with large datasets, allowing users to filter and analyze information efficiently.

Syntax

DAVERAGE(database, field, criteria)

• database: The range of cells that comprise the data set you want to use, including headers.
• field: Indicates which column to average, specified either by the column header as a text string or by the column number.
• criteria: The range that contains the conditions that must be met for a cell to be included in the average calculation.

Example #1

DAVERAGE(A1:C10, "Sales", E1:E2)

This function calculates the average of the ‘Sales’ column in the range A1:C10 where the criteria specified in E1:E2 are met. For instance, if E1 contains a condition that filters for ‘Region A’, the result will reflect the average sales of records only from ‘Region A’. Example result: 3500.

Example #2

DAVERAGE(A1:C10, 2, D1:D2)

This example averages the values in the second column (assumed to be ‘Revenue’) of the specified data range, according to the criteria laid out in D1:D2. If D1 filters for ‘2023’, the result might show the average revenue for that year, yielding a result of 5200.

Example #3

DAVERAGE(A1:C10, "Amount", F1:F2)

In this use case, the function calculates the average of the ‘Amount’ column within A1:C10 based on the conditions defined in F1:F2. For instance, if F1 is set to identify ‘Processed’ transactions, the average amount processed could return a value like 1500.

Error handling

• DIV/0!: This error occurs when no records meet the specified criteria, leading to a division by zero in the average calculation.
• NAME?: This indicates that the field name provided does not match any header in the database range, potentially due to a typo.
• VALUE!: This error arises if non-numeric values are included in the field you are attempting to average.

Conclusion

In summary, the DAVERAGE function is an essential tool for anyone looking to perform targeted statistical analysis on data stored in a structured format. By leveraging this function alongside criteria, users can derive meaningful averages that enhance data-driven decision-making.