If you ever get stuck and think that you cannot resolve a certain issue with Excel, just remember that combining formulas can be a great solution to your problem.
Such is the case when dealing with zeroes in Excel. In the example below, we will show how to ignore zeros in the calculation of the average.
Ignore Zero When Calculating the Average
For our example, we will use the list of random numbers:
To calculate the average of this list, we would only need to insert the AVERAGE formula in cell D2 and use our list:
|
1 |
=AVERAGE(A2:B11) |
And will get the following results:
To disregard the zeros that we have in our list, we need to add IF to our formula, and the formula will be:
|
1 |
=AVERAGEIF(A2:B11,"<>0") |
With this condition added, we will get the following results:
Having in mind that the average is a quotient of the SUM of a certain range and the COUNT of that same range, we can use these two formulas together with the condition and calculate our average.
The formula in cell F2 will be as follows:
|
1 |
=SUMIF(A2:B11,"<>0")/COUNTIF(A2:B11,"<>0") |
And will end up with the same result:
