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

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
`DAVERAGE`

function 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
`DAVERAGE`

the 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”.