The use of this function is to return the average of a set of numbers. We can also calculate an excel average ignore zero.

excel average ignore zero

MS Excel is a common application of Microsoft office. A spreadsheet program to save and analyze numerical data, this is MS Excel. This data saves in form of tables. However, the ease of analyzing data on a spreadsheet is the plus point of Excel. Similarly, we can do a variety of numerical and mathematical analyses on Excel. Some of them are sum, average, count, max, min, etc. Calculating the average is an easy analysis in excel. Similarly, it helps in calculating the arithmetic mean of a set of given excel arguments. However, the average function in excel comes under statistical functions. The use of this function is to return the average of a set of numbers. We can also calculate an excel average ignore zero. For example, we can get the average of a set of numbers ignoring zero in excel or excel average without zeros.

How to open MS-Excel?

You can follow the below steps to open MS Excel. Thereby, find out how to excel average ignore zero.

  • First, click on the Start on Desktop.
  • Second, select all programs.
  • Third, the next step is to select the MS Office application.
  • Fourth and finally, choose MS Excel from the MS Office application.

Alternatively, click on the Start icon on your PC. Afterward, type MS Excel where the search option appears. And lastly, press enter. Consequently, MS Excel gets opening on your screen.

Cell and Cell Address

Before jumping to the excel averageif not zero, we have to know about cells and addresses.

  • What is a cell?
  • The excel spreadsheet is a table that contains rows and columns.
  • Accordingly, the rectangular box at the criss-cross points between rows and columns makes a cell. 
  • What is a cell address?
  • While addressing a cell we have to give the cell a cell name.
  • Hence, the name by which a cell gets differentiate from another is a cell address.
  • For Example, if row 2 gets intersects in column D, then the cell address is D2. 

MS Excel: Benefits

MS Excel is one of the most common applications in MS Office. Even in case to find the excel average ignore zero this is useful. Similarly, this is very widely in use due to various purposes like :

  • First, the data is easy to save.
  • Second and finally, adding and removing information is very easy.
  • Hence, with less hard work and without any discomfort, saving data easy.

Given below are some of the benefits of using MS Excel:

  • Easily Storing Data.
  • There is no limit to the amount of information that can save in a spreadsheet.
  • Hence, MS Excel is in wide use to save data or to analyze data.
  • However, filtration of information is very easy and convenient in MS Excel.
  • Easily Retrieve Data.
  • Information written on a piece of paper is difficult or takes longer to find later.
  • However, in the case of an Excel Sheet, it has a different story.
  • On the contrary, finding and retrieving data is easy in Excel.
  • Applying math
  • There is a lot of math formula option in Excel.
  • As a result, making calculations has become easier.
  • Similarly, this also made work in excel more time-saving.
  • Safer and Secure
  • Spreadsheets can be password protected on a laptop or
  • As a result, the chance of losing them is much
  • But while comparing to data in writing on record or paper this is very safe.
  • Data at One Place
  • Previously, data had to be kept in separate files and records while doing paperwork.
  • However, it is more convenient now.
  • Additionally, more than one worksheet can be added to a single MS Excel file.
  • Neatly and clearly visible.
  • As storing data is in tabular forms, consequently, your analysis will be easier.
  • As a result, the information in an Excel table will be more readable and easier to understand.

AVERAGEIF function: Excel

The AVERAGEIF function will find the excel average ignore zero. This function averages the cells based on the criteria provided, based on their syntax. The function syntax is as given below:- =AVERAGEIF (range, criteria, [average_range]) Above, is the base function to find excel average ignore zero where the syntax defines as,

  • Range
  • First, this argument contains the cell range in which the criteria for testing
  • Criteria
  • Second, this argument contains the condition for determining the cells to be average.
  • However, it can be :
  • Numeric or text values.
  • A logical expression.
  • A cell
  • Or other functions.
  • Above, it always contains a condition to meet.
  • Average_range
  • Third and final, this argument consists of the range of cells containing the numbers you want to average.
  • However, this is the optional
  • Similarly, if you omit this argument in the function, the function averages the cells present in the range argument.

Ignoring zeros

  • On calculating excel average excluding 0, you have to average by ignoring the zeros in a range argument.
  • However, you do not need to specify the mean_range argument in the AVERAGEIF function
  • Hence, you only need to provide the range argument and the criteria argument to find the average.
  • Next, you have to test the criterion "not equal to zero" or averageif not zero.
  • Consequently, using the non-equal () logical expression with zero enclosed in double quotation marks ("") we can omit zeros.

For example, you want average non zero excel ignoring Zeros and Blank cells. However, one also needs to understand how it distinct from normal AVERAGE function. Suppose, you have got a range of cells from A1:A11 that has numbers, zeros, and blank cells. By the usage of the AVERAGEIF feature. Again, with the standard expression Not Equal to Zero (“<>0”). Further, you will have an average of the range of numbers ignoring 0s and blank values. The excel average ignore zero formula is as follows:- =AVERAGEIF(A1:A11,"<>0") Above, the formula given will ignore the cells with zero values as per the criterion expression. Similarly, it ignores the blank cells as the basic functionality of the AVERAGEIF. As a result, it will only count the value of range A1 to A11without a zero value. Similarly, it will average if not blank. This gives excel average ignore zero.


As you know, the AVERAGE function ignores blank cells by default. However, it does not ignore zeros. Therefore, to ignore zeros while calculating the average in excel, use the AVERAGEIF. As above, the AVERAGEIF is the most suitable function in excel average ignore zero. Read more: What are the signs of a dying CPU