Part 1: Introduce
Definition
DAVERAGE is a database function in Microsoft Excel that calculates the average of selected database entries based on specified conditions. It’s a beautiful tool for summarizing or analyzing data that fits specific criteria.
Purpose
The DAVERAGE the function is used to compute the average of selected database columns that match specified criteria. It’s useful when calculating an average from a large dataset but only wants to include entries that meet certain conditions.
Syntax & Arguments
The syntax for the DAVERAGE function in Microsoft Excel is:
=DAVERAGE(database, field, criteria) Explanation of Arguments
- Database: This is the range of cells that makes up the database. The database should reference a range of cells or a named range. The database must include a header row.
- Field: This is the column in the database where the function will be used. The area can be given as text with the column header or as a number, where 1 refers to the first column, 2 to the second column, and so on.
- Criteria: This is the range of cells that contains the conditions specified. The standard refers to a range of cells or a named range. It should include at least one header title and one cell below the header with a condition to be met.
Return value
The DAVERAGE function will return the average of the selected database entries based on the given conditions.
Remarks
- The
DAVERAGEfunction ignores text values and cells that are left empty. - The requirements for the
DAVERAGEprocess are case-insensitive, meaning it treats lowercase and uppercase text the same way.
Part 2: Examples of the DAVERAGE Function in Microsoft Excel
Example 1:
Purpose
Calculate the average sales of a specific product category.
Data table and formula
| A | B | C | |
|---|---|---|---|
| 1 | Product Category | Product | Sales |
| 2 | Electronics | Laptop | 200 |
| 3 | Electronics | Mobile Phone | 150 |
| 4 | Furniture | Chair | 300 |
| 5 | =DAVERAGE(A1:C4, "Sales", E1:F2) |
Criteria Table:
| E | F | |
|---|---|---|
| 1 | Product Category | |
| 2 | Electronics |
Result table
| A | |
|---|---|
| 1 | Average Sales for Electronics |
| 2 | 175 |
Explanation
In this example, the DAVERAGE The function calculates the average sales for the ‘Electronics’ category. The function returns the average sales of all ‘Electronics’ products, which in this case is 175.
Example 2:
Purpose
Calculate the average price of a specific supplier’s products.
Data table and formula
| A | B | C | |
|---|---|---|---|
| 1 | Supplier | Product | Price |
| 2 | Alpha Corp | Laptop | $900 |
| 3 | Alpha Corp | Mobile Phone | $700 |
| 4 | Beta Corp | Chair | $100 |
| 5 | =DAVERAGE(A1:C4, "Price", E1:F2) |
Criteria Table:
| E | F | |
|---|---|---|
| 1 | Supplier | |
| 2 | Alpha Corp |
Result table
| A | |
|---|---|
| 1 | Average Price for Alpha Corp Products |
| 2 | $800 |
Explanation
Here, the DAVERAGE The function calculates the average price for ‘Alpha Corp’s products. The function considers all products supplied by ‘Alpha Corp’ and computes their average price of $800.
Example 3:
Purpose
Calculate the average score of a specific student.
Data table and formula
| A | B | C | |
|---|---|---|---|
| 1 | Student Name | Subject | Score |
| 2 | John | Math | 90 |
| 3 | John | English | 80 |
| 4 | Jane | Math | 85 |
| 5 | =DAVERAGE(A1:C4, "Score", E1:F2) |
Criteria Table:
| E | F | |
|---|---|---|
| 1 | Student Name | |
| 2 | John |
Result table
| A | |
|---|---|
| 1 | Average Score for John |
| 2 | 85 |
Explanation
In this example, the DAVERAGE The function is used to calculate the average score of ‘John’. The process considers all scores from ‘John’ and computes their average, which is 85.
Example 4: DAVERAGE with COUNT Function
Purpose of Example
This example demonstrates how to use the DAVERAGE function with the COUNT function to calculate the average of a particular data set and count the number of specific items in a column.
Data Tables and Formulas
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Category | Amount | Count | |
| 2 | Food | 100 | 1 | |
| 3 | Transport | 150 | 2 | |
| 4 | Food | 200 | 3 | =DAVERAGE(A1:B4, "Amount", A1:B1) * COUNTIF(A2:A4, "Food") |
Result Table
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Category | Amount | Count | Result |
| 2 | Food | 100 | 1 | |
| 3 | Transport | 150 | 2 | |
| 4 | Food | 200 | 3 | 300 |
Explanation
In this example, the DAVERAGE function calculates the average of the “Amount” column for all rows. The COUNTIF function counts the number of “Food” items in the “Category” column. The result is the product of the average calculated and the count of “Food” items, which is 300.
Example 5: DAVERAGE with MAX Function
Purpose of Example
This example demonstrates how to use the DAVERAGE function with the MAX function to calculate the maximum average amount across multiple categories.
Data Tables and Formulas
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Category | Amount | Food | Drink | |
| 2 | Food | 100 | 100 | 200 | |
| 3 | Drink | 200 | |||
| 4 | Food | 150 | =MAX(DAVERAGE(A1:B4, "Amount", A1:C2), DAVERAGE(A1:B4, "Amount", A1:D2)) |
Result Table
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Category | Amount | Food | Drink | Result |
| 2 | Food | 100 | 100 | 200 | |
| 3 | Drink | 200 | |||
| 4 | Food | 150 | 200 |
Explanation
In this example, the DAVERAGE function is used twice to calculate the average of the “Amount” column for the “Food” and “Drink” categories separately. The MAX function is then used to determine the maximum average amount between the two types. The result is 200, which is the maximum average amount.
Example 6: DAVERAGE with MIN Function
Purpose of Example
This example demonstrates using the DAVERAGE function with the MIN function to calculate the minimum average amount across multiple categories.
Data Tables and Formulas
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Category | Amount | Food | Drink | |
| 2 | Food | 100 | 100 | 200 | |
| 3 | Drink | 150 | |||
| 4 | Food | 150 | =MIN(DAVERAGE(A1:B4, "Amount", A1:C2), DAVERAGE(A1:B4, "Amount", A1:D2)) |
Result Table
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Category | Amount | Food | Drink | Result |
| 2 | Food | 100 | 100 | 200 | |
| 3 | Drink | 150 | |||
| 4 | Food | 150 | 125 |
Explanation
In this example, the DAVERAGE function is used twice to calculate the average of the “Amount” column for the “Food” and “Drink” categories separately. The MIN function is then used to determine the minimum average amount between the two types. The result is 125, which is the minimum average amount.
Example 7: DAVERAGE with IF Function
Purpose of Example
This example demonstrates how to use the DAVERAGE function in conjunction with the IF function to calculate the average of a particular data set, conditional upon a specific criterion being met.
Data Tables and Formulas
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Category | Amount | ||
| 2 | Food | 100 | ||
| 3 | Transport | 150 | ||
| 4 | Food | 200 | =DAVERAGE(A1:B4, "Amount", IF(A2:A4="Food", A1:B1, "")) |
Result Table
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Category | Amount | Average | |
| 2 | Food | 100 | ||
| 3 | Transport | 150 | ||
| 4 | Food | 200 | 150 |
Explanation
In this example, the DAVERAGE function is used to calculate the average of the “Amount” column, but only for rows where the “Category” column is equal to “Food”. The IF function is used to specify this condition, and the DAVERAGE function then calculates the average based on this condition. The result is 150, which is the average of the amounts 100 and 200.
Example 8: DAVERAGE with IFERROR Function
Purpose of Example
This example demonstrates using the DAVERAGE function with the IFERROR function to handle potential errors when calculating averages based on certain conditions.
Data Tables and Formulas
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Category | Amount | ||
| 2 | Food | 100 | ||
| 3 | Transport | 150 | ||
| 4 | Food | 200 | =IFERROR(DAVERAGE(A1:B4, "Amount", IF(A2:A4="Travel", A1:B1, "")), "No Data") |
Result Table
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Category | Amount | Average | |
| 2 | Food | 100 | ||
| 3 | Transport | 150 | ||
| 4 | Food | 200 | No Data |
Explanation
In this example, the DAVERAGE function is used to calculate the average of the “Amount” column, but only for rows where the “Category” column is equal to “Travel”. However, since there are no rows with “Travel” in the “Category” column, this would typically result in an error. The IFERROR function catches this error and returns “No Data” instead.
Example 9: DAVERAGE with ROUND Function
Purpose of Example
This example demonstrates how to use the DAVERAGE function with the ROUND function to calculate the average of a particular data set and round the result to a specified number of decimal places.
Data Tables and Formulas
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Category | Amount | ||
| 2 | Food | 100 | ||
| 3 | Transport | 150 | ||
| 4 | Food | 200 | =ROUND(DAVERAGE(A1:B4, "Amount", IF(A2:A4="Food", A1:B1, "")), 1) |
Result Table
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Category | Amount | Average | |
| 2 | Food | 100 | ||
| 3 | Transport | 150 | ||
| 4 | Food | 200 | 150.0 |
Explanation
In this example, the DAVERAGE function is used to calculate the average of the “Amount” column, but only for rows where the “Category” column is equal to “Food”. The ROUND function then rounds this average to one decimal place. The result is 150.0.
Example 10: DAVERAGE with SUMPRODUCT Function
Purpose of Example
This example demonstrates using the DAVERAGE function with the SUMPRODUCT function to calculate a weighted average based on certain conditions.
Data Tables and Formulas
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Category | Amount | Weight | ||
| 2 | Food | 100 | 0.5 | ||
| 3 | Transport | 150 | 0.3 | ||
| 4 | Food | 200 | 0.2 | =DAVERAGE(A1:C4, "Amount", IF(A2:A4="Food", A1:B1, "")) * SUMPRODUCT(B2:B4, C2:C4) |
Result Table
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Category | Amount | Weight | Result | |
| 2 | Food | 100 | 0.5 | ||
| 3 | Transport | 150 | 0.3 | ||
| 4 | Food | 200 | 0.2 | 75 |
Explanation
In this example, the DAVERAGE function is used to calculate the average of the “Amount” column for rows where the “Category” column is equal to “Food”. The SUMPRODUCT function then calculates the sum of the products of the “Amount” and “Weight” columns. The final result is the product of these two values, representing a weighted average of 75.
Part 3: Tips and tricks
- If your data set includes date or time values, ensure that they are correctly formatted, as the
DAVERAGEfunction might not work appropriately with improperly formatted data. - Be careful when referencing cells in your criteria range. A common mistake is to include unnecessary cells, which can return incorrect results.
- When defining criteria, you can use operators like
>(greater than),<(less than),>=(greater than or equal to),<=(less than or similar to), and<>(not identical to). - The
DAVERAGEthe function doesn’t count text or blank cells. Make sure the field you choose for calculation has numerical values. - You can use wildcard characters in text criteria. Use
?to match any single personality and*to match any sequence of characters. For example,"a*"it will match any string that starts with “a”.