Prem Rawat

Peace is Possible

— Prem Rawat

True peace begins within you. Discover the peace that already exists inside yourself, and let it bring more love, happiness and understanding into your life.

Visit YouTube Channel

How to Calculate Average Without Zeros in Excel (4 Easy Methods)

Learn how to calculate average without zeros in Excel using AVERAGEIF, AVERAGEIFS, FILTER, and SUMIF formulas with practical examples.

 

How to Calculate Average Without Zeros in Excel (4 Easy Methods)

   Want to calculate an average without including zero values in Excel? Learn four easy methods using AVERAGEIF, AVERAGEIFS, FILTER, and SUMIF with practical examples and step-by-step formulas.

How to Calculate Average Without Zeros in Excel

When working with numbers in Excel, calculating an average seems straightforward. You simply add the values and divide the total by the number of entries. However, things become a little different when your data contains zeros.

In many spreadsheets, zero represents an actual value, but sometimes it is used as a placeholder for missing information, incomplete records, or days when no activity occurred. Including these zeros in an average can bring the result down and give you a misleading picture of your data.

For example, imagine you are calculating the average daily sales of a shop. Your sales figures are $500, $700, $0, $600, and $800. If the zero represents a day with no recorded sales rather than an actual zero-sales day, including it will reduce the calculated average.

Fortunately, Excel provides a simple solution. You can use the AVERAGEIF function to calculate an average while excluding zeros automatically.

In this guide, you will learn different ways to calculate an average without zeros, understand the formulas, and explore practical examples you can use in everyday spreadsheets.

Why Should You Calculate an Average Without Zeros?

Before choosing a formula, it is important to understand why zeros sometimes need to be excluded.

Consider a spreadsheet containing monthly sales data. Some cells may contain zero because sales information has not yet been entered. If Excel includes those zeros in the calculation, your average may not accurately represent the available sales figures.

Here are some common situations where excluding zeros can be useful:

  • Sales reports: Calculate average sales without including days with missing sales records.

  • Student results: Find the average marks without including placeholder zeros.

  • Employee performance: Calculate average productivity based on recorded performance.

  • Business analysis: Find the average revenue from days with actual transactions.

  • Survey results: Calculate average responses while excluding zero placeholders.

However, remember that a zero can also be a genuine result. If a business actually made no sales on a particular day, excluding that zero may make the average look higher than the real daily average. Always decide whether zero represents missing information or a valid measurement before removing it.

Method 1: Use the AVERAGEIF Function to Ignore Zeros

The easiest way to calculate an average without zeros in Excel is by using the AVERAGEIF function.

This function calculates the average of cells that meet a particular condition. You can simply tell Excel to include only values greater than zero.

Formula:

=AVERAGEIF(A2:A10,">0")

How this formula works:

  • A2:A10 is the range containing your numbers.

  • ">0" tells Excel to include only values greater than zero.

  • Zero and negative values are excluded from the calculation.

Practical Example

Suppose you have the following daily sales figures in Excel:

DaySales
Monday500
Tuesday700
Wednesday0
Thursday600
Friday800
Saturday0

To calculate the average sales without including zero values, enter this formula:

=AVERAGEIF(B2:B7,">0")

Result: $650

Excel calculates the average using only four values:

[
\frac{500+700+600+800}{4}=650
]

The two zero values are ignored automatically.

This is usually the best method when you want a simple formula that excludes zeros from an average.

Method 2: Use the AVERAGEIFS Function to Exclude Zeros

If you want to calculate an average using multiple conditions, the AVERAGEIFS function is another useful option.

For example, you may want to calculate the average sales for a particular product while ignoring zero values.

Formula:

=AVERAGEIFS(B2:B10,A2:A10,"Apple",B2:B10,">0")

How this formula works:

  • B2:B10 contains the sales values to average.

  • A2:A10 contains product names.

  • "Apple" tells Excel to include only Apple sales.

  • B2:B10,">0" excludes zero and negative sales values.

Practical Example

Imagine your spreadsheet contains sales records for different products.

ProductSales
Apple500
Banana300
Apple700
Apple0
Banana400
Apple600

To find the average Apple sales without zeros, use:

=AVERAGEIFS(B2:B7,A2:A7,"Apple",B2:B7,">0")

Result: $600

Excel includes 500, 700, and 600 while ignoring the zero and all Banana records.

This method is particularly helpful when working with larger business reports containing multiple products, departments, employees, or categories.

Method 3: Use the AVERAGE Function with FILTER

If you are using Microsoft 365 or Excel 2021 and later, the FILTER function provides another flexible way to exclude zeros.

Formula:

=AVERAGE(FILTER(A2:A10,A2:A10<>0))

How this formula works:

  • A2:A10 is the original data range.

  • A2:A10<>0 checks which values are not equal to zero.

  • FILTER returns only the values that meet this condition.

  • AVERAGE calculates the average of the filtered results.

Practical Example

Suppose your spreadsheet contains these numbers:

EmployeeMonthly Sales
Employee 11,000
Employee 20
Employee 31,500
Employee 42,000
Employee 50

Enter this formula:

=AVERAGE(FILTER(B2:B6,B2:B6<>0))

Result: 1,500

Excel filters out the zero values before calculating the average.

One advantage of this method is that you can easily modify the FILTER condition to handle more complicated requirements.

Important: If every value is zero, FILTER returns an empty result and AVERAGE cannot calculate a valid average. You can prevent an error by using:

=IFERROR(AVERAGE(FILTER(A2:A10,A2:A10<>0)),0)

This formula returns zero when there are no qualifying values.

Method 4: Use SUMIF and COUNTIF to Calculate an Average Without Zeros

You can also calculate an average without zeros by combining the SUMIF and COUNTIF functions.

This approach is useful when you want to understand exactly how Excel calculates the average.

Formula:

=SUMIF(A2:A10,"<>0")/COUNTIF(A2:A10,"<>0")

How this formula works:

  • SUMIF(A2:A10,"<>0") adds all values that are not zero.

  • COUNTIF(A2:A10,"<>0") counts the cells that are not zero.

  • The division calculates the average of the qualifying values.

Practical Example

Suppose your data contains the following numbers:

NumberValue
1100
2200
30
4300
5400

Use this formula:

=SUMIF(B2:B6,"<>0")/COUNTIF(B2:B6,"<>0")

Result: 250

Excel adds the four non-zero values and divides their total by four.

If all the cells contain zeros or are blank, this formula can return a division error. To handle that situation, use:

=IFERROR(SUMIF(A2:A10,"<>0")/COUNTIF(A2:A10,"<>0"),0)

Comparison of Excel Formulas for Averaging Without Zeros

Excel offers several ways to calculate an average while excluding zero values. The best choice depends on your spreadsheet and the type of calculation you need.

Excel FormulaBest Used ForExcludes Zeros?
AVERAGEIFSimple average without zerosYes
AVERAGEIFSAverage with multiple conditionsYes
AVERAGE(FILTER())Dynamic filtering in modern ExcelYes
SUMIF/COUNTIFManual average calculationYes

For most everyday calculations, AVERAGEIF is the simplest and most convenient option. If you need additional conditions, AVERAGEIFS is usually a better choice.

Important Things to Remember When Excluding Zeros in Excel

Although excluding zeros is straightforward, a few details can affect your results.

Zero and blank cells are different

Excel generally ignores blank cells when calculating an average. However, a cell containing zero is treated as a numerical value.

For example, the AVERAGE function includes zeros but ignores genuinely empty cells.

Negative numbers are also excluded by the greater-than-zero condition

Consider this formula:

=AVERAGEIF(A2:A10,">0")

It excludes both zero and negative values.

If you want to exclude only zeros while keeping negative numbers, use:

=AVERAGEIF(A2:A10,"<>0")

AVERAGEIF can include positive and negative values

The "< >0" condition (written without the space in Excel as "<>0") tells Excel to include any number other than zero.

Check whether zero represents missing data

If zero is a genuine measurement, removing it can distort your results. For example, if you are calculating average daily profit and a business genuinely made no profit on a particular day, that zero should normally remain part of the calculation.

Handle ranges containing only zeros

If there are no qualifying numbers, Excel cannot calculate a meaningful average. Consider using IFERROR to display zero or another suitable message instead of an error.

Frequently Asked Questions (FAQs)

How do I calculate an average in Excel without zeros?

Use the AVERAGEIF function:

=AVERAGEIF(A2:A10,">0")

It calculates the average using only positive numbers.

Which Excel function ignores zero values?

AVERAGEIF and AVERAGEIFS can exclude zeros by using the ">0" or "<>0" criteria.

Does the AVERAGE function ignore zeros automatically?

No. The AVERAGE function includes zero values in its calculation but ignores blank cells and text values in referenced cells.

How do I exclude zeros but include negative numbers?

Use this formula:

=AVERAGEIF(A2:A10,"<>0")

It excludes zeros while including both positive and negative numbers.

How do I calculate an average without zeros and avoid errors?

Use the following formula:

=IFERROR(AVERAGEIF(A2:A10,">0"),0)

It returns zero instead of an error when there are no qualifying positive values.

Final Thoughts

Calculating an average without zeros in Excel is simple once you know which function to use. For most spreadsheets, the AVERAGEIF function is the quickest solution:

=AVERAGEIF(A2:A10,">0")

If your calculation requires multiple conditions, use AVERAGEIFS. For more flexible filtering, try combining FILTER with AVERAGE.

The most important thing is to understand what zero represents in your data. Excluding zeros can make an average more meaningful when they represent missing information, but it can produce misleading results when they are genuine measurements.

Choose the formula that matches your data, and you can create more accurate Excel reports without manually removing zero values.

COMMENTS

Loaded All Posts Not found any posts VIEW ALL Readmore Reply Cancel reply Delete By Home PAGES POSTS View All RECOMMENDED FOR YOU LABEL ARCHIVE SEARCH ALL POSTS Not found any post match with your request Back Home Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sun Mon Tue Wed Thu Fri Sat January February March April May June July August September October November December Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec just now 1 minute ago $$1$$ minutes ago 1 hour ago $$1$$ hours ago Yesterday $$1$$ days ago $$1$$ weeks ago more than 5 weeks ago Followers Follow THIS PREMIUM CONTENT IS LOCKED STEP 1: Share to a social network STEP 2: Click the link on your social network Copy All Code Select All Code All codes were copied to your clipboard Can not copy the codes / texts, please press [CTRL]+[C] (or CMD+C with Mac) to copy Table of Content