Excel’s AVERAGE function includes zero values by default. When zeros in a dataset represent missing observations rather than actual measurements — a day with no sales recorded, a meter that failed to report — including them in the mean understates the true value. The fix is AVERAGEIF(range,"<>0"), which restricts the calculation to non-zero numeric cells.
This article covers the AVERAGE family of functions with corrected syntax, the cases where each one is appropriate, and the pitfalls in deciding whether a zero is data or absence-of-data.
Why zeros distort averages
Consider five days of sales: $100, $150, $0, $200, $0. If the two zeros represent days the store was closed (no sale possible), the operational average daily sales is $150 across three open days. If the two zeros are included as legitimate measurements, the calculated average drops to $90 — a 40% understatement.
The decision rule is whether the zero represents a measurement worth counting or an absence of data. There is no single right answer; it depends on the question being asked. AVERAGE includes zeros; AVERAGEIF with "<>0" excludes them.
AVERAGE: the baseline function
Syntax:
=AVERAGE(number1, [number2], ...)Typical usage with a contiguous range:
=AVERAGE(A1:A10)This calculates the arithmetic mean of the 10 cells A1 through A10. AVERAGE skips blanks and text cells automatically but includes zero values. Up to 255 individual numbers, references, or ranges can be passed as arguments.
AVERAGEIF: a single criterion
Syntax:
=AVERAGEIF(range, criteria, [average_range])To exclude zeros from a range in column A:
=AVERAGEIF(A1:A100,"<>0")The "<>0" criterion tells Excel to include only values where the cell is not equal to zero. Other useful criteria:
">0"— positive values only (excludes both zeros and negatives)">100"— only values above a threshold"<>"&E1— exclude whatever value is in cellE1
The optional average_range parameter lets the criterion apply to one range while the average is computed over a parallel range. For example, to average column B values where column A is “East”:
=AVERAGEIF(A1:A100,"East",B1:B100)AVERAGEIFS: multiple criteria
Syntax (note the average_range comes first, not last):
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)To average sales (column C) for the East region (column A) excluding zeros (column C):
=AVERAGEIFS(C2:C100, A2:A100, "East", C2:C100, "<>0")Each criteria_range must be the same dimensions as the average_range. The function returns #DIV/0! if no rows satisfy all the criteria.
Array formula alternative
For cases AVERAGEIF cannot express directly (multiple OR conditions, transformations of the source values), the AVERAGE+IF array pattern works:
=AVERAGE(IF(A1:A100<>0, A1:A100))In Excel 2019 and earlier, enter this with Ctrl+Shift+Enter to commit it as an array formula (Excel adds the curly braces). In Excel for Microsoft 365 and Excel 2021+, dynamic arrays handle this automatically — a regular Enter is enough.
To filter on a separate column:
=AVERAGE(IF((A1:A100<>0)*(B1:B100="Approved"), A1:A100))The (condition1)*(condition2) pattern is the standard array way to combine multiple conditions in a single boolean.
Handling errors
If a range contains DIV/0 errors or no qualifying rows exist, wrap the formula in IFERROR:
=IFERROR(AVERAGEIF(A1:A100,"<>0"), 0)This returns 0 (or any chosen fallback) when no non-zero values are present. For more granular error handling, use IFNA (Microsoft 365 / 2013+) to catch only #N/A errors and let other errors propagate.
Blank vs. Zero
A frequent source of confusion: AVERAGE already ignores blank cells. The behaviour to override is its inclusion of explicit zeros. If a dataset has both blanks and zeros and both should be excluded, AVERAGEIF(range,"<>0") is sufficient — blanks are skipped automatically and zeros are filtered by the criterion.
A formula that incorrectly tries to exclude blanks explicitly:
=AVERAGEIF(A1:A100,"<>"&"")This will not behave the way most users expect — "<>" against "" is interpreted as “not equal to empty string,” which is technically true for every cell containing text or numbers and false for genuinely blank cells. The simpler and correct form is just AVERAGEIF(A1:A100,"<>0").
Common pitfalls
- Range as a single cell.
=AVERAGEIF(A1,"<>0")averages only one cell —A1. The range needs to be the full data block, e.g.A1:A100. This is a frequent typo when adapting examples. - Text-formatted numbers. If column A is stored as text, AVERAGE ignores it. Convert to numeric with
VALUE()or a multiply-by-1 helper column. - Mixed dates and numbers. Excel stores dates as numbers under the hood; AVERAGE will happily compute the mean of dates if they exist in the range. Use AVERAGEIF with a type-aware criterion or split the data.
- Pivot-table averages. A PivotTable’s “Average” summary on a row of zeros still includes those zeros. To exclude them, add a calculated field that uses an IF condition or filter the zeros from the source.
When zero is the right answer
Not every zero should be excluded. If a sales team genuinely had a zero-revenue day, that day belongs in the operational average — excluding it overstates daily performance. The discipline is to define “missing” before writing the formula. A common pattern in business data:
- Use
NA()or leave the cell blank for missing observations. - Use
0only for actual measured zeros. - Then
AVERAGE(which skips blanks) gives the right answer without needing AVERAGEIF.
Cleaning the data once at entry is more robust than filtering it on every formula.
FAQs
How do I make Excel’s AVERAGE ignore zeros?
Use AVERAGEIF with the "<>0" criterion — for example =AVERAGEIF(A1:A100,"<>0").
What’s the difference between AVERAGE, AVERAGEIF, and AVERAGEIFS?
AVERAGE is the unfiltered mean (skipping blanks and text, including zeros). AVERAGEIF adds one criterion. AVERAGEIFS allows multiple criteria across one or more ranges.
Does AVERAGE already ignore blank cells?
Yes. Blank cells, text values, and logical values are skipped by AVERAGE. Only explicit zero values are counted.
How do I handle DIV/0 errors?
Wrap the call in IFERROR — for example =IFERROR(AVERAGEIF(A1:A100,"<>0"),0).
Can I exclude zeros and negatives in one formula?
Yes — use ">0" as the criterion: =AVERAGEIF(A1:A100,">0"). This includes only strictly positive values.
Can I do this in a PivotTable?
The default PivotTable Average aggregation cannot exclude zeros directly. Add a calculated field (PivotTable Analyze › Fields, Items, & Sets › Calculated Field) using an IF expression, or filter zeros from the source range before the pivot.
