Ignore Zeros when Finding the Average in Excel

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

Ignore Zeros when Finding the Average with Excel's AVEREAGEIF Function
Ignore Zeros when Finding the Average with Excel's AVEREAGEIF Function. © 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 meets a specified criterion.

One such use for the function is to have it to 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 image above 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 obtain 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 - as shown in the examples in the image above.

The AVERAGEIF function ignores:

  • Cells in the Average_range argument that contain Boolean (TRUE or FALSE) values - row 5 above; 

  • Cells in the Average_range that are empty - row 4 above;

Note:

  • If no cells in Range meet the identified criteria, AVERAGEIF returns the #DIV/0! error value - row 7 in the example, 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.- rows 6 and 8 above;

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 just enter the complete function manually, many people find it easier to use the dialog box as it takes care of entering the function's syntax - such as brackets and the comma separators required between arguments.

In addition, 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 will add the quotation marks for you.

Listed below are the steps used to enter AVERAGEIF into cell D3 of the example above using the function's dialog box.

Opening the AVERAGEIF 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 list;
  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;
  8. Note: The Average_range is left blank since we are finding the average value for the same cells entered for the Range argument;
  9. Click OK to close the dialog box and return to the worksheet;
  10. The answer 5 should appear in cell D3;
  11. Since the function ignores the zero value in cell B3, the average of the remaining two cells is 5 : (4+6) /2 = 10;
  1. If you click on cell D8 the complete function = AVERAGEIF( A3:C3, "<>0" ) appears in the formula bar above the worksheet.