You can use the following formula to calculate the average in Excel while ignoring zeros and blank cells:
This particular formula calculates the average value in the range B2:B14 and ignores cells that are equal to zero or blank.
Note: Excel ignores blank cells by default when calculating an average.
Thus, we only need to specify in the formula that cells should not be equal ( “<>0”) to zero as well.
The following example shows how to use this formula in practice.
Example: Calculate Average & Ignore Zero and Blank Cells in Excel
Suppose we have the following dataset in Excel that shows the total sales made by various employees at a company:
If we simply used the AVERAGE() formula, we would find the average sales for all of the employees who had a non-blank value:
The average sales per employee who had a non-blank value is 3.
However, suppose we wanted to only calculate the average for employees who had a sales value that was not blank and not equal to zero.
We could type the following formula into cell D2:
The following screenshot shows how to use this formula in practice:
The average sales for employees who had greater than zero sales was 4.125.
This formula calculated the average by only using the values that were not blank and not equal to zero.
We can confirm that this is correct by manually calculating the average of all values that are not blank or equal to zero:
Average of Values Greater than Zero: (10+4+4+3+2+1+4+5) / 8 = 4.125.
This matches the value calculated by our formula.
The following tutorials explain how to perform other common operations in Excel: