Math
Averaging numbers in one group
Definition
Averaging the numbers for one group, such as every am row, means giving AVERAGEIF the labels, the group to match, and the numbers.
Syntax
=AVERAGEIF(range, criteria, average_range)
Arguments
| Argument | Meaning |
|---|---|
range |
The column of group labels to scan. |
criteria |
The group to match. Text needs double quotes, for example "am". |
average_range |
The column of numbers to average for the matching rows. |
Worked example
A kennel logs shift labels in A2:A5 and headcounts in B2:B5.
| A: Shift | B: Dogs |
|---|---|
| am | 3 |
| pm | 10 |
| am | 5 |
| am | 7 |
Formula: =AVERAGEIF(A2:A5,"am",B2:B5)
Result: 5. am sits in rows 2, 4, and 5, so (3 + 5 + 7) / 3 equals 5.
Practice
Cells C2:C5 hold yes, no, yes, and yes. Cells D2:D5 hold 4, 9, 8, and 6. In E2, write a formula that averages D2:D5 for rows where C2:C5 equals yes.
- In E2, write an AVERAGEIF formula that averages D2:D5 where C2:C5 equals yes.
- E2 should show the mean of D2:D5 where C2:C5 equals yes.
Step 1 of 2
Enter =AVERAGEIF(C2:C5,"yes",D2:D5) in E2.
yes appears in C2, C4, and C5, so (4 + 8 + 6) / 3 is 6.
Formulas here use English function names, such as SUM, and a period as the decimal separator, in every language.
Loading the sheet…
E2 uses AVERAGEIF.
E2 now shows 6.
The next lesson is Counting rows that pass two tests.
Related functions
AVERAGEIF is the function that averages a number column for rows whose labels match one group.
Common mistake
Leaving out the number column, as in =AVERAGEIF(A2:A5,"am"): the formula then tries to average the labels themselves and cannot return 5.