LyteByte

Sometimes it may be needed to calculate the average of a column of cells excluding all the zeros in any of the cells. It may be little tricky for novice Excel users if they don’t know how to use multiple formulas in conjunction. Though we can use same method in both Excel 2003 and Excel 2007, the later gives much easier new features that we can use.

So, lets see how to Average Non-zero values in Excel 2003 and Excel 2007?

First lets see how to calculate non-zero average in Excel 2003.

Assume we have to take non-zero average of cells from A1 to A9. We normally use =AVERAGE(A1:A9) to calculate regular average. This will take zeros for calculation and will return wrong result.

You can use two formulas (SUM and COUNTIF) together to get the desired result. =SUM(A1:A9)/COUNTIF(A1:A9,"<>0") takes the sum of all cells from A1 to A9 but counts only cells if they are not equal to zero. You can also change the formula to use SUMIF and COUNTIF with “>0” etc.

Another method is by using the Array Formula or CSE formula. This helps to implement AVERAGEIF feature which is not available in Excel 2003. {=AVERAGE(IF(A1:A9>0,A1:A9))}

Note: All the array formula must be started and ended with curly braces {}, you can get the braces by pressing CTRL + SHIFT + ENTER, rather than pressing just Enter alone after entering the formula. We’ll learn more about Array Formulas in another post soon.

In Excel from Office 2007, you can also use all the same formula as in Excel 2003. But you can also take advantage of new formulas available like built-in AVERAGEIF. To achieve non-zero average, we can use the formula, =AVERAGEIF(A1:A9,"<>0",A1:A9)

Note: In the images above, the condition is written as “>0” which will remove any negative numbers as well. In that case you may be required to use the condition “<>0”.