Software

An alternative Excel averaging formula that doesn't evaluate zero

Excel's AVERAGE() function evaluates zero values. If you want to exclude zeroes, use these alternatives.

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 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.

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.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

4 comments
jfuller1212
jfuller1212

doesn't work for columns that happen to have all zeroes, which I need to average to zero....
 - results in a #div/0!  cell

jonc2011
jonc2011

Useful. In my Excel manual it include it as: [b]Ignore zeros when averaging[/b] =AVERAGEIF(range,"0") also ignores empty cells For a formula that that includes empty cells (and ignores zero cells): =SUM(range)/COUNTIF(range,"0")

jfuller1212
jfuller1212

@jonc2011  

doesn't work if some columns happen to have all zeros.  results in a #div/0! cell.

I can't have different formulas for some columns that have numbers and others that happen to have zeros for a 12 month period.....

jonc2011
jonc2011

Can't get the above to include the greater/less than signs

Editor's Picks