An alternative Excel averaging formula that doesn’t evaluate zero

Excel’s AVERAGE() function considers zero values in its calculations, which is probably what you’d expect, but not necessarily what you want. Over the years, I’ve seen many formulas that ignore zero value and most are more convoluted than they need to be.

If you’re using Excel 2007 or 2010, the solution is much easier than it used to be, thanks to the new AVERAGEIF() function. This function uses the following syntax to return an average, based on a condition:

AVERAGEIF(average,criteria,averagerange)

The first two arguments are required; average is a reference to the cells you want averaged andcriteria is the expression the referenced values must satisfy. To exclude zero values, you’d use the criteria <>0. Specifically, the function in C6, =AVERAGEIF(B2:B5,”<>0″),  averages the values in  B2:B5 only if they don’t equal 0. Column B uses the traditional AVERAGE(), which includes zero. Column A uses the formula, =(A2+A3+A4+A5)/4, to find the average.

Be careful when choosing AVERAGEIF() because it ignores empty cells and that might not be what you want either.

If you need a formula that that doesn’t ignore empty cells or if you’re still using Excel 2003, you can use a formula in the following form:

=SUM(range)/COUNTIF(range,"<>0")

There are other formulas, including an array (my least favorite solution). The key is to find the right solution for the problem, and as I’ve shown, the functions and formulas have behaviors that you might not want, so be wary.

Courtesy: Tech Republic