Using the standard AVERAGE function with data that contains zeros can give you an incorrect average. Instead, we can use the AVERAGEIF function to take the average of non-zero cells.
Contents:
Formula
To take the average of all non-zero cells in a given range, we can use the following formula:
= AVERAGEIF(range, "<>0")Replacing the range with the range of cells we would like to take the average of.
Explanation
The AVERAGEIF function is a conditional formula in Excel that calculates the average of cells that meet a specified criterion.
The function has two parts, the Range and the Criteria.
Range refers to the range of cells that you want to include in the average calculation. This could be anything from a single cell, a row or column, or a 2-D array of cells (rows and columns selected)
Criteria is the condition that the cells must meet to be included in the average calculation. In this case, we used "<>0" to specify that all cells that are not equal to zero should be included in the average.
So, the formula takes the specified range of cells and evaluates each cell to see if it meets the specified criteria. If a cell meets the criteria, its value is included in the average calculation. If a cell does not meet the criteria, it is ignored, leaving us with the average of all the values that meet the criteria.