DAVERAGE

Syntax:

DAVERAGE ( database , field , criteria )

Description: Averages the values in a column of a list or database that match the specified criteria.

Note:

Arguments:

Name	Type	Description
database	reference	The range of cells that makes up the list or database, which shall be a list of related data in which rows of related information are records, and columns of data are fields. The first row of the list shall contain labels for each column.
field	text, number	Indicates the column to which criteria shall be applied. It can either be a string containing the column's label, or the column's position number, where columns are numbered starting at 1. [: If column 3's label is "Age" then either 3 or "Age" can be used. ]
criteria	reference	The range of cells that contains the specified conditions. Each cell in that range that contains a condition shall have a value that is the form of a number, an expression, a cell reference, or text that defines which cells will be selected. In the case of text, a condition can consist of any comparison operator followed by the operand against which each cell's value is to be compared. If the text form is used and the text does not begin with a comparison operator, the criteria matches any string starting with that text. [: A criteria of "Pea" can result in Pea, Pear, and Peach's being matched, whereas a criteria of "=Pea" will only match Pea. ] criteria can include one or more wildcard characters, question mark (`?`) and asterisk (`*`). A question mark matches any single character; an asterisk matches any sequence of characters. To search for a question mark, asterisk, or tilde character, prefix that character with a tilde (`~`). The range shall include at least one column label and at least one cell below the column label in which a condition for the column is specified. [: If the range `G1:G2` contains the column label `Income` in `G1` and the amount 10,000 in `G2`, one could define the range as `MatchIncome` and use that name as criteria. ] The value of criteria shall not overlap the range specified by database. To find rows that meet multiple criteria for a single column, all of the criteria shall be specified directly below one another in separate rows of the criteria range. To find rows that meet multiple criteria for multiple columns, all of the criteria shall be specified in the same row of the criteria range. To find rows that meet multiple criteria for multiple columns, where any criteria can be true, each of the criteria shall be specified in a different row of the criteria range. To find rows that meet multiple sets of criteria, where each set includes criteria for multiple columns, each set of criteria shall be specified in a separate row of the criteria range. To find rows that meet multiple sets of criteria, where each set includes criteria for one column, multiple columns with the same column heading shall be included in the criteria range.

Name

Type

Description

database

reference

The range of cells that makes up the list or database, which shall be a list of related data in which rows of related information are records, and columns of data are fields. The first row of the list shall contain labels for each column.

field

text, number

Indicates the column to which criteria shall be applied. It can either be a string containing the column's label, or the column's position number, where columns are numbered starting at 1. [: If column 3's label is "Age" then either 3 or "Age" can be used. ]

criteria

reference

The range of cells that contains the specified conditions. Each cell in that range that contains a condition shall have a value that is the form of a number, an expression, a cell reference, or text that defines which cells will be selected. In the case of text, a condition can consist of any comparison operator followed by the operand against which each cell's value is to be compared. If the text form is used and the text does not begin with a comparison operator, the criteria matches any string starting with that text. [: A criteria of "Pea" can result in Pea, Pear, and Peach's being matched, whereas a criteria of "=Pea" will only match Pea. ]

criteria can include one or more wildcard characters, question mark (?) and asterisk (*). A question mark matches any single character; an asterisk matches any sequence of characters. To search for a question mark, asterisk, or tilde character, prefix that character with a tilde (~).

The range shall include at least one column label and at least one cell below the column label in which a condition for the column is specified. [: If the range G1:G2 contains the column label Income in G1 and the amount 10,000 in G2, one could define the range as MatchIncome and use that name as criteria. ] The value of criteria shall not overlap the range specified by database.

To find rows that meet multiple criteria for a single column, all of the criteria shall be specified directly below one another in separate rows of the criteria range.

To find rows that meet multiple criteria for multiple columns, all of the criteria shall be specified in the same row of the criteria range.

To find rows that meet multiple criteria for multiple columns, where any criteria can be true, each of the criteria shall be specified in a different row of the criteria range.

To find rows that meet multiple sets of criteria, where each set includes criteria for multiple columns, each set of criteria shall be specified in a separate row of the criteria range.

To find rows that meet multiple sets of criteria, where each set includes criteria for one column, multiple columns with the same column heading shall be included in the criteria range.

Return Type and Value: number – The average of the values of the cells that correspond to the specified criteria.

	A	B	C	D	E	F
1	Tree	Height	Age	Yield	Profit	Height
2	=Apple	>10				<16
3	=Pear
4	Tree	Height	Age	Yield	Profit
5	Apple	18	20	14	105.00
6	Pear	12	12	10	96.00
7	Cherry	13	14	9	105.00
8	Apple	14	15	10	75.00
9	Pear	9	8	8	76.80
10	Apple	8	9	6	45.00

the average yield of apple trees over 10 feet in height is computed by DAVERAGE(A4:E10,"Yield",A1:B2), which results in 12

The average age of all trees is computed by DAVERAGE(A4:E10,3,A4:E10), which results in 13]