Excel DAVERAGE Function

Microsoft Excel DAVERAGE Function

Basic Description

The Excel Daverage function calculates the average of values in a field (column) in a database for selected records, that satisfy user-specified criteria.

The function is very similar to the Excel Averageifs function, which was first introduced in Excel 2007.

The syntax of the Excel Daverage function is :

DAVERAGE( database, field, criteria )

where the arguments are shown in the table below:

database	-	A range of cells containing the database. The top row of the database should specify the field names.
field	-	The field (column) within the database, for which the average is to be calculated. This can either be a field number, or can be the field name (ie. the header in the top row of the database) encased in quotes (eg. "Area", "Quarter", etc)
criteria	-	A range of cells that contain the criteria, to specify which records should be included in the calculation. The range can include one or more criteria, which are presented as a field name in one cell and the condition for that field in the cell below. eg. Quarter Area >2 North

Wildcards

You can also use the following wildcards in text-related criteria:

?    -    matches any single character
*    -    matches any sequence of characters

if you do actually want to find the ? or * character, type the ~ symbol before this character in your search.

eg. the condition "A*e" will match all cells containing a text string beginning with "A" and ending in "e".

The criteria supplied beneath each field heading can be either:

	-	a numeric value (including an integer, decimal, date, time, or logical value) (eg. 10, 01/01/2011, FALSE)
or
	-	a text string (eg. "Text", "Wednesday")
or
	-	an expression (eg. ">2", "<>0")

Note that the Excel database functions are not case sensitive. So, for example, the criteria ="North" will be satisfied by cells containing the text "North" or "north".

Excel Daverage Function Examples

	A	B	C	D
1	Quarter	Area	Sales Rep.	Sales
2	1	North	Jeff	$223,000
3	1	North	Chris	$125,000
4	1	South	Carol	$456,000
5	1	South	Tina	$289,000
6	2	North	Jeff	$322,000
7	2	North	Chris	$340,000
8	2	South	Carol	$198,000
9	2	South	Tina	$222,000
10	3	North	Jeff	$310,000
11	3	North	Chris	$250,000
12	3	South	Carol	$460,000
13	3	South	Tina	$395,000
14	4	North	Jeff	$261,000
15	4	North	Chris	$389,000
16	4	South	Carol	$305,000
17	4	South	Tina	$188,000

The following examples are based on the simple database on the right, which stores the sales figures for four sales representatives, over the four quarters of a year.

Example 1

In the example below, the Daverage function is used to calculate the average quarterly sales in quarters 2, 3 and 4, for the Sales Rep. "Tina". The criteria are specified in cells F1 - G2 and the format of the Daverage function is shown in cell F3.

	F	G
1	Quarter	Sales Rep.
2	>1	Tina
3	=DAVERAGE( A1:D17, "Sales", F1:G2 )

The above Daverage function calculates the average of the values in cells D9, D13 & D17, and therefore returns the value $268,333.

Example 2

In the example below, the Daverage function is used to calculate the average sales in quarter 1, by sales reps in the "South" area.

	F	G
1	Quarter	Area
2	1	South
3	=DAVERAGE( A1:D17, "Sales", F1:G2 )

The above Daverage function calculates the average of the values in cells D4 and D5 and so returns the value $372,500.

Note that, in the above two examples, instead of typing in "Sales" for the field argument, we could have simply used the number 4 (to denote the 4th column of the database).