Use Excel's AVERAGEIF to Ignore Zero Values When Finding the Average

Ignore Zero Values in Data When They Throw off the Average

Ignore Zeros when Finding the Average with Excel's AVEREAGEIF Function
© Ted French

The AVERAGEIF function was added in Excel 2007 to make it easier to find the average value in a range of data that meets a specified criterion. One such use for the function is to have it ignore zero values in data that throw off the average or arithmetic mean when using the regular AVERAGE function. In addition to data that is added to a worksheet, zero values can be the result of formula calculations, especially in incomplete worksheets.

Ignore Zeros When Finding the Average

The example image contains a formula using AVERAGEIF that ignores zero values. The criterion in the formula that does this is  "<> 0".

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.

 The examples in the image all use the same basic formula; only the range changes. The different results obtained are due to the different data used in the formula.

AVERAGEIF Function Syntax and Augments

A function's syntax refers to the layout of the function and includes the function's name, brackets, and arguments. 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 will search to find matches for the Criteria argument below.
  • 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 argument is omitted, the data in the Range argument is averaged instead.

The AVERAGEIF function ignores:

  • Cells in the Average_range argument that contain Boolean (TRUE or FALSE) values.
  • Cells in the Average_range that are empty.

If no cells in Range meet the identified criteria, AVERAGEIF returns the #DIV/0! error value, where all cells in Range are equal to zero.

If the Range argument is completely blank or contains only text values, AVERAGEIF returns the #DIV0! error value.

Ignore Zeros Example

Options for entering the AVERAGEIF function and its arguments include:

  1. Typing the complete function, such as: = AVERAGEIF( A3:C3, "<>0" ) into a worksheet cell
  2. Selecting the function and its arguments using the AVERAGEIF function dialog box

Although it is possible to enter the complete function manually, many people find it easier to use the dialog box because it takes care of entering the function's syntax, such as brackets and the comma separators required between arguments.

Also, if the function and its arguments are entered manually, the Criteria argument must be surrounded by quotation marks: "<> 0". If the dialog box is used to enter the function, it adds the quotation marks for you.

Opening the AVERAGEIF Dialog Box

Here are the steps used to enter AVERAGEIF into cell D3 of the example image using the function's dialog box.

  1. Click on cell D3 to make it the active cell — the location where the function results will be displayed.
  2. Click on the Formulas tab of the ribbon.
  3. Choose More Functions > Statistical from the ribbon to open the function drop-down menu.
  4. Click on AVERAGEIF in the list to bring up the function's dialog box.
  5. In the dialog box, click on the Range line.
  6. Highlight cells A3 to C3 in the worksheet to enter this range into the dialog box;
  7. On the Criteria line in the dialog box, type: <> 0. The Average_range is left blank because you are finding the average value for the same cells entered for the Range argument.
  8. Click OK to close the dialog box and return to the worksheet.
  9. The answer 5 appears in cell D3.
  10. Since the function ignores the zero value in cell B3, the average of the remaining two cells is 5 : (4+6)/2 = 10.
  11. If you click on cell D8 of the example, the complete function = AVERAGEIF( A3:C3, "<>0" ) appears in the formula bar above the worksheet.