To average values in Excel while excluding zeros, use =AVERAGEIF(A2:A10,"<>0"), replacing A2:A10 with your data range. The formula averages the numeric values in that range that are not zero; blank cells do not add to the result.
The basic formula
AVERAGEIF is the straightforward choice when one condition determines which values to include. Its syntax is =AVERAGEIF(range, criteria, [average_range]). The first argument is the cells Excel checks, the second is the condition, and the optional third argument is the cells Excel averages when the condition is met.
In the formula =AVERAGEIF(A2:A10,"<>0"), the criterion "<>0" means “not equal to zero.” Because the average range is omitted, Excel checks and averages the same cells. The quotes are part of the required criteria syntax; they tell Excel to interpret the comparison as a condition rather than as a number.
Enter the formula in an empty cell, such as B2, and press Enter. Excel for Microsoft 365 on Windows calculates the result automatically. If you add or change values within A2:A10, the formula updates. Expand the range if your data extends beyond row 10, or use an Excel Table column reference if the list will grow regularly.
How Excel calculates the result
Excel evaluates each cell in the specified range against the condition. A numeric zero fails the “not equal to zero” test and is left out. Nonzero numeric values that meet the condition are used in the average. In the simple formula, the cells being tested and the cells being averaged are identical.
For example, suppose A2:A6 contains 0, 12, 18, 0, and 30. Excel excludes both zeros, adds 12 + 18 + 30 to get 60, and divides by the three included values. The result is 20. The zeros are not counted in the divisor, which is why the answer is not 12.
This distinction matters: excluding zero is different from changing zero to blank before calculating. The formula leaves the source data untouched and changes only which values participate in the calculation. That makes it useful for reports where a zero should remain visible in the underlying records but should not affect a particular average.
Using separate test and average ranges
Sometimes the condition is in one column, while the values to average are in another. For instance, column B might contain units sold and column C might contain the corresponding prices. If you want to average prices only for rows where units sold are not zero, use =AVERAGEIF(B2:B20,"<>0",C2:C20).
Here, B2:B20 is the range Excel tests, and C2:C20 is the average range. The rows must line up: B2 determines whether C2 is included, B3 determines whether C3 is included, and so on. The formula does not mean “ignore zero prices”; it means “average prices for rows whose units-sold value is not zero.”
If the goal is instead to average the prices in C while excluding zero prices in C, test and average the price range itself: =AVERAGEIF(C2:C20,"<>0"). Checking which column contains the zeros you intend to exclude is an important step when a formula returns a plausible but unexpected result.
Zeros, blanks, and missing data
A blank cell and a numeric zero are not the same thing in Excel. A blank usually represents no entered value, while zero is a numeric value that may be a meaningful measurement. AVERAGE already ignores genuinely blank cells, but it includes numeric zeros. The “not equal to zero” criterion is useful when zero values should also be excluded.
Think about what zero means in your data before applying the formula. In a sales report, zero sales might be a valid result for a day when the store was open, or it might be a placeholder for missing information. Those interpretations call for different analyses. Excluding valid zero-sales days can make the average sales on active days look higher than the average across all days.
Cells that look blank may contain a formula returning an empty string, a space, or other text. Those are not necessarily identical to truly empty cells. In a numeric range, text is not averaged as a number, but unusual entries can still make data checks and criteria behavior harder to interpret. If the result matters, inspect a few representative cells and confirm whether they are genuinely blank, numeric zero, or text.
Choosing the right range
The range should cover the values you intend to summarize, without accidentally including totals or unrelated numbers. If your data is in A2:A100, a formula that stops at A50 silently leaves out later rows. Conversely, including a total at the bottom can skew the average because the total is treated as another value.
For a list that changes size, convert the data to an Excel Table using Insert > Table. Tables expand as rows are added and can make formulas easier to read. If the table is named Sales and the relevant column is Amount, a structured-reference formula can be written as =AVERAGEIF(Sales[Amount],"<>0"). Excel may assign a different table name or column heading in your workbook, so use the names shown there.
When copying a formula, consider whether the range should move. A reference such as A2:A100 adjusts when copied to another cell. To keep the range fixed, use dollar signs: =AVERAGEIF($A$2:$A$100,"<>0"). This is helpful when filling a formula across a report, but it is not necessary for a single summary calculation.
Common problems and fixes
- The result is #DIV/0!: Excel has no qualifying numeric values to average. The range may contain only zeros, blanks, or text, or the test range may exclude every row. Check the data and confirm the criterion points to the intended cells.
- The answer seems too high: The formula may be excluding valid zero observations, or it may be averaging a smaller subset than intended. Compare the included records with the original data and reconsider what zero represents.
- The answer seems too low: Check for an included total, an incorrect range, or a separate average range that does not correspond to the test range. Also verify that values stored as text are not being mistaken for numeric values.
- The formula appears as text: The destination cell may be formatted as Text, or the formula may have a leading apostrophe. Change the cell format to General, then re-enter the formula.
- Excel rejects the formula: Confirm the parentheses and quotation marks, and check the argument separator used by your Excel regional settings. The examples here use commas; some settings require semicolons.
If you want a message instead of an error when no nonzero numbers exist, with the data in A2:A10 use =IFERROR(AVERAGEIF(A2:A10,"<>0"),"No nonzero values"). In the example where A2:A10 contains only zeros or no averageable values, the displayed result is “No nonzero values.” This handles the display, but it does not fix missing or incorrectly entered data.
Alternatives and related formulas
AVERAGE is appropriate when zeros should count. With 0, 12, 18, 0, and 30 in A2:A6, =AVERAGE(A2:A6) returns 12 because all five numeric values are included. Use this comparison to make sure excluding zeros reflects the question you want the report to answer.
AVERAGEIFS is useful when several conditions apply. For example, with amounts in B2:B20 and a category in C2:C20, you could average nonzero amounts only for rows labeled East with =AVERAGEIFS(B2:B20,B2:B20,"<>0",C2:C20,"East"). It checks each condition range and averages only rows that satisfy all the criteria. For a single zero-exclusion condition, AVERAGEIF is simpler.
You can also calculate an average after filtering a table, but a normal average formula does not necessarily mean “average only the currently visible rows.” If the requirement is to respond specifically to filters or hidden rows, choose a function designed for that behavior rather than assuming AVERAGEIF will follow the visible records. For ordinary criteria-based exclusion, use the formula that directly expresses the condition.
Frequently Asked Questions
What is the formula to average cells except zero in Excel?
Use =AVERAGEIF(A2:A10,"<>0"), changing A2:A10 to the range containing your values. The criterion excludes zero values from the average.
Does AVERAGEIF ignore blank cells too?
Blank cells do not contribute numeric values to an average. The “not equal to zero” condition is needed to exclude numeric zeros, which a regular average would include.
Can I exclude zero from one column and average another?
Yes. Put the column to test first and the column to average third, as in =AVERAGEIF(B2:B20,"<>0",C2:C20). The two ranges must correspond row by row.
Why does my formula return #DIV/0!?
There may be no nonzero numeric cells that meet the condition. Check that the selected range is correct and contains numeric values. You can wrap the formula in IFERROR to show a message when no values qualify.
Does this formula work in Excel for Mac or Excel for the web?
Yes. AVERAGEIF is available in current Excel for Microsoft 365 on Windows, Mac, and the web. The formula works the same way; menus and interface details can differ, but no special platform-specific steps are required.
Related
- Excel SUM and Hidden Cells: What Gets Included in Totals?
- Excel SUM and Multiply: How to Combine Them
- Excel Sum Minus: How to Add and Subtract Numbers in Excel
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Nested XLOOKUP Examples for ExcelA nested XLOOKUP places one XLOOKUP inside another, so one search can supply the lookup value, return array,…
- Excel INDIRECT Sheet Name: Build Dynamic Sheet ReferencesTo use a cell’s text as a worksheet name in Excel, combine it with the cell or range…
- Excel Autofill Keyboard Shortcuts for Fill Down and RightTo autofill a selected range in Excel, include the source cell and the cells you want to fill,…
- Excel Wildcard SUMIF Formula Examples That Match TextTo total values for text that contains a word, use SUMIF with asterisks around the word: with product…
- Excel SUMIF: Sum Columns When a Cell Contains TextTo add values in one column only when the corresponding cell in another column is exactly x, use…
- COUNTIF Data Analysis Examples in ExcelCOUNTIF data analysis examples show how to count spreadsheet records that meet a rule, such as a region,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.