In this post we will see how could we calculate average in excel while excluding 0’s.
We will explain the situation a bit more to have a better understanding of what we are talking about here.
In the below data in column A, you could see we have some values as 0 in row number 4,6 and 09 and when we try to calculate the average in excel with the normal average formula , the formula will give us the average of values taking 0’s also into consideration.
As you could see in the below pic, 25 is the sum of all the values in column A and the count of value is 9, so normal average will give as 25/9 which is shown in the cell C3.
Now what if we want to calculate the average in excel excluding zeros.
We will use sibling of average formula in excel which is called AVERAGEIF(range,condition) where range is the range in which the values are placed and the condition is any condition that we would like to provide while calculating average.
So in this case we don’t want to take zeros into consideration we will tell AVERAGEIF formula not to include 0’s in calculating the average,
Enter the formula =AVERAGEIF(A2:A10,”<>0″) in cell B3.
As you could see we have provided the condition as <>0 which will tell excel not to evaluate 0’s while calculating average in excel.
Now in this case the denominator would be 6 and not 9 that we saw in normal average case, and the average would be 25/6.
Likewise you could give any condition and based on the condition excel will exclude those values while calculating average.
If I were to calculate the average only for the positive values, I would write the condition as “>0” .
Hope this helped.
