Excel AVERAGE: What It Ignores and Includes

In Excel, AVERAGE ignores empty cells, text, and logical values such as TRUE or FALSE when they are inside a cell range or array. It does not ignore zero or numeric values, and an error in the range usually makes the result an error; values supplied directly as function arguments are handled differently.

What “ignore” means in AVERAGE

AVERAGE calculates the arithmetic mean: it adds the numbers it recognizes and divides by how many recognized numbers there are. “Ignore” means a cell is left out of both parts of that calculation. A text entry does not add zero to the total, and it does not increase the count. A blank cell likewise contributes neither a value nor a count.

This distinction matters because omitting a cell is not the same as treating it as zero. Suppose a set of scores is 10, 20, and blank. AVERAGE uses the two numbers and returns 15. If the third entry is an actual zero instead, the calculation is 10 + 20 + 0 divided by three, which returns 10. The blank says there is no recorded value; zero says a value was recorded and it was zero.

AVERAGE is also not the same as the median. The mean can be pulled toward unusually high or low values because every recognized number contributes to the sum. Understanding which entries Excel includes is therefore important when interpreting a result, not just when checking a formula.

What AVERAGE includes in a cell range

When you give AVERAGE a range, it uses numeric values in that range. These include ordinary numbers, percentages, and dates or times stored by Excel as numbers. A date may look unlike a number on the worksheet, but Excel stores dates as serial values; AVERAGE can therefore calculate a numerical mean from date cells. The displayed result may need date formatting to look like a date.

Zero is included, including a zero produced by a formula. A number formatted to look blank is still included if its underlying value is zero or another number. Formatting changes how a value appears, not whether AVERAGE counts it. Likewise, a percentage such as 25% is stored numerically as 0.25, so it contributes 0.25 to the calculation.

With numbers in B2:B6, where the cells contain 10, 0, 20, a genuinely empty cell, and the text N/A, =AVERAGE(B2:B6) returns 10. Excel adds 10 + 0 + 20 and divides by three. It skips the empty cell and the text entry. If the N/A entry were replaced by the number 5, the average would be 8.75 because the four numeric values would total 35.

Text, logical values, and blanks

Text in a referenced cell is normally ignored, even when it looks like a number. For example, a value imported or entered as text may display as 12 but not be counted by AVERAGE. If the intended value is numeric, convert the cell to a number rather than assuming its appearance proves Excel recognizes it as one. A green error indicator or left-aligned display can be a clue, though formatting and worksheet settings can affect appearance.

TRUE and FALSE in cells within the referenced range are also ignored by AVERAGE. They are logical values, not numbers for this calculation. This is easy to confuse with formulas or expressions that explicitly convert logical results to numbers: once a result is numeric, it can be included. Check the actual stored value and the formula that produced it when a result seems surprising.

A truly empty cell is ignored. A formula that displays an empty string is different: it returns text, not a genuinely empty cell. In a range, that text result is ignored by AVERAGE too, so it often behaves like a blank for this function. But other functions and checks can distinguish an empty cell from a formula returning empty text, so do not treat the two as identical in every spreadsheet task.

Direct arguments behave differently

Excel distinguishes between values inside a referenced range and values supplied directly as arguments. Logical values and text representations of numbers typed directly into the argument list are counted. For example, =AVERAGE(2,4,TRUE) returns 2.333333: Excel treats TRUE as 1 in this direct-argument context and averages 2, 4, and 1.

That exception is one reason a small test using typed values can give a different result from a formula that points to cells containing the same-looking entries. If a function refers to cells, AVERAGE skips text and logical values in those cells. If you type a supported value directly into the argument list, the rules differ. For predictable analysis, keep input values in cells and use clear numeric data rather than relying on this special case.

Direct arguments that are invalid text can also cause an error rather than being quietly skipped. The safe practical rule is not to infer range behavior from a formula with typed arguments: identify whether each input is a cell reference, a range, or a literal argument.

Errors are not ignored

An error value in a referenced range, such as #N/A, #VALUE!, or #DIV/0!, generally propagates through AVERAGE. Instead of returning the mean of the remaining numbers, the formula displays an error. This is different from ordinary text such as a note or label, which AVERAGE ignores when found in a range.

For example, an accidental division-by-zero error in one source cell can make a summary average fail even if every other entry is a valid number. Find and fix the source error, or deliberately decide how the analysis should treat it. Do not simply hide the symptom: replacing errors with zero changes the average, while excluding them changes the population being summarized. The right choice depends on what the data means.

Excel’s error-checking tools can help locate a problematic cell, and you can inspect the range or use Find to search for visible error values. If the source formula is expected to produce an error in some cases, consider designing the data-cleaning step so that exceptional cases are handled consistently before calculating a summary.

Hidden rows, filtered data, and what gets averaged

AVERAGE does not automatically limit its calculation to rows currently visible on screen. If a row is hidden manually or excluded by a filter, its numeric value can still be included in an AVERAGE over the full range. Hiding a row is a display choice, not a change to the underlying data or to AVERAGE’s basic range rules.

This often surprises people who filter a table to show one department and expect a total at the bottom to update. If the desired result should respond to filtering, use SUBTOTAL with its average operation rather than ordinary AVERAGE. SUBTOTAL can ignore rows removed by a filter; its function-number options also determine whether manually hidden rows are included. Check the option carefully if manual hiding matters, and test the result against a small known example.

Filtering and ignoring text are separate behaviors. A filtered-out numeric value may still be included by AVERAGE, while a text value in a visible row is ignored. Decide first whether the question is “average all numeric entries in this range?” or “average only the currently visible records?” The answer determines whether AVERAGE is the appropriate tool.

Choosing the right averaging function

Use AVERAGE when the intended result is the arithmetic mean of numeric entries in a range and blanks or text labels should not count. If the requirement is more specific, another function may match it better:

AVERAGEIF calculates an average for values meeting one condition, such as sales for a particular region.
AVERAGEIFS handles multiple conditions, such as scores for one class during a specified term.
AVERAGEA includes text and logical values in references according to its own rules; text is treated as zero, TRUE as 1, and FALSE as 0. This is not the same as AVERAGE simply counting every cell as a numeric observation.
SUBTOTAL can average a range while responding to filtered rows, making it useful for filtered lists.

Choosing among these functions is a question about the definition of the result, not just which formula is shortest. For example, if an unanswered survey response should be omitted, a blank may be suitable. If it should count as a score of zero, enter a numeric zero. If only one category should contribute, use a conditional average rather than averaging the whole column and hoping hidden or unrelated records are excluded.

Common causes of an unexpected result

When an average looks too high or too low, first check for zeros and text-formatted numbers. Zeros lower a mean because they are included; text that looks numeric may be left out. Then check for formulas returning empty text, errors in the range, dates stored as serial numbers, and hidden rows that remain part of the calculation.

It is also useful to verify the count of numeric entries separately. If the data is in B2:B6, =COUNT(B2:B6) returns the number of numeric cells in that range. For the example containing 10, 0, 20, a blank, and N/A, it returns 3. That count is consistent with the AVERAGE result of 10. COUNT is a diagnostic, not a substitute for reviewing whether those three numeric entries are the ones the analysis should include.

Finally, confirm that the selected range is correct. A formula may include an old row, a heading, or a value from another group. Excel’s formula bar shows the expression, and selecting the referenced range makes it easier to inspect its boundaries. A correct function applied to the wrong records still produces an unhelpful answer.

Frequently Asked Questions

Does Excel AVERAGE ignore zero?
No. Zero is a numeric value, so it is included in both the sum and the count. If zero means “not measured” in your dataset, decide how to represent that missing information rather than assuming AVERAGE will omit zero automatically.

Does AVERAGE ignore blank cells?
Yes. A genuinely empty cell in the referenced range contributes nothing and is not counted. A formula returning an empty string is text and is also ignored by AVERAGE in a range, although it is not technically an empty cell.

Does AVERAGE count text that looks like a number?
Not when that text is in a referenced cell. Convert it to a real number if it should contribute. Text representing a number typed directly as a function argument is a separate case and can be counted under the direct-argument rules.

Does AVERAGE ignore hidden or filtered rows?
No. Ordinary AVERAGE can include numeric values in hidden rows and rows excluded by a filter. Use SUBTOTAL when the average should respond to filtered rows, and check its settings if manually hidden rows must also be excluded.

Why does AVERAGE return an error?
A referenced error value can make the average return that error instead of a number. Inspect the cells in the range for errors such as #N/A or #DIV/0!, then correct or intentionally handle the underlying data issue.

A practical way to remember the rule

For a range, remember: numbers count, including zero; blanks, text, and logical values are skipped; errors are not silently skipped. Dates count because Excel stores them numerically. Directly typed arguments have exceptions, and hidden rows do not disappear from an ordinary AVERAGE calculation. With those distinctions in mind, a surprising result usually points to a specific data type, range boundary, error, or visibility assumption that can be checked.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.