Excel 2010 Bible

Part II: Working with Formulas and Functions

382

FIGURE 17.2

The calculated average includes cells that contain a 0.

Two of the sales staff had the week off, however, so including their 0 sales in the calculated average

doesn’t accurately describe the average sales per representative.

Note
The AVERAGE function ignores blank cells, but it does not ignore cells that contain 0.

The following array formula returns the average of the range but excludes the cells containing 0:

{=AVERAGE(IF(B5:B12<>0,B5:B12))}

This formula creates a new array that consists only of the nonzero values in the range. The

AVERAGE function then uses this new array as its argument.

You also can get the same result with a regular (non-array) formula:

=SUM(B5:B12)/COUNTIF(B5:B12,”<>0”)

This formula uses the COUNTIF function to count the number of nonzero values in the range. This

value is divided into the sum of the values.

Note
The only reason to use an array formula to calculate an average that excludes zero values is for compatibility
with versions prior to Excel 2007. A simple approach is to use the AVERAGEIF function in a non-array
formula:

=AVERAGEIF(B5:B12,”<>0”,B5:B12)