Math
How many, and how much on average
Definition
COUNT and AVERAGE can read the same range to tell you how many numbers it holds and what their mean is.
Syntax
=COUNT(B2:B5) and =AVERAGE(B2:B5)
Arguments
| Argument | Meaning |
|---|---|
B2:B5 |
The shared range that both functions read. |
COUNT |
Returns how many cells in the range contain numbers. |
AVERAGE |
Returns the mean of only those numeric cells. |
Worked example
A cyclist logs four rides in kilometres. One ride was skipped and marked with a word. The distances are in B2:B5.
| A: Ride | B: Distance (km) |
|---|---|
| Ride 1 | 21 |
| Ride 2 | skipped |
| Ride 3 | 42 |
| Ride 4 | 60 |
Formula: =COUNT(B2:B5) and =AVERAGE(B2:B5)
Result: 3 and 41. Three cells hold numbers, and 21 + 42 + 60 is 123, which divided by 3 gives 41.
Practice
Cells E2:E5 hold 70, 85, the word absent, and 25. In F2, count the numbers. In F3, average them.
- In F2, write a COUNT formula for E2:E5.
- In F3, write an AVERAGE formula for E2:E5.
Step 1 of 2
Enter =COUNT(E2:E5) in F2.
Enter =AVERAGE(E2:E5) in F3.
Formulas here use English function names, such as SUM, and a period as the decimal separator, in every language.
Loading the sheet…
F2 now shows 3.
F3 now shows 60.
The next lesson is Finding a row by its key.
Related functions
COUNT tells you how many values AVERAGE is dividing by, so reading both together shows whether a mean rests on enough data.
Common mistake
Entering 0 for a missing reading: the zero counts as a number, so COUNT goes up and AVERAGE goes down.