Formulla Utilized To Average In Microsoft Excel Excluding Zeroes

Average In Excel

Average In Excel 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 and criteria 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.

install office

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

install office

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")

office setup

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.

Blogs : install office