# How to Exclude a Cell in Excel AVERAGE Formula (4 Methods)

In Microsoft Excel, we use the AVERAGE function to determine the average of specific values from a data range. To extract the exact value of the average, we need to know how to exclude a cell in the Excel AVERAGE formula. Sometimes dataset contains zero or blank cells and Excel takes that cell into account to calculate the average value. So, it gives a deviation from the actual value of the average that we intend.

## How to Exclude a Cell in Excel AVERAGE Formula: 4 Methods

In this article, we will explain 4 methods to exclude a cell in the Excel formula based on the AVERAGE function. To illustrate these methods to you we may use different datasets for different methods. We will go over several examples to clarify all the methods in this article.

### 1. Input Argument Manually to Exclude a Cell in Excel AVERAGE Formula

The easiest and simplest method to exclude a cell in the Excel AVERAGE formula is to input the argument manually in the formula. As we input the arguments manually, itâ€™s easy to exclude cells that we donâ€™t want to include in the AVERAGE formula. In the following dataset, we have Sales Amounts for different persons on different days of the week. We can see there are zero and blank cells in the dataset. If we input range B5:E15Â as the argument of the AVERAGE function it will calculate the average considering the zero and blank cells. But we want to excuse zero and blank cells from the calculation of the average.

So, letâ€™s take a look at the steps to input arguments manually to exclude a cell in the Excel AVERAGE formula.

STEPS:

• First, select cell D12 where we want to return the average value.
• Next, insert the following formula in that cell.
`=AVERAGE(C6,E6,C7,D7,E8,D9,C10,E10)`
• To exclude the zero and blank cells hold the Ctrl key and select the cells that you want to take as arguments in the formula.
• After that, press Enter.

• Finally, the above actions give us the average value only for selected cells in cell D12. So, with the manual insertion of arguments, we can easily exclude cells from the AVERAGEÂ formula.

### 2. Ignore Blank/Zero Cells to Exclude a Cell in Excel AVERAGE Formula

In this example, we will see how to deal with zero and blank cells in our Excel worksheet. To clarify the motive of this method, we will calculate the average of the following dataset for two cases. In the first case, we will exclude only the blank cells and will input the calculated average value in cell D13. On the other hand, for the second case, we will exclude both zero and blank cells and input the calculated average value in cell D14.

Now, letâ€™s take a look at the steps to ignore blank/zero cells in the Excel AVERAGE formula. You can also find average with blank cells in Excel.

STEPS:

• Firstly, select cell D13.
• Next, insert the following formula in that cell.
`=AVERAGE(C6:E10)`

• Then, press Enter.

So, we get the average value for the range C6:E10. If we notice the above calculation, the AVERAGE formula excludes the blank cells by default. But it considers cells containing zero. Thatâ€™s why the average value is different from the previous example.

• Next, we will move to our second case. Go to the FileÂ tab.

• Next, select Options.

• Then select the option Advanced from the newly appeared dialogue box.
• Scroll down and uncheck the option Show a zero in cells that have zero value from the section Display options for this worksheet.
• Now, press OK.

• So, we can see this will remove the zero values from the dataset.

• Select cell E14 and insert the following formula in that cell.
`=AVERAGE(C6:E10)`

• Then press Enter.
• Lastly, we can see the result is the same for both cases. So, if we have zero values in the dataset, we have to delete them manually to exclude them from the calculation of the AVERAGEÂ formula.

### 3. Use AVERAGE Formula for Ignoring Error Cells

Sometimes we will have error values in our dataset. If we include those error cells in our Excel formula we will get an error in our result also. In the following dataset, we have three error cells. To clarify this method to you firstly we will calculate the average value for the data range including error cells. Next, we will do the same but this time we will exclude error cells with the IFERROR function.

So, letâ€™s see the steps to use the AVERAGE formula for ignoring error cells in Excel.

STEPS:

• Select cell D13 and insert the following formula.
`=AVERAGE(C6:E10)`
• Then press Enter.

• So, we can see that the above command returns an error in cell D13.

• Next, select cell D14 and insert the following formula.
`=AVERAGE(IFERROR(C6:E10,""))`
• Now, press Enter.

• Finally, the error cells are ignored. We get the average value excluding error cells in cell D14.

🔎 How Does the Formula Work?

• IFERROR(C6:E10,””): This part checks if there are any error values in the data range C6:C10Â and returns values excluding error cells.
• AVERAGE(IFERROR(C6:E10,””)): Returns the average value for the cells in the data range C6:C10Â excluding error cells.

### 4. Combination of Excel SUM and COUNT Functions to Exclude Cells

In this example, we will use a combination of SUM and COUNT functions to exclude a cell in the AVERAGE formula. In the following dataset, we will calculate the average sales for different days of weeks. At the time of calculating the average, we will exclude the cells containing zero values.

STEPS:

• Firstly, select cell C11 and insert the following formula.
`=(SUM(C6:C10)-SMALL(C6:C10,1)-SMALL(C6:C10,2))/(COUNT(C6:C10)-2)`
• Then, press Enter.
• So, the above command returns the average value on Monday in cell C13. If we notice we have excluded the zero cells from our calculation.
• Select cell C13 and drag the Fill Handle tool horizontally to cell E11.
• The above action will copy the formula of cell C13 in cells D13 & E13 and return the average Sales AmountsÂ for Tuesday and Wednesday.

🔎 How Does the Formula Work?

• COUNT(C6:C10)-2): Counts total cell numbers from C6 to C10 and ignores the lowest two cells which are zero cells.
• SUM(C6:C10): Returns the totals for cells C6 to C10.
• SUM(C6:C10)-SMALL(C6:C10,1)-SMALL(C6:C10,2): Here the SMALL function determines the lowest two parts of the range C6:C10. Then the lowest two values are subtracted from the total.
• SUM(C6:C10)-SMALL(C6:C10,1)-SMALL(C6:C10,2))/(COUNT(C6:C10)-2: Returns the average excluding two zero cells.

## Related Articles

<< Go Back to Conditional AverageÂ | Calculate Average | How to Calculate in ExcelÂ | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Mukesh Dipto

Mukesh Dipto is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including Rhino3D, Maxsurf C++, AutoCAD, HTML, CSS, JavaScript, and WordPress. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF