The `DAVERAGE` function in Excel is used to calculate the average of numbers in a database column based on specific criteria. Here’s how to use it:
Syntax
DAVERAGE(database, field, criteria)
- `database`: The range of cells containing the database. It includes the column headers at the top.
- `field`: The column from which to compute the average. This can be the column label in quotes (like `”Age”`) or the column number (like `3` for the third column in the database).
- `criteria`: The range of cells containing the conditions you specify. This range includes column headings and specifies the conditions to be met for selecting records.
Steps to Use DAVERAGE
- Prepare Your Database: Your data needs to be organized in a tabular format with the first row containing column headers.
- Define Criteria Range: Set up your criteria range above or beside the database table. Ensure it includes the same column headers as your database. Under the header(s), specify the condition(s) that rows need to meet for the function to include them in the average calculation.
- Enter the DAVERAGE Formula:
- Click the cell where you want the result.
- Enter the `DAVERAGE` formula using the syntax described above.
Example
Imagine you have a database in cells `A1:D10` for sales records which includes columns: “Region”, “Product”, “Sales”, and “Quarter”. If you want to find the average sales for the “North” region, you would do the following:
- Database: `A1:D10`
- Field: `”Sales”` or `3` if it’s the third column.
- Criteria Range: Let’s say it’s `F1:F2`, with “Region” in `F1` and “North” in `F2`.
Formula:
=DAVERAGE(A1:D10, "Sales", F1:F2)
Tips
- Ensure that the field names in the criteria range exactly match those in the database, including any capitalization.
- Criteria can be more complex, with multiple rows for OR conditions and multiple columns if applying multiple criteria.
- You can use operators like `>`, `<`, `>=`, `<=`, `<>` in criteria for numeric conditions. For example, to average sales greater than 1000, you could write `>1000` under the “Sales” header in the criteria range.
By following these guidelines, you’ll be able to effectively use the `DAVERAGE` function to extract meaningful averages from your datasets based on specified criteria.
Unlock Your Potential
Excel
Access
Power BI
