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.

Tutorial Content

## 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.