[Table of Contents] [docx version]
SpreadsheetML Reference Material - Table of Contents
AVERAGEIFS ( average-range , cell-range-1 , selection-criteria-1
[ , cell-range-2 , selection-criteria-2 [ , … ] ] )
Description: The average of the values of all cells that meet multiple criteria.
Arguments:
|
Name |
Type |
Description |
|
average-range |
Designates the cells whose values are averaged. In this case, average-range need not have the same size and shape as cell-range-1 through cell-range-n. The actual cells that are added are determined by using the top, left cell in average-range as the beginning cell, and then including cells that correspond in size and shape to cell-range-1 through cell-range-n. If a cell in average-range is empty, that cell is ignored. Each cell in average-range is used in the average calculation only if all of the corresponding criteria specified are true for that cell. | |
|
cell-range-1 |
Designates the first range of cells to be inspected. | |
|
selection-criteria-1 |
selection-criteria-1 specifies the criteria for the first range of cells that will be averaged. In the case of text, selection-criteria-1 can consist of any comparison operator followed by the operand against which each cell's value is to be compared. If a cell in any selection criteria range is empty, it is treated as if its value was 0. Cells that contain TRUE evaluate to 1; cells in any range that contain FALSE evaluate to 0. selection-criteria-1 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 (~). | |
|
cell-range-n |
The optional arguments selection-criteria-2 through selection-criteria-n have corresponding arguments cell-range-2 through cell-range-n, and have the same semantics as selection-criteria-1 and cell-range-1, respectively.
| |
|
selection-criteria-n |
Return Type and Value: number – The average of the cells corresponding to those selected.
However, if
• Cells in average-range are empty or contain text values that cannot be translated into numbers, the return value is unspecified.
• There are no cells that meet all the criteria, the return value is unspecified.
[Example: Given the following data:
|
|
A |
B |
C |
D |
|
1 |
Student |
First Quiz Grade |
Second Quiz Grade |
Final Exam Grade |
|
2 |
Emilio |
75 |
85 |
87 |
|
3 |
Julie |
94 |
80 |
88 |
|
4 |
Hans |
86 |
93 |
Incomplete |
|
5 |
Frederique |
Incomplete |
75 |
75 |
AVERAGEIFS(B2:B5,B2:B5,">70",B2:B5,"<90") results in 80.5 (the average for all students all first quiz grades that are between 70 and 90)
AVERAGEIFS(D2:D5,D2:D5,"<>Incomplete",D2:D5,">80") results in 87.5 (the average for all students all first quiz grades that are above 80 and not marked "Incomplete")
AVERAGEIFS(B2:D5,B2:B5,"<>Incomplete",C2:C5,"<>Incomplete",D2:D5, "<>Incomplete") results in 82.375 (the average grades for all students who do not have incomplete grades)
end example]