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:A10is 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:
| Day | Sales |
|---|---|
| Monday | 500 |
| Tuesday | 700 |
| Wednesday | 0 |
| Thursday | 600 |
| Friday | 800 |
| Saturday | 0 |
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:B10contains the sales values to average.A2:A10contains 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.
| Product | Sales |
|---|---|
| Apple | 500 |
| Banana | 300 |
| Apple | 700 |
| Apple | 0 |
| Banana | 400 |
| Apple | 600 |
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:A10is the original data range.A2:A10<>0checks which values are not equal to zero.FILTERreturns only the values that meet this condition.AVERAGEcalculates the average of the filtered results.
Practical Example
Suppose your spreadsheet contains these numbers:
| Employee | Monthly Sales |
|---|---|
| Employee 1 | 1,000 |
| Employee 2 | 0 |
| Employee 3 | 1,500 |
| Employee 4 | 2,000 |
| Employee 5 | 0 |
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:
| Number | Value |
|---|---|
| 1 | 100 |
| 2 | 200 |
| 3 | 0 |
| 4 | 300 |
| 5 | 400 |
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 Formula | Best Used For | Excludes Zeros? |
|---|---|---|
AVERAGEIF | Simple average without zeros | Yes |
AVERAGEIFS | Average with multiple conditions | Yes |
AVERAGE(FILTER()) | Dynamic filtering in modern Excel | Yes |
SUMIF/COUNTIF | Manual average calculation | Yes |
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