Average in Excel Without Zeros: Formula and Examples

To average numbers in Excel while ignoring zeros, use =AVERAGEIF(A2:A8,"<>0"). Replace A2:A8 with your data range; Excel averages the nonzero numbers and ignores blank cells.

The AVERAGEIF formula and how it works

AVERAGEIF calculates an average using a condition. In this case, the condition <>0 means “not equal to zero.” The formula checks each cell in the range and includes values that meet that condition in the average. Because blank cells are not numbers to average, they do not affect the result.

For a simple list, the formula needs only two pieces: the range to check and the condition. If you use a separate range for the values to average, AVERAGEIF can also accept an average_range argument. When the same cells are both checked and averaged, as in the formula above, Excel can infer the average range from the first argument.

This approach excludes actual numeric zeros, not just zeros that happen to be displayed. It also keeps negative numbers: a value of -4 is not equal to zero, so it remains part of the average. That distinction matters when negative values are meaningful, such as a loss, temperature below zero, or a correction in a financial dataset.

Worked example: compare the two averages

Suppose A2:A8 contains 12, 0, 18, a blank cell, 15, 0, and 9, in that order. The nonzero numbers total 54, and there are four of them. Their average is 54 divided by 4, or 13.5. The two zeros are excluded, and the blank is ignored as well.

With those values in A2:A8, the formula below returns 13.5:

With the example data in A2:A8, use:

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

For comparison, the regular average includes the two zeros but still ignores the blank. The six numeric entries total 54, so their average is 9. The choice between 9 and 13.5 is not a matter of formatting: it reflects whether a zero is a real observation that should count in the calculation.

Try the formula in a cell outside the data range, such as B2, so it does not overwrite one of the values being averaged. If you add more observations later, update the range or use a structured Excel Table reference so the calculation can expand with the data.

When to exclude zeros—and when not to

Ignoring zeros is appropriate when zero represents “no measurement,” “not applicable,” or a placeholder rather than a genuine result. For example, if a team records sales only on days when a store is open, zeroes entered for closed days may distort the average daily sales for operating days. In that case, excluding them may answer the intended question more accurately.

But zero is often a valid measurement. If a product sold zero units on an open day, excluding that day will make average daily sales appear higher than the true average across all open days. Similarly, excluding zero test scores, zero balances, or zero rainfall readings can change the meaning of a report. Decide what the zero represents before choosing the formula.

Exclude zero when it is a placeholder for missing or inapplicable data.
Keep zero when it is a real recorded outcome and belongs in the population you are summarizing.
Use a separate marker, such as a blank or an explicit status column, when you need to distinguish “not measured” from a true zero.

A useful check is to compare the result with the ordinary average and count how many zeros are in the range. A large difference can be expected when many genuine observations are zero; it can also reveal that the data-entry convention needs clarification.

Average with additional conditions

AVERAGEIF handles one condition. If you need to exclude zero and restrict the average to records meeting another condition, use AVERAGEIFS. For example, imagine that A2:A20 contains regions and B2:B20 contains sales. To average nonzero sales for the East region, set the average range to the sales column and add conditions for both the sales and region columns.

With regions in A2:A20 and sales in B2:B20, the formula below returns the mean of nonzero sales entries for East. If East has sales of 10, 0, and 20, for example, the result is 15:

=AVERAGEIFS(B2:B20,B2:B20,"<>0",A2:A20,"East")

AVERAGEIFS applies all its conditions together, so a row must meet both tests to be included. In this example, the sale must be nonzero and its region must be East. If no numeric cells meet the conditions, Excel returns #DIV/0!, because there is no set of numbers to average. Check the range, spelling, and criteria before interpreting that error as a calculation problem.

For more complicated criteria, such as dates in a particular period or values above a threshold, add further criteria-range and criteria pairs to AVERAGEIFS. Keep the ranges the same size; mismatched ranges can cause errors or unexpected results.

Alternative for Microsoft 365 and Excel 2021

In Microsoft 365 or Excel 2021, FILTER can first select the nonzero values, then AVERAGE can calculate their mean. This can be helpful when you also want to inspect or reuse the filtered values in a larger calculation. It is not necessary for a basic average, because AVERAGEIF is shorter and is supported in older Excel versions too.

With the example numbers in A2:A8, this formula also returns 13.5:

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

FILTER produces an array containing the entries that meet its test, and AVERAGE calculates the mean of that array. If every entry is zero, or there are no qualifying numeric values, the filtered result is empty and the formula can return an error. AVERAGEIF has a similar no-match issue, so decide how a report should display that case rather than assuming it will produce zero.

The formula syntax is the same in Excel for Windows, Excel for Mac, and Excel for the web. FILTER availability depends on the Excel version, while AVERAGEIF is a widely supported option. In installations using localized function names or regional separators, Excel may display localized names or require semicolons instead of commas; the formulas here use English function names and comma separators.

Common problems and data-quality checks

A common misconception is that hiding zeros with number formatting removes them from calculations. It does not. A custom number format or a display setting can make zero values invisible, but the underlying values remain zero and are still included by a regular AVERAGE. AVERAGEIF is different because it deliberately filters those values out of the calculation.

Also check how imported data is stored. A cell containing the number 0 is not necessarily the same as a cell containing text that looks like 0. Text-formatted numbers can be ignored by arithmetic functions or behave differently in criteria tests. If the result seems wrong, inspect the cells, convert numeric-looking text to numbers where appropriate, and confirm that the range covers the intended records.

Unexpectedly high average: Confirm that the zeros you excluded are truly placeholders, not valid outcomes.
#DIV/0!: The range may contain no qualifying numeric values. Check whether all values are zero, blank, or text.
Wrong range: Make sure the formula includes all relevant rows and does not include headings or unrelated data.
Displayed zero versus stored value: Check the cell value itself; formatting can hide a zero without changing it.
Imported values: Look for numbers stored as text, spaces, or other unexpected characters that can interfere with calculations.

It is useful to keep the original records intact and put the summary formula in a separate cell. That makes it easier to audit the calculation and change the rule later if the meaning of zero changes.

Choosing the right method

For one range and one rule—ignore zero—AVERAGEIF is usually the clearest choice. It is concise, does not require a helper column, and communicates the exclusion directly in the formula. If the average must meet several rules, use AVERAGEIFS. If you need to expose the selected values or combine filtering with other dynamic-array work, FILTER followed by AVERAGE is another option in supported versions.

Before choosing, define the population you want to describe. “Average of all recorded days” and “average on days with a nonzero result” are different questions, even when they use the same source data. Write down whether zeros, blanks, text entries, and negative values should count. That definition prevents a technically correct formula from answering the wrong business or research question.

If the data is in an Excel Table, a structured reference can make formulas easier to read and can expand as rows are added. For example, if a table is named SalesData and its numeric column is named Amount, a structured-reference version can be written as =AVERAGEIF(SalesData[Amount],"<>0"). Use the actual table and column names in your workbook. The result follows the same rule: average numeric amounts that are not zero.

Frequently Asked Questions

What is the Excel formula to average without zeros?
Use =AVERAGEIF(A2:A8,"<>0"), replacing A2:A8 with your range. The criterion means “not equal to zero”; blanks are also ignored by the average.

Does AVERAGEIF ignore blank cells too?
Yes. Blank cells are not numeric values to average, so they do not add to the total or the count. A formula that returns an empty string is text rather than a numeric zero and is likewise not averaged as a number.

Will this formula keep negative numbers?
Yes. A negative number is not equal to zero, so it meets the criterion and is included. If you want only positive values, use a positive-value condition instead of the nonzero condition.

Why does my average return #DIV/0!?
Excel returns this error when there are no qualifying numeric values to average. The range may be empty, contain only zeros, or contain text rather than numbers. Check the source values and confirm that your condition matches the data.

Can I average without zeros in a filtered list?
AVERAGEIF does not automatically limit its calculation to rows currently visible after filtering; hidden rows can still be included. If the result must reflect only visible records, use an approach designed for visible cells and verify how it handles both filtered-out rows and zero values.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.