AVERAGEIFS
Syntax:
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 |
reference |
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 |
number, expression, reference, text |
Designates the first range of cells to be inspected. |
selection-criteria-1 |
reference, text |
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 |
cell-range-n |
number, expression, reference, text |
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 |
reference, text |
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.
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)]