Ignore null values ​​with Excel AVERAGEIF when searching for means

  1. Ignore null values ​​in data when they reset the average
  2. Ignore zeros when searching for average
  3. AVERAGEIF syntax and additions
  4. Example of ignoring zeros
  5. Open Formula Builder

Ignore null values ​​in data when they reset the average

The AVERAGEIF function makes it easy to find the average value in a range of data that meets a certain criterion. One use of this function is to ignore null values ​​in the data, thus discarding the mean or arithmetic mean when using the normal AVERAGE function. In addition to adding data to a worksheet, null values ​​can result from formula calculations, especially in partial worksheets.

The information in this article applies to Excel 2019, 2016, 2013, 2010, and Excel for Mac.

Ignore zeros when searching for average

The image below shows an example of a function that uses AVERAGEIF to ignore all null values. All functions shown use the same basic formula, changing only the range between the examples. Different results are due to different data used in the formula.

Criteria in the formula to ignore zeros:


AVERAGEIF syntax and additions

Function syntax refers to the layout of the function and includes the function name, parentheses, and arguments. The syntax for the AVERAGEIF function is:

 = AVERAGEIF (диапазон, критерии, средний диапазон) 

Arguments for the AVERAGEIF function:

  • Range (required): The group of cells the function searches to find matches for the criteria argument .
  • criteria (necessary). Indicates whether to average the data in the cell.
  • Average_range (optional): The range of data that will be averaged if the first range meets the specified criteria. If this argument is omitted, the data in the Range . argument is be averaged.

The AVERAGEIF function ignores:

  • Cells in the Average_Range argument that contain boolean (TRUE or FALSE) values.

  • Cells in Average_range that are empty.

If there is no cell in the range does not match the specified criteria, AVERAGEIF returns # DIV/0! error value where all cells in the range are equal to zero. If the Range argument is completely empty or contains only text values, AVERAGEIF also returns # DIV/0! error value.

Example of ignoring zeros

Options for entering the AVERAGEIF function and its arguments include:

  • Enter the complete function in a leaf cell.

  • Selecting a function and its arguments with the Formula Builder.

While it is possible to enter the entire function manually, it is easier to use the dialog box. The dialog box takes care of entering the function’s syntax, such as parentheses and comma separators, as required between arguments.

If the function and its arguments are entered manually, the Criteria . argument is must be in quotation marks, e.g. “0” . If the formula builder is used to enter a function, quotes are added for you.

Open Formula Builder

These are the steps used to enter AVERAGEIF in cell D3 of a sample image using Formula Builder.

  1. Select cell D3 to make it active. Displays the results of the function.

  2. Select formulas .

  3. Select Additional Features > Statistical to open the drop-down list of functions.

  4. Select AVERAGEIF in the list to open Formula Builder.

  5. Select line Range .

  6. Highlight cells A3 C3 on the worksheet to enter that range.

  7. In the Criteria line, type 0 . Average_range left blank because you find the average for the same cells entered for the range argument .

  8. Select Finished to end the function. Answer 5 appears in cell D3.

 = AVERAGEIF (A3: C3, "0") 

Because 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 entire function appears in the formula bar above the worksheet.

Leave a Reply

Your email address will not be published. Required fields are marked *