# DAVERAGE Function in Excel

## 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:

syntax
`=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 `DAVERAGE` function ignores text values and cells that are left empty.
• The requirements for the `DAVERAGE` process 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

ABC
1Product CategoryProductSales
2ElectronicsLaptop200
3ElectronicsMobile Phone150
4FurnitureChair300
5`=DAVERAGE(A1:C4, "Sales", E1:F2)`

Criteria Table:

EF
1Product Category
2Electronics

#### Result table

A
1Average Sales for Electronics
2175

#### 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

ABC
1SupplierProductPrice
2Alpha CorpLaptop\$900
3Alpha CorpMobile Phone\$700
4Beta CorpChair\$100
5`=DAVERAGE(A1:C4, "Price", E1:F2)`

Criteria Table:

EF
1Supplier
2Alpha Corp

#### Result table

A
1Average 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

ABC
1Student NameSubjectScore
2JohnMath90
3JohnEnglish80
4JaneMath85
5`=DAVERAGE(A1:C4, "Score", E1:F2)`

Criteria Table:

EF
1Student Name
2John

#### Result table

A
1Average Score for John
285

#### 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

ABCD
1CategoryAmountCount
2Food1001
3Transport1502
4Food2003`=DAVERAGE(A1:B4, "Amount", A1:B1) * COUNTIF(A2:A4, "Food")`

Result Table

ABCD
1CategoryAmountCountResult
2Food1001
3Transport1502
4Food2003300

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

ABCDE
1CategoryAmountFoodDrink
2Food100100200
3Drink200
4Food150`=MAX(DAVERAGE(A1:B4, "Amount", A1:C2), DAVERAGE(A1:B4, "Amount", A1:D2))`

Result Table

ABCDE
1CategoryAmountFoodDrinkResult
2Food100100200
3Drink200
4Food150200

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

ABCDE
1CategoryAmountFoodDrink
2Food100100200
3Drink150
4Food150`=MIN(DAVERAGE(A1:B4, "Amount", A1:C2), DAVERAGE(A1:B4, "Amount", A1:D2))`

Result Table

ABCDE
1CategoryAmountFoodDrinkResult
2Food100100200
3Drink150
4Food150125

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

ABCD
1CategoryAmount
2Food100
3Transport150
4Food200`=DAVERAGE(A1:B4, "Amount", IF(A2:A4="Food", A1:B1, ""))`

Result Table

ABCD
1CategoryAmountAverage
2Food100
3Transport150
4Food200150

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

ABCD
1CategoryAmount
2Food100
3Transport150
4Food200`=IFERROR(DAVERAGE(A1:B4, "Amount", IF(A2:A4="Travel", A1:B1, "")), "No Data")`

Result Table

ABCD
1CategoryAmountAverage
2Food100
3Transport150
4Food200No 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

ABCD
1CategoryAmount
2Food100
3Transport150
4Food200`=ROUND(DAVERAGE(A1:B4, "Amount", IF(A2:A4="Food", A1:B1, "")), 1)`

Result Table

ABCD
1CategoryAmountAverage
2Food100
3Transport150
4Food200150.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

ABCDE
1CategoryAmountWeight
2Food1000.5
3Transport1500.3
4Food2000.2`=DAVERAGE(A1:C4, "Amount", IF(A2:A4="Food", A1:B1, "")) * SUMPRODUCT(B2:B4, C2:C4)`

Result Table

ABCDE
1CategoryAmountWeightResult
2Food1000.5
3Transport1500.3
4Food2000.275

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

1. If your data set includes date or time values, ensure that they are correctly formatted, as the `DAVERAGE` function might not work appropriately with improperly formatted data.
2. Be careful when referencing cells in your criteria range. A common mistake is to include unnecessary cells, which can return incorrect results.
3. 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).
4. The `DAVERAGE` the function doesn’t count text or blank cells. Make sure the field you choose for calculation has numerical values.
5. 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”.