DAVERAGE Function in Excel: Explained

In this article, you will learn how to use the DAVERAGE formula in Excel.

‍

What is the DAVERAGE Function in Excel

The DAVERAGE function in Excel is a database function that calculates the average of selected database entries based on specified criteria. This is a very useful function when you need to determine the average of a specific subset of data within a larger dataset.

‍

When is the Excel DAVERAGE function beneficial?

The DAVERAGE function in Excel is handy when you're working with large databases and you need to calculate an average based on certain conditions or criteria. This function can save a significant amount of time and effort compared to manual calculations, particularly for large datasets. Some examples of situations where the DAVERAGE function is useful include the following:

• Sales Data Analysis: The DAVERAGE function can be used to calculate the average sales volume or revenue for specific products, regions, or time periods. For instance, you might want to find out the average monthly sales revenue for a particular product line.
• Financial Analysis: You could use the DAVERAGE function to determine the average price of stocks from a certain sector, or to calculate the average quarterly revenue of companies within a specific industry.

‍

Understanding the syntax of the DAVERAGE function in Excel

The syntax for the DAVERAGE function is the following:

=DAVERAGE(database, field, criteria)

Database: This argument, which is required, represents the cell range that constitutes the database. The database should be a complete table, inclusive of headers.

Field: This required argument denotes the database column that the function will operate on. It can be specified either by using the column label in quotes or by the column number within the dataset (not the sheet) given without quotes (with the first column represented as 1, the second as 2, and so forth).

Criteria: This is a required argument representing the cell range that holds the conditions you define. The criteria range should encompass a minimum of one column label (that must match a database column header) and one cell beneath these labels to set the condition.

‍

How to use the DAVERAGE function in Excel

Suppose you are an accountant and you have a database of various transactions with columns for “Transaction ID”, “Transaction Date”, “Transaction Type”, and “Transaction Value” and you want to know the average transaction value made by specified conditions, for this you could use the DAVERAGE function. For instance, let's say you wanted to find the average transaction value made in May or the average transaction value made by purchases in June. Below is a screenshot of using DAVERAGE in Excel with this example.

‍

As shown by the annotations in the screenshot above, the database argument is an entire array including headers. As mentioned earlier, the field argument can be input as the column header in quotation marks or as the column number. So, since we want to find the average transaction value we want the transaction value column to be our field argument. Hence, we can input this argument as either “Transaction Value” or notice that the transaction value column is the 4th column in the database and instead write “4”. Both types of inputs can be seen above. Lastly, the criteria argument is an array separate from the database that specifies the condition you want applied to the average calculation. For instance, the first formula takes a date range as the condition and so we have a table with the column headers we want (that being Transaction Date) and the values below (our date ranges). 

‍

What's the difference between Excel’s DAVERAGE, AVERAGEIF, and AVERAGEIFS functions?

Excel's DAVERAGE, AVERAGEIF, and AVERAGEIFS functions are all used for averaging data based on certain criteria, but they are used in different contexts and have different capabilities.

• The DAVERAGE function in Excel is a database function that calculates the average of selected database entries based on specified criteria.
• The AVERAGEIF function is simpler and can be used with unstructured data. It takes a range, a criterion, and an optional “average_range”. It computes the average of the cells in the “average_range” that meet the specified criterion. The limitation is that it only allows one condition.
• The AVERAGEIFS function extends AVERAGEIF by allowing multiple criteria. It takes an “average_range” and at least one pair of “criteria_range” and “criteria”. It computes the average of the cells in the “average_range” that meet all the specified conditions. Unlike DAVERAGE, the AVERAGEIFS function is better suited for smaller and unstructured data sets.

Depending on the data set, it is up to the user which function is better suited for the situation.

Go to the page LiveFlow‘s How to Guides to find more information about Excel and Google Sheets formulas and tips that were not covered here.