Average Function in Excel Not Working? Fix the Cause

If Excel’s AVERAGE function is not working, first check that the range contains numbers Excel recognizes and that the formula points to the intended cells. With values in A2:A10, enter =AVERAGE(A2:A10); if the result is an error or seems wrong, the cause is usually text stored as numbers, an incorrect range, an error value, or a misunderstanding about blanks and zeros.

What the AVERAGE function includes

AVERAGE adds the numeric values it finds and divides their total by the number of those values. It does not treat every occupied cell as a number. In a referenced range, it ignores blank cells, text, and logical values such as TRUE or FALSE. It does include zero, so a cell containing 0 counts in both the total and the count used to calculate the average.

For example, suppose A2:A6 contains the numbers 10 and 20, the text entry 30, a blank, and the number 40. If the 30 is stored as text, AVERAGE uses only the three numeric cells: (10 + 20 + 40) ÷ 3 = 23.33. That may look like a bad calculation, but Excel is following its rules. If the text entry is converted to the number 30, the average becomes (10 + 20 + 30 + 40) ÷ 4 = 25.

There is a small but important distinction between values in cells and values typed directly into a function’s argument list: text representations of numbers and logical values typed directly as arguments can be evaluated, while text and logical values in referenced cells are ignored. In ordinary worksheets, AVERAGE is usually given a cell range, so the range behavior is the one most likely to explain a surprising result.

Check the formula and the selected range

Before changing data, verify the formula itself. AVERAGE accepts one or more numbers, cell references, or ranges. A missing colon, an extra range, or a range that stops one row too early can produce a valid result that does not reflect all the intended data. Click the formula cell and inspect the colored outline Excel displays around referenced cells, or select the formula and check its references in the formula bar.

Confirm that the formula begins with an equals sign and spells AVERAGE correctly.
Check that the first and last rows in the range include all the values you intend to average.
Look for a second range or cell reference that was included accidentally.
If copying a formula down a column, check whether its references shifted as intended. Use dollar signs for references that must stay fixed.

For example, if the numbers to average are in B2:B10, averaging B2:B9 omits the last value without producing an error. If the range contains a heading, such as “Score” in B1, that text is ignored, but it is still clearer to select only the data rows. Also consider whether the dataset has changed since the formula was created: a range that once covered all records may not automatically include new rows added below it.

Find numbers that are actually stored as text

Imported CSV files, copied web data, and entries with a leading apostrophe can look numeric while remaining text. AVERAGE ignores text in a referenced range, which can make the answer too high or too low depending on which values were skipped. Common clues include numbers aligned differently from nearby entries, green warning indicators, or a cell that does not behave like a number when you sort or calculate with it.

To test an individual cell, use ISNUMBER. With a suspect value in A2, enter =ISNUMBER(A2) in another cell. TRUE means Excel recognizes the value as numeric; FALSE means it does not. Check a few cells, especially imported values or entries that came from different sources. A cell formatted as Text before data entry can also cause a typed number to be stored as text.

Possible fixes include selecting the affected cells and using the warning menu to convert them to numbers, or using Data > Text to Columns and completing the wizard when the data is a straightforward column of numeric text. You can also re-enter a value after setting the cell format to General or Number. Changing the number format alone does not convert existing text into numeric values; it changes how a value is displayed, not what kind of value is stored.

VALUE can convert a compatible text value to a number in a helper column. With numeric text in A2, enter =VALUE(A2) in B2 and fill the formula down, then average the converted results in column B. This approach is useful when you want to preserve the original imported data. Be aware that conversion can depend on regional settings: decimal and thousands separators that do not match Excel’s expected format may need to be corrected first.

Understand blanks, zeros, and missing observations

A genuinely empty cell is ignored, but a zero is included. Those are not interchangeable: if a zero represents a real measurement, it belongs in the average; if it represents missing information, including it may lower the result incorrectly. Decide what the data means before trying to make the formula skip zeros. A blank formula result, such as a formula returning an empty string, may look blank but is not identical to an untouched empty cell in every Excel operation.

For a simple example, the average of 10, 20, and 0 is 10, because the sum is 30 and there are three numeric values. If the zero is actually a placeholder for “not recorded,” averaging only 10 and 20 gives 15 instead. Do not remove or exclude zeros merely because the answer looks unexpected; first establish whether zero is a valid observation in the dataset.

If zeros should be excluded by design, use AVERAGEIF. With measurements in A2:A10, enter =AVERAGEIF(A2:A10,">0") to average only positive values. This excludes zero and negative values as well, so it is appropriate only when all values to retain should be greater than zero. If negative values are valid but zeros are placeholders, filter or prepare the data using a method that matches that rule rather than silently excluding all negatives.

Diagnose errors such as #DIV/0! and #VALUE!

AVERAGE returns #DIV/0! when it has no numeric values to average. This often happens because the selected range contains only text and blanks, because numeric-looking entries are stored as text, or because the range points to the wrong cells. Check the range and test suspected numbers with ISNUMBER before treating the error as a formatting problem.

An error already present in a referenced cell can also cause AVERAGE to return an error instead of a numeric result. Find errors such as #N/A or #VALUE! within the input range and decide how they should be handled. Replacing an error with zero changes the calculation by adding a numeric observation, so that is not a neutral fix. Correct the source error when possible; otherwise, define a clear rule for excluding or treating that record.

IFERROR can display a friendlier message when a calculation fails, but it does not repair the underlying data. With values in A2:A10, this formula shows a message instead of an error: =IFERROR(AVERAGE(A2:A10),"Check numeric data"). Use this for presentation only when appropriate. If a report needs to distinguish “no numeric entries” from other problems, investigate the source rather than hiding every error behind the same message.

Check calculation mode and formula display

If a formula appears to be correct but its result does not update after you change a value, check whether Excel is calculating automatically. In Microsoft 365 for Windows, go to Formulas > Calculation Options and select Automatic. When calculation is set to Manual, formulas may keep showing results from an earlier calculation until you recalculate. Press F9 to recalculate formulas in open workbooks; on a Mac, use the corresponding Excel recalculation shortcut shown for your keyboard and version, since function-key behavior can depend on macOS settings.

Also check whether Excel is showing the formula itself rather than its result. If a cell displays =AVERAGE(A2:A10) as text, the cell may be formatted as Text, the formula may have a leading apostrophe, or Show Formulas may be enabled. Change the cell format to General, then re-enter the formula so Excel evaluates it. Toggle formula display from the Formulas tab using Show Formulas; on Windows, Ctrl+` is the keyboard shortcut. On a Mac, the shortcut can vary by keyboard layout, so use the ribbon command if that key combination is unavailable.

Choose the right average for the question

AVERAGE calculates the arithmetic mean, which is useful when each numeric observation should have equal weight. It is not always the right summary. AVERAGEA treats text and FALSE values in references differently from AVERAGE, while AVERAGEIF and AVERAGEIFS let you calculate averages based on one or more criteria. For example, a user may want an average for one department, one date range, or positive values only. Choose based on the meaning of the data, not simply on which function returns the preferred number.

Hidden rows are another common source of confusion. AVERAGE includes values in hidden rows if those cells are within the referenced range; hiding rows changes what is displayed, not what AVERAGE calculates. If you need a subtotal that responds to filtering, SUBTOTAL with function number 1 calculates an average while excluding rows filtered out of the list. Function number 101 also excludes manually hidden rows. For a filtered table with values in B2:B20, enter =SUBTOTAL(1,B2:B20). This differs from AVERAGE, so use it only when the visible-row behavior is intended.

Formatting can also make a correct average appear wrong. A result formatted as a percentage may display 25% when the stored result is 0.25; a date-formatted result can appear as a calendar date because Excel dates are stored as serial numbers. Use Home > Number Format to select a suitable display, and increase decimal places if rounding hides detail. Formatting changes appearance, not the underlying average.

A reliable troubleshooting sequence

When the answer still looks wrong, work through the checks in order rather than rewriting the formula repeatedly. This makes it easier to find whether the problem is the range, the data, or the intended definition of an average.

Click the formula cell and confirm the function and referenced range.
Count the observations you expect to include, then inspect the corresponding cells for blanks, text, zeros, and errors.
Test suspicious values with ISNUMBER and convert numeric text only when it represents a real numeric observation.
Decide whether zeros, hidden rows, or missing records should be included, and select AVERAGE or a criteria-based alternative accordingly.
Check calculation mode, formula display, and number formatting if the value does not refresh or looks unlike the expected result.

For a quick cross-check, calculate the count of numeric entries separately. With the same data in A2:A10, =COUNT(A2:A10) returns the number of numeric cells AVERAGE can use from that range. If that count is smaller than the number of observations you expect, the discrepancy points toward text, blanks, or an incorrectly selected range. COUNT is a diagnostic aid, not proof that every included value is meaningful.

Frequently Asked Questions

Why does Excel AVERAGE ignore numbers in some cells?
Those entries may be numbers stored as text. AVERAGE ignores text in a referenced range, even when the text looks numeric. Test a cell with ISNUMBER and convert compatible text values to numbers if they are intended to be measurements.

Does AVERAGE include zero in Excel?
Yes. Zero is a numeric value, so it is included in both the sum and the count. If zero means missing data in your worksheet, use a method that excludes it only if that matches the rules for your dataset.

Why does AVERAGE return #DIV/0!?
The selected range may contain no numeric values Excel can use. Check for text-formatted numbers, an incorrect range, or a range containing only blanks. An existing error in a referenced cell can produce a different error that needs to be traced to its source.

Does AVERAGE include hidden or filtered rows?
Yes. AVERAGE includes numeric values in hidden rows that remain inside its range. Use SUBTOTAL when you need an average that responds to filtered rows, and choose its function number according to whether manually hidden rows should also be excluded.

Why does my average not change when I edit a value?
Excel may be set to manual calculation, or the formula may refer to a different cell than the one you edited. Check Formulas > Calculation Options, verify the range, and recalculate if necessary.

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.