[Table of Contents] [docx version]
SpreadsheetML Reference Material - Table of Contents
DAVERAGE ( database , field , criteria )
Description: Averages the values in a column of a list or database that match the specified criteria.
[Note: In order to perform an operation on an entire column in a database, a blank line must be entered below the column labels in the criteria range. end note]
Arguments:
|
Name |
Type |
Description |
|
database |
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. | |
|
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. [Example: If column 3's label is "Age" then either 3 or "Age" can be used. end example] | |
|
criteria |
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. [Example: A criteria of "Pea" can result in Pea, Pear, and Peach's being matched, whereas a criteria of "=Pea" will only match Pea. end example] 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. [Example: 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. end example] 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.
[Example: Given the following data:
|
|
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
end example]