The AVERAGE() function in Excel will include zeros in calculating the mean value of a dataset. In some cases, however, you’ll want to exclude these values from your calculation. In this tutorial, you’ll learn how to calculate an average in Excel while excluding 0. Excel makes calculating an average in Excel while excluding zeros easy using the AVERAGEIF() function.
By the end of this tutorial, you’ll have learned the following:
- How to use the AVERAGEIF() function to calculate an average in Excel ignoring zeros
- How your dataset’s average will change when excluding zeros from a calculation
How to Calculate an Average in Excel Ignoring 0
How can you exclude 0 from an average in Excel?
=AVERAGEIF(range, “<>0”)
In order to calculate an average in Excel while ignoring zeros, you can use the AVERAGEIF() function. In the Excel AVERAGEIF() function, pass in the range of values an use the “<>0” condition.
By default, the Excel AVERAGE() function will include 0 values. However, in some cases, you may not want to include 0s in your calculations. For example, zeros may actually represent missing values.
In order to exclude 0s from your average calculation in Excel, you can use the AVERAGEIF() function. The Excel AVERAGEIF() function accepts two arguments:
range=, the range of values to calculate an average forcriteria=, the criteria to use when selecting values for the average
In this case, we’ll be using criteria to exclude certain values. In particular, we’ll tell Excel to ignore any values that are equal to 0. The syntax for this is a little awkward, in that we pass in a string that indicates ignoring zeros.
Let’s take a look at an example to see how this works:
Learn how to calculate an average in Excel while ignoring zeros
Load a dataset containing the values you want to calculate an average for
Load your dataset into Excel for the data that you want to use. Note that the dataset we’re using has three zeros.
Use the Excel AVERAGEIF() function to calculate the average
Start typing the AVERAGEIF() function in Excel. For the range, we’re using our entire dataset, which is covered in A2:A12.
For the condition, enter “<>0”
Now, for the condition of the function we use "<>0", which means that the value shouldn’t be equal to zero. In this case, Excel will calculate the average but include only values that aren’t equal to 0.
Hit the Return key to complete the calculation
When you hit the Enter key, Excel evaluates the function. In this case, Excel returns the average value of 11.875.
Now that you know how to calculate an average value in Excel while ignoring 0s, let’s take a look at the impact of calculating an average while ignoring any zero values.
Understanding the Impact of Calculating an Average Excluding 0
In the example above, our dataset had 11 values, three of which were zeroes. In this case, that represents over a quarter of our values. Because of this, removing the zeros has a huge impact on the average that we calculate. Let’s take a look at the impact of removing zeros from our average calculation:
- With zeros included: 6.694
- Without zeros: 11.875
We can see that the value of the average is near twice as large when we exclude the zeros from the calculation. Of course, the impact of this is amplified in our dataset because of its small size. If your dataset were, say, 500 records and only three were zeros, the impact would be significantly smaller.
Conclusion
In this tutorial, you learned how to use Excel to calculate an average of a dataset while ignoring the zeros. By default, Excel will include zero values when it calculates an average using the AVERAGE() function. We can use the AVERAGEIF() function, however, to tell Excel to ignore any 0 values. In fact, the AVERAGEIF() function can be used to ignore any type of condition. In this case, we used the condition of "<>0", which tells Excel to include only values that are not equal to zero. You also learned what the impact of excluding zeros is when calculating an average. While the example we used amplified the effect, it did illustrate the effect.
Additional Resources
To learn more about related topics, check out the tutorials below: