To get the average of a set of numbers, excluding or ignoring zero values, use the AVERAGEIF function.
In the example shown, the formula in E6 is:
How this formula works
AVERAGEIF performs an average based on criteria you supply. In this example, the criteria supplied is “<>0”, which means “not equal to zero”.
Like AVERAGE , AVERAGEIF automatically ignores blank cells and text.