How to get the right average in Excel

Excel's Average() function considers all the entries in the input range and 0s matter. For instance, if the input range is three cells, and one of those cells contains a 0, Excel totals the values and divides by 3. That's probably what you'd expect and what you want.

If you don't want Excel to evaluate 0s, the simplest solution is not to format the cells — but that's not practical. Instead, try an expression in the following form:

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

In fact, you could use this expression to exclude any value, not just 0s. Thanks to Bill Patten for sharing this simple value-excluding averaging expression.

What you might not expect is that Excel ignores an empty cell. If that same cell is empty, Average() divides by 2. That's something to watch for, because it might not be what you want.

About Susan Harkins

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.

Editor's Picks