Div 0 Error in Excel AVERAGE: Causes and Fixes

Excel shows #DIV/0! for an average when the calculation has no numeric values to average, so its count of numbers is zero. Check the cells in the average range; if an empty result is appropriate, use a check such as =IF(COUNT(A2:A10)=0,"",AVERAGE(A2:A10)) to show a blank instead of the error.

Why AVERAGE returns #DIV/0!

The AVERAGE function adds the numeric values it finds and divides their total by how many numeric values there are. If it finds none, the divisor is zero, and Excel returns #DIV/0! rather than a number. This is usually a clue about the data or the range, not evidence that the AVERAGE function itself is broken.

For example, if A2:A4 is empty, =AVERAGE(A2:A4) returns #DIV/0!. The same result can occur if those cells contain only text, such as “pending,” or formulas that return an empty string. AVERAGE ignores blank cells and text in referenced cells; it does not treat them as numeric zeroes.

Zero is different from blank. If A2:A4 contains 0, 10, and 20, the average is 10 because all three are numbers. A range with actual zeros therefore has an average, even if the result might not be what you expect. Dates and times are also stored as numbers in Excel, so they can be included in an average too.

How Excel counts values in an average

When AVERAGE receives a cell range, it includes numeric values and excludes empty cells, text, and logical values such as TRUE or FALSE entered in cells. A formula that displays an empty string with "" is still a formula in the cell, but its displayed result is text, not a number; AVERAGE excludes it. Error values in the range are a separate matter: an error such as #N/A generally propagates through the calculation rather than being ignored.

That distinction helps explain why a range that looks populated can still produce #DIV/0!. A column may contain labels, imported numbers stored as text, or formulas returning empty strings, but no numeric values that AVERAGE can use. Conversely, a cell displaying 0 does count. To inspect the situation, select a cell in the range and check its formula bar or use Excel’s number formatting and data-cleaning tools to determine whether the underlying value is numeric.

For a basic count check, with the values to average in A2:A10, enter =COUNT(A2:A10). It returns the number of numeric cells. If the result is 0, AVERAGE has no numeric observations in that range. COUNT is a diagnostic here; it does not tell you whether text values are valid data that should be converted or intentionally excluded.

Find the cause before changing the formula

Start by confirming the formula’s referenced range. A formula may point to the wrong rows after data has been moved, may omit the newest entries, or may refer to a range that is currently empty. Click the formula cell and inspect the colored range outline Excel displays while editing the formula. Check that the intended data—not a heading or an unrelated column—is selected.

Next, look at what is actually in the source cells. A value such as 125 may be stored as text after a CSV import, especially if it has a leading apostrophe or inconsistent separators. COUNT will not count that as a number. Depending on the cause and the data format, you can correct the source values, use Excel’s conversion options, or re-import the data with the appropriate regional settings. Do not convert text automatically if it includes identifiers, account codes, or other values that should remain text.

Check whether the range contains any numbers, including zero.
Look for formulas returning "", text entries, or numbers stored as text.
Verify that the formula references the intended cells and includes the expected rows.
Check whether filters or changing source data have left the selected range without numeric entries.
If the range contains another error value, investigate that error separately; masking it may hide a data problem.

Filtering does not normally make AVERAGE ignore hidden rows: a regular AVERAGE over a range includes hidden and filtered-out numeric cells. So a filtered view that appears empty is not, by itself, proof that the range has no numbers. If the desired calculation should include only visible rows, use a function designed for visibility, such as SUBTOTAL, and choose its behavior carefully rather than assuming AVERAGE follows the filter.

Show a blank when there are no numbers

If an empty result is the clearest presentation when there are no numeric observations, test the count before calculating. With values in A2:A10, use =IF(COUNT(A2:A10)=0,"",AVERAGE(A2:A10)). When the range has no numbers, the formula returns a blank-looking result; when it has numbers, it returns their average. For instance, if A2:A4 contains 10, 20, and a blank, the result is 15.

This approach is more informative than replacing every possible error with a blank. The COUNT test addresses the specific no-numbers case, while an unrelated error in a cell used by AVERAGE can still appear and alert you. Keep in mind that a formula returning "" is not truly an empty cell: other formulas and charts may treat it differently from a cell that has never contained a value.

For a dashboard where a visible message is preferable, with data in A2:A10, use =IF(COUNT(A2:A10)=0,"No numeric data",AVERAGE(A2:A10)). The text makes the reason for the missing result clear to readers, though the result column now contains either text or a number. If another formula expects a numeric result, a blank or a dedicated error-handling design may be more suitable.

When IFERROR is useful—and when it can mislead

A shorter option is to wrap the average in IFERROR. With values in A2:A10, =IFERROR(AVERAGE(A2:A10),"") displays a blank whenever the average calculation returns an error. This can be useful in a presentation sheet where a blank is intentionally preferred to an error message.

However, IFERROR catches more than #DIV/0!. It can also hide other problems, such as an error value already present in the source range or a reference problem in a more complex formula. If those errors matter, use the COUNT check instead, or handle the specific error deliberately. A clean-looking worksheet is not necessarily a correct worksheet if the formula has silently concealed invalid data.

For a criteria-based average, a similar issue arises if no matching numeric values exist. With regions in A2:A10 and amounts in B2:B10, =AVERAGEIF(A2:A10,"West",B2:B10) averages amounts for rows labeled West; if no qualifying numeric amounts are available, it can return #DIV/0!. To show a message for an error, the same assumed layout can use =IFERROR(AVERAGEIF(A2:A10,"West",B2:B10),"No matching numeric data"). Use this only if hiding other errors from that calculation is acceptable. Also confirm that the criteria range and average range line up row for row.

AVERAGE versus AVERAGEIF and AVERAGEIFS

AVERAGE calculates across the numeric cells in a range without applying a condition. AVERAGEIF applies one condition, while AVERAGEIFS can apply multiple conditions. The criteria can reduce the eligible rows to none, or to rows whose corresponding average cells contain no numbers, which is why a criteria-based average may fail even when the worksheet has plenty of numeric values elsewhere.

For example, a department column may contain numeric salaries, but no row may match the department named in the criteria. Or rows may match the department while the corresponding amount cells are blank or text. In either case, the important question is not simply “Are there numbers on this sheet?” but “Are there numeric values in the rows this formula actually averages?” Check the criterion spelling, spaces, and referenced ranges as well as the values.

AVERAGEIFS uses the average range first, followed by criteria-range and criterion pairs. When troubleshooting it, verify that each criteria range has the same dimensions as the average range. A mismatch can cause a different error, so do not treat every error from a conditional average as proof that there were zero matches. Correct the range design first, then decide how a genuine no-match result should appear.

Common misconceptions and practical choices

One common misconception is that blank cells are counted as zero. They are not: AVERAGE excludes them, while a literal numeric zero is included. If blanks represent missing observations, that is often the desired behavior. If the business rule says a missing value should count as zero, fill or calculate an actual zero only when that interpretation is valid; changing missing data into zero can lower the average and misrepresent the underlying situation.

Another misconception is that every #DIV/0! in a worksheet comes from AVERAGE. The error can also come from explicit division by zero or a formula that divides by a cell containing zero. Select the cell and inspect its formula to identify the operation that failed. Fixing the source data for an average will not solve an unrelated division formula.

Choose the response based on what the result means:

Use a COUNT check when “no numeric observations” should be treated differently from other formula errors.
Use IFERROR when any error from the calculation should intentionally be replaced with a chosen display value.
Correct the data when numbers are stored as text or the formula points at the wrong range.
Leave the error visible during analysis if it is useful as a warning that the expected data has not arrived.

Frequently Asked Questions

Why does Excel AVERAGE show #DIV/0?
It has no numeric values to average in the referenced range, so the count used as the divisor is zero. Check for blanks, text, formulas returning empty strings, and an incorrect range.

How do I make AVERAGE return a blank instead of #DIV/0?
With the values in A2:A10, use =IF(COUNT(A2:A10)=0,"",AVERAGE(A2:A10)). It returns a blank-looking result only when there are no numeric cells and otherwise calculates the average.

Does AVERAGE count zero as a value?
Yes. A numeric zero is included in both the sum and the count. A genuinely blank cell is excluded, so a range containing 0 and 10 averages to 5, while a range containing only 10 and blanks averages to 10.

Can numbers stored as text cause #DIV/0!?
Yes. If every apparent number in the selected range is stored as text, AVERAGE may find no numeric values. Confirm the cell contents and convert only values that are meant to be numeric.

Why does AVERAGEIF return #DIV/0 when the sheet has numbers?
The criteria may match no rows, or the matching rows may have no numeric values in the average range. Check the criterion, both ranges, and the corresponding values rather than looking only at numbers elsewhere on the sheet.

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

More in Errors & fixes

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

Spotted an error in this guide? Let us know.