Ignore Zero Values With Excel AVERAGEIF When Finding Averages

The AVERAGEIF function makes it easier to find the average value in a range of data that meets a specified criterion. One 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 below image contains an example function that uses AVERAGEIF to ignore all zero values. All shown functions use the same basic formula with only the range changing between examples. The different results are due to the different data used in the formula.

The criterion in the formula that allows zeros to be ignored is:

"<>0"

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 searches to find matches for the Criteria argument.
• 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 also returns the #DIV/0! error value.

Ignore Zeros Example

Options for entering the AVERAGEIF function and its arguments include:

• Typing the complete function into a worksheet cell.
• Selecting the function and its arguments using the Formula Builder.

Although it is possible to enter the complete function manually, it's easier to use the dialog box. The dialog box 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, for example "<> 0". If the Formula Builder is used to enter the function, it adds the quotation marks for you.

Opening the Formula Builder

Here are the steps used to enter AVERAGEIF into cell D3 of the example image using the Formula Builder.

1. Select cell D3 to make it the active cell. This is the location where the function results display.

2. Select Formulas.

3. Choose More Functions > Statistical to open the function drop-down list.

4. Select AVERAGEIF in the list to bring up the Formula Builder.

5. Select the Range line.

6. Highlight cells A3 to C3 in the worksheet to enter this range.

7. On the Criteria line, 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. Select Done to complete the function. The answer 5 appears in cell D3.

= AVERAGEIF( A3:C3, "<>0" )

Since the function ignores the zero value in cell B3, the average of the remaining two cells is 5 ((4+6)/2 = 10). If you select cell D8 of the example, the complete function appears in the formula bar above the worksheet.