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.
Prepare below table. There are some zero cells and blank cells. We will ignore them when calculating the average.
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″).
Step 2: Press Enter to get the average. Verify that average is calculated properly.
Actually, excel can calculate the average ignore the blank cells automatically. Zero numbers are still included. See screenshot below.
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.
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.
Step 3: Then you will find that zero value is ‘removed’ from your table. Only blank cells exist now.
Step 4: In E2 enter the formula =AVERAGE(A1:C5). Then press Enter to check the result.
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.
- 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])….