# How to Average Ignore Zero Number

AVERAGE function is a frequently used function in our office work. Except this basic function, Excel also provides some other Average related functions like AVERAGEIF, AVERAGEIFS. In this article, we will show you applying AVERAGEIF function to get average with zero numbers ignored from average range. We will calculate average for the same range by AVERAGE and AVERAGEIF separately, troughing comparing the two results, you can find out the difference between them. In this article, we will introduce you the syntax, arguments, and basic usage of above two functions.

## EXAMPLE There are some numbers saved in range A1:C4. Zero numbers are included in this range. E2 is used for saving the average of given numbers in this range with zero numbers included. F2 is used for saving the average with zero numbers excluded. Through setting proper criteria for AVERAGEIF function, we can get correct average with zero numbers ignored.

## FORMULA with AVERAGE & OFFSET & COUNT FUNCTIONS

In E2, enter the formula =AVERAGE(A1:C4), then press Enter, average of all numbers with zero numbers included is 46.25. In F2, enter the formula =AVERAGEIF(A1:C4,”<>0″), then press Enter, average of numbers with zero numbers excluded is 61.67. You can see that we apply different functions to get average. The difference is AVERAGEIF function can calculate average with condition.

## FUNCTION INTRODUCTION

AVERAGE function returns the average of numbers from a given range reference.

Syntax:

` =AVERAGE(number1, [number2], …)`

AVERAGEIF function returns the average of a set of numbers from a given range based on one condition or criteria. We can split the function as AVERAGE + IF.

Syntax: =AVERAGEIF(range, criteria, [average_range])

EXPLANATION

`=AVERAGEIF(A1:C4,"<>0")`

In this case, a set of numbers are saved in range A1:C4, some zero numbers are included in this range. As we want to calculate average with zero number ignored, so criteria range and average range are the same. For AVERAGEIF function, average range is optional, so if it is omitted, criteria range is equal to average range.

“<>0” is the criteria value in this AVERGAEIF function. “<>” is “not equals to” operator. “<>0” is not equal to 0, so set “<>0” as criteria can make zero numbers excluded in calculation. Actually, some average related functions like AVERAGE, AVERGAEIF and AVERAGEIFS can ignore blank cells and cells contain texts automatically, so if user want to ignore blank cells or cells contain texts, you can directly apply AVERAGE function to get average ignoring these invalid cells. See example below. But AVERAGE function cannot handle errors like #N/A automatically, so we need to apply AVERGAIF or AVERGAIFS function to filter errors by adding proper criteria.

SUMMARY

1.  AVERAGE function is used for returning the average of a set of numbers in Excel.
2. AVERAGEIF function returns the average of a set of numbers refer to one given condition or criteria.
3. AVERAGE/AVERAGEIF/AVERAGEIFS functions can ignore blank cells and cells contain texts.

### Related Functions

• Excel AVERAGE function
The Excel AVERAGE function returns the average of the numbers that you provided.The syntax of the AVERAGE function is as below:=AVERAGE (number1,[number2],…)….
• Excel AVERAGEIF function
The Excel AVERAGEAIF function returns the average of all numbers in a range of cells that meet a given criteria.The syntax of the AVERAGEIF function is as below:= AVERAGEIF (range, criteria, [average_range])….
• Excel AVERAGEIFS function
The Excel AVERAGEAIFS function returns the average of all numbers in a range of cells that meet multiple criteria.The syntax of the AVERAGEIFS function is as below:= AVERAGEIFS (average_range, criteria_range1, criteria1, [criteria_range2, criteria2],…)….

Related Posts
How to Calculate Average If Criteria Not Blank/Ignore Blank Cell

In daily work we often need to calculate the average of some numbers based on given conditions or criteria. To calculate average with criteria, we can apply AVERAGEIF of AVERAGEIFS function. AVERAGEIFS function can handle more than one group of ...

How to Calculate Average Ignore Non-Numeric Values and Errors

In daily work we often need to calculate the average of some numbers in a range for further analysis. Thus, we need to know some basic functions of calculate average in Excel. From now on, we will introduce you some ...

How to Average and Ignore Errors in Excel

This post will guide you how to average a list of values and ignoring any errors in the given range in Excel 2013/2016 or Excel office 365. You can use the AVERAGEIF function or AGGREGATE function to calculate the average ...

How to Calculate Average Ignore Blank and Zero Cells in Excel

Sometimes we need to calculate the average for a list or a range of numbers, but blank cells and zero cells may be also included in the selected range, so if these invalid cells are included, we often get error ...

How to Average Absolute Values in Excel

We can use AVERAGE function to calculate average of certain values. We can use ABS function to get absolute values for both positive number and negative number. If we want to get the average absolute values, we need to combine ...

How to Calculate Average among Multiple Different Worksheets in Excel

If we want to calculate the average for all numbers from multiple worksheets, how can we do calculate? Currently we may know the way to calculate the average for a selected range on one worksheet, this article will introduce you ...

How to Count the Average Between Two Dates in Excel

In our daily work, we usually have the problem of counting the average of a period. If the given dates are continuous, we can handle this situation very well, and if the dates are discontinuous, how do we count the ...

How to Round the Calculated Average result in Excel

This post will guide you how to use average and round functions at the same time to average a given range, and then round off the last result in Excel. How to use Round function to round the calculated average ...

How to Ignore Error Values When Calculating the Average in Excel

This post will guide you how to ignore error values when getting average for a range of cells in Excel. How do I average a range of cells ignoring error values, such as: #div/0!. Assuming that you have a list ...

How to Add Vertical Average Line to Bar Chart in Excel

This post will guide you how to add vertical average line to a bar chart in Excel. How do I add an average line to your bar chart in Excel 2013/2016. Add Vertical Average Line to Bar Chart Assuming that ...

Sidebar