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 when calculating the average. Above all, in this article, we will introduce you to apply formula to calculate average ignore the annoying cells. Refer to article below, you can get correct average for the selected range.

Precondition:

Prepare below table. There are some zero cells and blank cells. We will ignore them when calculating the average.

How to Calculate Average Ignore Blank and Zero Cells in Excel1

Method 1: Calculate Average Ignore Blank and Zero Cells by Formula


This is the most convenient way to calculate the average for selected range without zero and blank cells.

Step 1: Select a blank cell, for example E2, enter the formula =AVERAGEIF(A1:C5,”<>0″).

How to Calculate Average Ignore Blank and Zero Cells in Excel2

Step 2: Press Enter to get the average. Verify that average is calculated properly.

How to Calculate Average Ignore Blank and Zero Cells in Excel3

NOTICE:

Actually, excel can calculate the average ignore the blank cells automatically. Zero numbers are still included. See screenshot below.

How to Calculate Average Ignore Blank and Zero Cells in Excel4

So, you may think if we remove all zero values and left them as blank cells, then we can use AVERAGE function directly without other index or parameter. Well, if you remove them totally, you can apply AVERAGE function directly, but, if you just make these zero numbers invisible in the table, you may get improper value by formula. See example below:

Step 1: Click File (it is adjacent to Home) in the ribbon, then select Options to enter Excel Options window.

How to Calculate Average Ignore Blank and Zero Cells in Excel5

Step 2: On Excel Options window, in the left panel, click Advanced, then drag the scrollbar to the middle, under Display options for this worksheet, uncheck ‘Show a zero in cells that have zero value’ option. Then click OK.

How to Calculate Average Ignore Blank and Zero Cells in Excel6

Step 3: Then you will find that zero value is ‘removed’ from your table. Only blank cells exist now.

How to Calculate Average Ignore Blank and Zero Cells in Excel7

Step 4: In E2 enter the formula =AVERAGE(A1:C5). Then press Enter to check the result.

How to Calculate Average Ignore Blank and Zero Cells in Excel8

See, we still get the average with zero included. So, you cannot apply AVERAGE function without zero cells by disable showing them in the table.

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])….
Related Posts

How to Average Ignore Zero Number
How to Average Ignore Zero Number 5

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 ...

How to Calculate Average If Criteria Not Blank/Ignore Blank Cell
How to Calculate Average If Criteria Not Blank 11

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
How to Calculate Average Ignore Non-Numeric Values and Errors 8

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
average and ignore errors2

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 Average Absolute Values in Excel
How to Average Absolute Values in Excel5

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
Calculate Average among Multiple Different Worksheets 8

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
Count the Average Between Two Dates 11

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
round average function3

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
average cells ignore error3

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
add vertical average line to chart15

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