DAVERAGE function

The DAVERAGE function in Excel calculates the average of values in a field (column) of a database that meet specified criteria. This function is useful when you want to find the average of specific records in a list or database based on one or more conditions.

Syntax

DAVERAGE(database, field, criteria)

Parameters

• database: The range of cells that makes up the list or database. The first row of the database should contain headers for each column.
• field: The column that contains the data you want to average. You can either specify the column label (enclosed in double quotes, e.g., "Sales") or provide the column number (e.g., 2 for the second column).
• criteria: The range of cells that contains the conditions you want to apply to filter the data. The criteria range should include at least one column header and a condition below it.

Example of Usage

Let’s say you have a sales database with the following columns:

You want to find the average sales for the “East” region.

1. Database range: A1:D6 (including headers).
2. Field: "Sales" (or you can use the column number 3 since “Sales” is the third column).
3. Criteria range: For example, you might set up criteria in cells F1:F2 like this:

4. Formula:

=DAVERAGE(A1:D6, "Sales", F1:F2)

Or

=DAVERAGE(A1:D6, 3, F1:F2)

In this case, DAVERAGE will calculate the average sales for the “East” region. The result will be 1600, which is the average of the sales values 1000, 2000, and 1800 from the “East” region.

Detailed Example

Suppose you have a more complex dataset, and you want to find the average sales for “Laptops” in the “West” region:

1. Criteria: You can set up the criteria in cells F1:G2 like this:

2. Formula:

=DAVERAGE(A1:D6, "Sales", F1:G2)

This formula will average the sales values for “Laptops” in the “West” region. In this case, the result will be 2500, because there is only one record meeting both criteria.

Key Points

• Criteria Flexibility: The criteria parameter can handle complex conditions, including multiple columns or specific ranges of values. For example, you could find the average sales for products sold in a certain date range.
• Exact Matching: The criteria range can include wildcards or formulas for more advanced filtering.
• Column Reference: The field can be either the column header in double quotes (e.g., "Sales") or the index number (e.g., 3 for the third column).

Common Use Cases

• Conditional Averages: Use DAVERAGE to calculate averages based on multiple conditions, such as “average sales for a product in a specific region” or “average profit for a date range.”
• Database Operations: It’s especially helpful in database-like datasets where you need to filter and aggregate data simultaneously.

Tips

• Named Ranges: You can use named ranges for the database and criteria, which can make your formulas easier to manage, especially in large datasets.
• Wildcard Support: You can use ? and * in the criteria range for partial matches (? represents one character and * represents any number of characters).
• Advanced Criteria: You can use formulas in the criteria range to create more complex conditions, such as using a formula like =A2>1000 to find averages for sales greater than 1000.

Summary

The DAVERAGE function is a powerful tool for calculating conditional averages in Excel. It allows you to filter a database based on criteria and return the average of values from a specified field. This function is ideal for scenarios where you need to analyze subsets of data, such as sales figures for a particular product or region, making it perfect for reports, dashboards, and business analysis tasks.