We can calculate the average that ignores zero in the selected range of cell by using the AVERAGEIF function. In our example, we will calculate the average by adding and ignoring zero and see how results change significantly.
The formula used =AVERAGEIF(A6:C6,”<>0″) has following arguments:
range (A6:C6) – The range of cells to be used to calculate the average.
criteria (“<>0”) – The criteria to average only those values that are not equal to zero.