Why Is AVERAGE Not Working in Excel?

Excel’s AVERAGE function usually appears not to work because the selected range contains numbers stored as text, an error value, or no numeric values at all. Check the range and cell contents first; for a basic calculation, with the values to average in B2:B10, use =AVERAGE(B2:B10). The sections below explain what Excel counts, how to fix common problems, and which alternative function fits special cases.

What AVERAGE includes—and ignores

AVERAGE adds the numeric values in its arguments and divides their sum by the number of numeric values. In a cell reference such as B2:B10, it includes numbers and numeric zeroes, but ignores blank cells, text, and logical values such as TRUE or FALSE. That behavior can make the result seem unexpectedly high or low if you thought every cell in the range would count.

For example, suppose B2 contains 10, B3 contains 20, B4 contains the text N/A, B5 is blank, and B6 contains 30. With those values in B2:B6, =AVERAGE(B2:B6) returns 20: Excel calculates (10 + 20 + 30) ÷ 3. It does not treat the text or blank cell as a zero. A zero that is actually entered as a number is different: it is included in the calculation.

There is a small but useful distinction between referencing cells and typing values directly into the function. Logical values and text representations of numbers typed directly as arguments can be counted, while text and logical values in referenced cells are ignored. For ordinary worksheets, the safest approach is to put genuine numbers in cells and average a clearly defined range.

Numbers stored as text

A cell can look like a number and still be stored as text. This often happens after importing data from a website, CSV file, or another system, or when a number has a leading apostrophe. AVERAGE ignores text in a referenced range, so the result may use only some of the visible entries—or return #DIV/0! if every apparent number is text.

To check, select a suspicious cell and look for a warning indicator or a number format that says Text. You can also test whether Excel recognizes it as numeric: with the suspected value in B2, =ISNUMBER(B2) returns TRUE for a number and FALSE for text. Formatting a text value as Number does not necessarily convert it; the underlying cell value must be changed.

Possible fixes include selecting the affected cells and choosing Convert to Number from the warning menu, if Excel offers it, or using Data > Text to Columns and then selecting Finish for a straightforward column of numeric text. Check the result afterward, especially if the source uses decimal or thousands separators that differ from your Excel regional settings. For a single simple text number in B2, =VALUE(B2) returns its numeric value; the formula result can then be copied and pasted as values if you need to replace the original data.

Errors inside the range

AVERAGE does not silently skip Excel errors. If even one referenced cell contains an error such as #N/A, #VALUE!, or #DIV/0!, the AVERAGE result will generally show that error too. Find the source cell before changing the averaging formula: the error may indicate a genuine issue in an upstream calculation, a lookup that found no match, or a deliberately marked missing value.

Use Excel’s Find command to search for an error value, or inspect the cells in the referenced range. If the error is intentional and should not count, decide how your workbook should represent missing data. Replacing an error with a blank may be appropriate in some cases, but it changes how the source data is treated. Avoid wrapping the entire calculation in IFERROR merely to hide a problem; that can conceal a broken input and make a blank or misleading result look valid.

If the function returns #DIV/0!, the range may contain no numeric values for AVERAGE to divide by. Confirm that the range is correct and that its entries are real numbers rather than text. A range containing only blanks, text, or logical values has no numeric count, so Excel cannot calculate an average.

Check the formula and cell references

A correct function can still give the wrong answer if it points to the wrong cells. Inspect the formula bar and confirm the range starts and ends where you expect. Look for an accidentally omitted last row, an extra header cell, or a reference that moved when the formula was copied. In Excel for Microsoft 365 on Windows, selecting the formula cell makes its referenced cells easier to identify on the worksheet.

Also check whether the workbook expects an average of all entries or only entries matching a condition. AVERAGE calculates across its supplied numeric arguments; it does not automatically exclude a category, date, or status. For example, with categories in A2:A6 and values in B2:B6, =AVERAGEIF(A2:A6,"East",B2:B6) averages the values whose corresponding category is East. If no entries meet the condition, the result can be #DIV/0! because there is nothing to average.

When copying a formula, relative references adjust to the new location. If every copied formula should use the same range, make the reference absolute with dollar signs—for example, change B2:B10 to $B$2:$B$10. This is especially relevant when filling a formula down a column or across a report.

Formula shown instead of a result, or result not updating

If the cell displays the literal text =AVERAGE(B2:B10) instead of a number, first check whether the cell is formatted as Text or whether an apostrophe appears before the equals sign. Change the cell format to General, then edit the entry and confirm it again; changing the format alone may not make Excel reinterpret a formula already stored as text. Also check whether Show Formulas is turned on. On Windows, Ctrl+` toggles formula display; on Mac, Control+` is commonly used, though the key may vary with the keyboard layout.

If the formula is valid but the answer stays old after an input changes, the workbook may be using manual calculation. In Excel for Microsoft 365 on Windows, go to Formulas > Calculation Options > Automatic. In Excel for Mac, use Formulas > Calculation Options and select Automatic. F9 recalculates open workbooks in Windows Excel; on a Mac, the recalculation shortcut may depend on the keyboard and function-key settings, so the ribbon command is a reliable alternative.

Another possibility is a circular reference: the average formula may include its own cell, directly or through another formula. For example, placing an average in B10 while averaging B2:B10 includes B10 in its own calculation. Move the result outside the input range or correct the range boundary. Excel may display a circular-reference warning, but reviewing the referenced cells is still worthwhile.

Hidden rows, filters, and averages of visible data

Filtering a list or hiding rows does not make AVERAGE ignore those values. AVERAGE works on the cells in its specified range, including rows that are hidden or filtered out. This is a common reason an average differs from the visible records on screen.

If you want a result that responds to a filter, use SUBTOTAL with its average operation. With values in B2:B10, =SUBTOTAL(101,B2:B10) calculates an average that excludes filtered-out rows and manually hidden rows. The function number 101 tells SUBTOTAL to average while excluding manually hidden rows; function number 1 also averages but includes manually hidden rows. Filtered-out rows are excluded by SUBTOTAL in either case. Check that this is the intended rule before using the result in a report.

For a one-time average of a few visible cells, you can also select only those cells and use the status bar’s Average calculation, if it is enabled. That display is not a worksheet formula, however, and it will not automatically provide a result in a cell.

Choose the right averaging function

Use AVERAGE for a straightforward arithmetic mean of numeric cells. Choose a more specific function when the business rule is different:

AVERAGEIF averages values matching one condition, such as a department or date threshold.
AVERAGEIFS averages values that meet multiple conditions, such as a department and a reporting period.
AVERAGEA includes logical values and text in its calculation, treating text in referenced cells as zero and TRUE as 1. That behavior is different from AVERAGE and should be chosen deliberately.
SUBTOTAL can average a filtered list while excluding filtered-out rows, which ordinary AVERAGE does not do.

These functions answer different questions, so switching functions is not automatically a fix. For instance, AVERAGEA may change the result by counting text as zero, but it does not turn text-formatted numbers into numeric values. Converting the source data is usually clearer when the cells are meant to contain numbers.

A quick troubleshooting sequence

When an average looks wrong, work through these checks in order. This helps isolate whether the problem is in the data, the formula, or the worksheet’s calculation settings:

Read the exact result: is it a number, #DIV/0!, another error, or the formula text itself?
Confirm that the formula references the intended input range and excludes headers or the result cell.
Check that the values Excel should count are numbers, not text, and look for errors in the range.
Decide whether blanks, zeroes, hidden rows, or filtered records should count under your rule.
If the result is stale, check calculation mode; if the formula is displayed, check cell format and Show Formulas.

These checks also help when the function is working correctly but the chosen range or assumptions do not match the question you intended to answer. Keep a small test case with a few known values if you need to verify a complicated report; a simple expected result makes it easier to spot a range or data-type problem.

Frequently Asked Questions

Why does AVERAGE return #DIV/0!?
Excel has found no numeric values to average in the supplied arguments. Check for an empty or incorrect range, text-formatted numbers, or an AVERAGEIF/AVERAGEIFS condition that matches no rows.

Does AVERAGE count blank cells as zero?
No. Blank cells in a referenced range are ignored. A numeric zero is counted, so a range containing 10, 20, and 0 has an average of 10.

Why is AVERAGE ignoring numbers I can see?
They may be numbers stored as text. Test a cell with ISNUMBER or use Excel’s Convert to Number option where available, then confirm the average again.

Does AVERAGE ignore filtered or hidden rows?
No. AVERAGE includes numeric values in its referenced range even when their rows are hidden or filtered out. SUBTOTAL with function number 101 is an option when you want the average of visible rows.

Why does the formula appear in the cell instead of the answer?
The cell may be formatted as Text, the entry may have a leading apostrophe, or Show Formulas may be enabled. Set the cell to General and re-enter the formula, and toggle off Show Formulas if the whole sheet displays formulas.

Is AVERAGE the same as AVERAGEA?
No. AVERAGE ignores text and logical values in referenced cells. AVERAGEA counts text as zero and logical values as 1 for TRUE and 0 for FALSE, so it can produce a different result.

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.