1. Computing

Ignore Zeros when Finding the Average

Excel AVERAGEIF Function Tutorial


Excel AVERAGE Function - Ignore Zeros

Excel AVERAGE Function - Ignore Zeros

© Ted French

AVERAGEIF Function Overview

The AVERAGEIF function was added in Excel 2007 to make it easier to find the average value in a range of data that meet certain criteria.

One handy use for the function is to ignore zero values in your data that can throw off the average.

AVERAGEIF Function Syntax and Augments

The syntax for the AVERAGEIF function is:

=AVERAGEIF( Range, Criteria, Average_range)

The arguments for the AVERAGEIF function are:

Range - (required) the group of cells the function is to search

Criteria - (required) determines whether the data in a cell is to be averaged or not

Average_range - (optional) the data range that is averaged if the first range meets the specified criteria. If this range is omitted, the first range is averaged instead.

In this example, we will omit the Average_range argument since the range we want to average is the same one that we will search for zero values.

Example: Ignore Zeros when Finding the Average

In this example we will find the average value for data in the range D1 to D7 that do not equal zero.

To do this we will set the criteria argument to ignore all zero values in the range.

  1. Enter the following data into cells D1 to D7: 10, 20, 0, 30, 0, 40

  2. Click on cell D8 - the location where the function results will be displayed

  3. Click on the Formulas tab of the ribbon

  4. Choose More Functions > Statistical from the ribbon to open the function drop down list

  5. Click on AVERAGEIF in the list to bring up the function's dialog box

  6. In the dialog box, click on the Range line

  7. Drag select cells D1 to D7 in the worksheet to enter this range into the dialog box

  8. On the Criteria line in the dialog box, type 0

  9. Note: the "" character is the does not equal symbol in Excel and it is created by typing the angle brackets - located in the bottom right corner of the keyboard - back to back

  10. Click OK to close the dialog box and complete the function

  11. The answer 25 should appear in cell D8

  12. Since the function ignores the zeros in cells D3, D5, and D7, the average of the remaining four cells is 25 : (10+20+30+40) /4 = 25

  13. If you click on cell D8 the complete function = AVERAGEIF( D1:D7, "0" ) appears in the formula bar above the worksheet

©2014 About.com. All rights reserved.