In Google Sheets, the DAVERAGE function is a powerful tool designed to calculate the average of specific values from a structured database table or range. By using a SQL-like query format, users can seamlessly filter data and extract meaningful insights from large datasets, making it an essential function for data analysts and spreadsheet users alike.

Syntax

DAVERAGE(database, field, criteria)

• database: This is the range of cells that makes up the database, including headers.
• field: This specifies which column from the database will be averaged. You can use either the column number or the column name enclosed in quotes.
• criteria: This defines the conditions that must be met for the rows to be included in the average calculation, typically in a range with similar headers as the database.

Example #1

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

This function computes the average of the ‘Sales’ column in the range A1:C10 based on the criteria specified in the range E1:E2. For instance, if E1 contains “Region” and E2 contains “West”, it will average sales for the “West” region. For example, if the total sales for the West region are $2000 across 4 entries, the result would be $500.

Example #2

DAVERAGE(A1:C10, 2, G1:G2)

This formula calculates the average of the second column in the specified range A1:C10 under the same criteria set in G1:G2. If the second column corresponds to ‘Expenses’ and the conditions in G1 and G2 filter for a specific month, the average for that month could be, say, $300.

Example #3

DAVERAGE(A1:C10, "Profit", H1:H2)

This example averages the ‘Profit’ column defined in the database A1:C10 based on criteria in H1:H2. If the average calculated for the relevant entries is $1500, that will be the output based on the filtered data.

Error handling

• DIV/0! This error occurs when there are no records that meet the specified criteria, leading to a division by zero.
• VALUE! This happens when the ‘field’ argument is not numeric, such as when a column identifier is incorrect.
• NAME? This indicates that the name provided for the ‘field’ or ‘criteria’ does not match any headers in the database.

Conclusion

The DAVERAGE function is an invaluable asset in Google Sheets for users seeking to perform specific data analysis through averaging. By understanding its syntax and applying it to relevant data scenarios, users can harness the power of database functions effectively, leading to improved data insights and decision-making capabilities.