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

Method 1 – Input Argument Manually to Exclude a Cell in Excel AVERAGE Formula

In the sample 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 exclude zero and blank cells from the calculation of the average.

Input Argument Manually to Exclude a Cell in Excel AVERAGE Formula

STEPS:

  • Select cell D12 where we want to return the average value.
  • 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.
  • Press Enter.

Input Argument Manually to Exclude a Cell in Excel AVERAGE Formula

  • It gives output of the average value only for selected cells in cell D12.


Method 2 – Ignore Blank/Zero Cells to Exclude a Cell in Excel AVERAGE Formula

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

STEPS:

  • Select cell D13.
  • Insert the following formula in that cell.
=AVERAGE(C6:E10)

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

  • Press Enter.

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

It gives the average value for the range C6:E10.

  • Go to the File tab.

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

  • Select Options.

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

  • 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.
  • Press OK.

  • This will remove the zero values from the dataset.

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

  • Press Enter.
  • 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.

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


Method 3 – Use AVERAGE Formula for Ignoring Error Cells

Use AVERAGE Formula for Ignoring Error Cells to exclude a cell in excel average formula

STEPS:

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

Use AVERAGE Formula for Ignoring Error Cells to exclude a cell in excel average formula

  • The command returns an error in cell D13.

  • Select cell D14 and insert the following formula.
=AVERAGE(IFERROR(C6:E10,""))
  • Press Enter.

  • The error cells will now be ignored. We will get the average value excluding error cells in cell D14.

Use AVERAGE Formula for Ignoring Error Cells to exclude a cell in excel average formula

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.

Read More: How to Ignore #N/A Error When Getting Average in Excel


Method 4 – Combination of Excel SUM and COUNT Functions to Exclude Cells

Combination of Excel SUM and COUNT Functions to Exclude Cells

STEPS:

  • Select cell C11 and insert the following formula.
=(SUM(C6:C10)-SMALL(C6:C10,1)-SMALL(C6:C10,2))/(COUNT(C6:C10)-2)
  • Press Enter.

Combination of Excel SUM and COUNT Functions to Exclude Cells

  • The command returns the average value on Monday in cell C13. 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.

Combination of Excel SUM and COUNT Functions to Exclude Cells

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.

Download Practice Workbook


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

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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo