DAVERAGE function. Get the average value by specifying the aggregation items and search conditions.(Microsoft Excel)

Japanese version.

A function that averages values given an item and a condition.

What is a database in Excel functions?

A database is a table with item names in the first row and rows of data underneath.

It is a database in the Excel functions.

Sample database.

How it works


DatabaseRequired argument.Determination target.
Specify the range of the table containing the item name.
In the aforementioned sample, A2 to C13.
FieldRequired argument.Field to be calculated.
CriteriaRequired argument.Conditions to be calculated.

Example Results

One condition. Use column name for Field

Target cell range in argument 1.

"Price" and "Qty" in argument 2.

"Name" is "C" as the condition.

The average of "Price" is displayed in E5 and the average of "Qty" in E6.

One condition.Use column number for Field

Items can be numbered consecutively with the leftmost number being 1.

In most cases, item names are recommended because they make the formulas more readable.
However, if the item names change frequently, column numbers are better.

Multiple condition specification (logical OR).

Arranging the conditions vertically results in a logical sum.

In cell E6 of the example below, the rows corresponding to "C" and "D" are averaged.

Multiple condition specification (logical AND).

When the conditions are arranged horizontally, they become "AND" condition specifications.

In the example below, the rows that correspond to any of "B," "C," "D," and "E" (Blue background) and also correspond to "Price" of 82 or more (Green background) are averaged. (Red background color is the target of calculation)

Multiple condition specification. (Range of values)

A range of conditions can also be specified, such as "greater than or equal to" or "less than or greater than.

To do so, specify a comparison operator with the same items side by side.



Microsoft Excel Functions Database