To calculate an average in Excel while ignoring zeros, use AVERAGEIF with the criterion “<>0″. If your values are in A2:A10, enter =AVERAGEIF(A2:A10,"<>0") in an empty cell. Excel averages the nonzero numeric values and leaves blank cells out of the calculation too. This guide shows how to enter the formula, check its result, and handle common edge cases in Microsoft Excel for Microsoft 365 on Windows.
What You’ll Need
- An Excel workbook with the values you want to average.
- The cell range containing those values, such as A2:A10.
- An empty cell for the result. You do not need to delete or hide zero values in the source data.
Step-by-Step
- Identify the range to average. Select the cells that contain the values you want included, and note the first and last cell addresses. For example, a list in cells A2 through A10 is the range A2:A10. Include the data cells, not a heading such as “Score” in A1. The formula can work with a range containing numbers, zero values, and blanks. If the column also contains labels or notes, check that you are selecting the intended cells.
- Choose where the result should appear. Click a blank cell outside the data range, such as B2, so the result does not overwrite one of the values. With the values in A2:A7 being 8, 0, 12, blank, 10, and 0, enter
=AVERAGEIF(A2:A7,"<>0"). The formula returns 10: the nonzero numbers add to 30, and 30 divided by the three included numbers (8, 12, and 10) is 10. The two zeros and the blank do not affect this result. - Confirm the formula and press Enter. Type the equals sign first, then the function name and range. The criterion “<>0” means “not equal to zero.” The quotation marks are required because the criterion is written as text; do not remove them. After entering the formula, press Enter to display the average. If you see the formula itself instead of a result, see the troubleshooting section below.
- Check that the selected range matches your data. Click the result cell and inspect the formula bar. Confirm that the range starts at the first value and ends at the last one. A range that stops early produces an average from only part of the data; a range that includes unrelated numbers can change the result. If you add new values later, update the range or use an Excel table so the data range can expand more conveniently.
- Format the result if needed. The calculation may produce several decimal places. Select the result cell, then use Home > Number to choose a number format or adjust decimal places. Formatting changes how the answer appears, not the underlying average. For instance, displaying 10.333 as 10.33 rounds the visible value but does not change the stored calculation.
- Use another range when your data is elsewhere. Replace A2:A10 with the actual range in your sheet, keeping the criterion unchanged. With weekly figures in C5:C20, for example, the formula should refer to C5:C20 rather than the sample cells. You can also select the range with your mouse while building the formula, then type the criterion after the range. Make sure the formula refers to the cells containing the values, not a nearby heading or the cell where the answer will go.
- Add another condition only when your question requires one. If you want the average of nonzero scores for one region, use AVERAGEIFS. With regions in A2:A5 (North, North, South, North) and scores in B2:B5 (10, 0, 30, 20), enter
=AVERAGEIFS(B2:B5,B2:B5,"<>0",A2:A5,"North")in an empty cell. It returns 15, averaging the North scores 10 and 20 while excluding the North zero. The first range is the values to average; each following range and criterion pair adds a condition.
The basic AVERAGEIF method uses the same function and criterion in Excel for Microsoft 365 on Windows, Mac, and the web. Ribbon details can differ slightly between platforms, but no special setting is required. In some regional settings Excel expects semicolons instead of commas between function arguments; use the separator your Excel installation accepts.
Common Mistakes
Leaving out the quotation marks around the criterion. The condition needs to be entered as text. Without the quotation marks, Excel may interpret the expression differently or report a formula error. Keep the less-than and greater-than symbols together as “<>”, followed by zero.
Using the wrong range or including a heading. If the heading is included, Excel will usually ignore it as text, but selecting the wrong rows can still include unrelated numbers or omit actual values. Check the range references in the formula bar rather than assuming Excel selected the intended cells.
Confusing zero with a blank. AVERAGE already ignores blank cells; the criterion is needed to exclude numeric zeros. A cell containing a formula that displays 0 is still a zero value and is excluded by the criterion. A cell containing a space or text that looks like a number may not behave like a true numeric value, so check the source data if the answer seems unexpected.
Removing zeros from the data to make the average work. This changes the source information and can make other calculations misleading. The formula excludes zeros from this particular average without deleting them. Keep the original data intact unless you have a separate reason to edit it.
Assuming every zero should be ignored. A zero may be a meaningful measurement, such as no sales or a score of zero, rather than a missing observation. Exclude zeros only when that matches the meaning of your data and the question you are answering.
Tips
Keep the data range consistent when comparing averages across groups. If one average includes extra rows and another does not, the comparison may be unfair even though both formulas are valid. A small note beside the result describing the included period or category can make the calculation easier to interpret later.
For a growing list, consider converting the data range to an Excel table. Select the data and choose Insert > Table, confirming whether it has headers. Tables expand as you add rows, which helps avoid accidentally leaving new entries outside a fixed cell range. You can still use the same AVERAGEIF approach with the table’s value column; select the relevant table column when creating the formula and check Excel’s generated reference.
Do not round the source values just to make the displayed average look tidy. Use number formatting on the result cell instead. That preserves precision for further calculations. If you need to report a rounded value, follow the rounding rules used for your task or organization.
When auditing the answer, list the values that should count and verify their total and count. In the worked example, the included values are 8, 12, and 10, so the count is three and their total is 30. This quick manual check can reveal a mistaken range or a value that is stored differently than expected.
Troubleshooting
The result is #DIV/0!. This generally means Excel found no numeric values that meet the condition to average. Check whether the range contains only zeros, blanks, or text, and confirm that the cell references point to the right data. If zero is the only recorded value, decide whether zero is meaningful for your calculation; there is no nonzero average to return when all eligible values are excluded.
The answer seems too high or too low. Recheck the first and last cells in the range, and look for values outside the intended data set. Also inspect whether some apparent numbers are text, or whether there are formula results that are zero. Compare the values included in the calculation with the original list and recalculate their total divided by their count.
Excel displays the formula as text. The result cell may be formatted as Text, or the entry may have a leading apostrophe. Change the cell format to General using Home > Number, then re-enter the formula. Simply changing the format may not recalculate an already-entered text string; click into the cell, press F2, and then press Enter.
Excel says there is a problem with the formula. Check that the parentheses are balanced, the criterion is inside straight quotation marks, and the range is written correctly with a colon between its endpoints. If your regional settings use a semicolon as the argument separator, replace the commas with semicolons. Function names may be localized in some non-English installations.
A cell that looks like zero is still included. Check whether it contains text, a tiny nonzero decimal displayed as 0 because of number formatting, or a value with an unexpected character. Increase the displayed decimal places or inspect the formula bar. If the underlying value is actually 0, it should be excluded by the criterion; if it is a small value such as 0.004, it is not zero and will be included.
Frequently Asked Questions
Does AVERAGEIF ignore blank cells too?
Yes. AVERAGEIF averages numeric cells that meet the condition, and blanks are not included as numeric values. The “<>0” condition is specifically useful for excluding zeros.
Will it ignore a zero returned by another formula?
Yes. If a cell’s formula evaluates to the numeric value 0, AVERAGEIF treats that result as zero and excludes it. If the cell instead returns text, its treatment can differ, so inspect the underlying result when the average surprises you.
Can I ignore zeros in a row instead of a column?
Yes. Use the row’s cell range in place of the column range. The function works with a horizontal range as well as a vertical one; the criterion remains the same.
What if I want to average only positive nonzero values?
The not-equal-to-zero condition also allows negative numbers. If negatives should be excluded too, use a greater-than-zero condition instead, after confirming that positive-only values match your calculation.
Can I use this formula in Excel for Mac or Excel for the web?
Yes. AVERAGEIF is available in these versions, and the formula works the same way. The menus may look slightly different, but you can enter the formula directly into a cell.
Does the formula change the original data?
No. It calculates a result in the cell where you enter it and leaves the source values untouched. You can keep the zeros visible for other calculations and use this formula only where an average should exclude them.
Related
- Excel Payment Log: How to Track Payments and Balances
- Excel Payment Request: Create, Track, and Send One
- Excel Columns: How to Format, Resize, and Style Them
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- IFERROR with AVERAGE in Excel: Formulas and ExamplesTo use IFERROR with AVERAGE in Excel, wrap the average calculation in IFERROR: with the values to average…
- Advanced Excel HLOOKUP Formula: Examples and TipsFor an advanced Excel HLOOKUP, use =HLOOKUP(F1,$A$1:$D$4,2,FALSE) when the value to find is in F1, the lookup keys…
- Compound Interest Calculator in Excel: Formulas and TipsYes. In Microsoft Excel for Microsoft 365 on Windows, enter the starting balance, annual interest rate, number of…
- Q1 and Q3 in Excel: A Formula for Finding QuartilesTo find the first and third quartiles in Excel, use QUARTILE.INC with quartile numbers 1 and 3. If…
- Excel TRIM Formula Examples for Cleaning Messy TextIn Excel for Microsoft 365, use =TRIM(A2) to remove leading and trailing ordinary spaces from the text in…
- Excel Formula for Range of Values: Find the SpreadTo calculate the numerical range of values in Excel, subtract the smallest value from the largest: =MAX(A2:A7)-MIN(A2:A7). If…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.