Sometimes we may want to exclude zero values in the calculation of the average of a range. This tutorial shows how to find the average of a range ignoring zeroes.
Example
We use the following dataset in our illustration.
The AVERAGE function includes zero values in its calculation. Confirm this by doing the following:
- Select cell B7 and type in the following formula:
|
1 |
=AVERAGE(B2:B6) |
- Press the Enter key.
The AVERAGE function returns an average of 12. The function divided the sum of 60 (20 + 15 + 0 + 25 + 0) by 5 (the number of values in the range).
How to Exclude Zero Values in the Average
To exclude zero values in the calculation of the average, we have to use the AVERAGEIF function. We use the following steps:
- Select cell B8 and type in the following formula:
|
1 |
=AVERAGEIF(B2:B6, ">0") |
- Press the Enter key.
The AVERAGEIF function returns an average of 20. The function divided the sum of 60 (20 + 15 + 0 + 25 + 0) by 3 (the number of values in the range that are greater than zero).
Conclusion
The AVERAGE function includes zero values in its calculations. This tutorial showed how to use the AVERAGEIF function to find the average of a range ignoring zeroes.