Average Formula Not Working in Excel? Fix It

If the values are in B2:B5, enter =AVERAGE(B2:B5); with 10, 20, 30, and 40 in those cells, the result is 25. If Excel shows a different result, a formula error, or no update, check whether the inputs are real numbers, the selected range is correct, and calculation is set to Automatic.

How Excel’s AVERAGE function works

AVERAGE adds the numeric values in its arguments and divides their total by the number of numeric values. In the example above, 10 + 20 + 30 + 40 equals 100, and 100 divided by 4 equals 25. The function can use a range, separate cell references, or numbers entered directly, but a range is usually easier to check and maintain.

Excel treats different cell contents differently. A numeric zero is included in the calculation; a genuinely blank cell is ignored. Text in a referenced cell is also ignored, even if it looks like a number, and logical values such as TRUE or FALSE in cells are ignored. By contrast, an error value in the referenced range usually makes the AVERAGE result an error. Those distinctions explain many results that seem inexplicable at first.

AVERAGE does not automatically know which entries are meaningful. For example, if a zero means a real measurement, it belongs in the average. If zero is being used as a placeholder for missing data, including it may make the result misleading. Decide what the data represents before changing the formula to exclude values.

Check the range and the result first

A formula can calculate correctly and still use the wrong cells. Click the cell containing the result and inspect the formula bar. Confirm that the highlighted references cover every intended observation and do not include totals, headings, or unrelated values. A range such as B2:B20 includes both endpoints; B2:B19 does not include row 20.

Also check for numbers outside the selected range. If a new record was entered in B21 but the formula still refers to B2:B20, Excel will not include it. Convert the data to an Excel table if the list grows regularly: table columns generally expand as rows are added, making formulas easier to maintain. For a small one-time calculation, editing the range directly may be sufficient.

Compare the formula’s first and last row with the actual data.
Check whether a filter, hidden row, or grouped section has caused you to overlook entries. AVERAGE includes hidden and filtered-out numeric cells in a normal range.
Look for an accidental extra cell that contains a number, such as a subtotal, which may raise or lower the result.
Verify that the formula was entered in the intended cell and that it starts with an equals sign.

Filtering is a common source of confusion. AVERAGE includes values hidden by a filter; it does not mean average only the visible rows. If you need a visible-rows-only calculation, use a function designed for that purpose, such as SUBTOTAL, and choose its average operation. Do not replace AVERAGE unless excluding filtered rows is actually what you want.

Numbers stored as text are a leading cause

Values imported from a website, a text file, or another system can look like numbers while being stored as text. AVERAGE ignores text entries within a referenced range, so it may return an average based on only some of the visible values. A cell may also contain a leading apostrophe, an extra space, or an unrecognized decimal or thousands separator.

Click a suspicious cell and inspect the formula bar. A green triangle or a warning icon may indicate that a number is stored as text, although the warning is not shown in every case. You can select the affected cells and use the warning menu’s Convert to Number option when Excel offers it. Another option for a consistently formatted imported column is Data > Text to Columns, then select Finish; check the result first if the data uses unusual separators.

For a single text value, a conversion formula can help. Assuming B2 contains the text 12.5, enter =VALUE(B2) in C2; the result is the numeric value 12.5 when that decimal format is recognized by your Excel settings. Copy the result down for the other rows, then use the converted column in the average. If conversion returns an error, inspect spaces and regional number formats rather than assuming the original value is valid.

Changing the cell format to Number alone does not convert text into a numeric value. Formatting changes how a value is displayed, not necessarily what the value is. After a conversion, test a few cells and confirm that the values behave like numbers—for example, they should participate in a simple sum.

Understand blanks, zeros, and excluded values

Blanks are skipped, but zero is counted. With 10, 20, and 0 in a range, the average is 10, because 30 divided by 3 is 10. If the zero represents a valid result, that is the appropriate arithmetic. If it means no measurement was recorded, replace the placeholder with a truly blank cell or use a rule that excludes zeros.

Likewise, a formula that displays an empty string is not the same as a numeric zero. A cell containing a formula that returns an empty string is generally treated as text by AVERAGE and is ignored. This can make two cells that look blank behave differently in other calculations, so inspect the formula bar when results are surprising.

When only some rows should count, use a conditional average rather than deleting data. For example, suppose A2:A5 contains East, West, East, East and B2:B5 contains 10, 20, 30, 50. Enter =AVERAGEIF(A2:A5,"East",B2:B5) to average the three East values; the result is 30 because (10 + 30 + 50) divided by 3 equals 30. The condition and the values-to-average ranges must align row by row.

Fix errors and unexpected results

If AVERAGE returns #DIV/0!, Excel has no numeric values to average in the supplied references. The range may be empty, contain only text, or refer to cells whose results are text. Confirm that at least one input is a real number and that the formula points to the correct range. AVERAGE does not return zero simply because a range contains no usable numbers.

If the result is an error such as #N/A or #VALUE!, find the cell producing the error. AVERAGE generally propagates errors in referenced cells instead of silently skipping them. Correct the source error where possible. Wrapping the whole calculation in IFERROR can hide the underlying problem, so use that approach only when a deliberate fallback result is appropriate and users will understand what it means.

An average that is unexpectedly high or low may be mathematically correct. Check for an outlier, a duplicated record, a subtotal included with detail rows, or a number stored with the wrong unit. For instance, entering 500 instead of 50 changes the average even though Excel has no way to know the entry is a typo. Sorting the data or reviewing its source can reveal issues that a formula cannot diagnose.

Check calculation settings and formula entry

If the inputs change but the displayed average does not, check calculation mode. In Excel for Microsoft 365 on Windows, select Formulas > Calculation Options > Automatic. In Manual mode, Excel may wait for a recalculation command. Press F9 to recalculate open workbooks; on a Mac, use Fn+F9 when the function keys are configured for hardware controls, or choose the calculation command from the Formulas tab.

Make sure the cell is not displaying the formula as text. If you see the characters =AVERAGE(B2:B5) instead of a result, the cell may be formatted as Text, or the entry may begin with an apostrophe or space. Change the format to General, then re-enter the formula—changing the format alone may not make Excel reinterpret an existing entry. Also check whether Show Formulas is on; pressing Ctrl+` on Windows toggles that display. The key is usually the grave-accent key, and its position can vary by keyboard. On Mac, use Control+` where that shortcut is available or turn off Show Formulas from the Formulas tab.

When Excel rejects the formula as invalid, check the spelling, parentheses, and list separator. The standard English-language formula shown here uses commas between arguments. Some regional settings require semicolons instead, so use the separator accepted by your Excel installation. In a formula with only one range, such as AVERAGE(B2:B5), there is no argument separator to change.

Choose the right averaging function

AVERAGE is appropriate when every numeric observation should have equal weight. It is not the same as an average of group averages if the groups contain different numbers of observations. For example, averaging two department averages gives each department equal weight, even if one department has far more records. To get the average across all underlying records, average the original values together.

Use AVERAGEIF when one condition determines which values count, or AVERAGEIFS when multiple conditions apply. Use SUBTOTAL when the calculation should respond to filtered or hidden rows, choosing its average operation and checking the function number for the behavior you need. These functions solve different problems; switching functions will not repair numbers stored as text or a mistaken range.

A weighted average is another distinct calculation. If each value has a different importance or quantity, a simple AVERAGE may not answer the intended question. For example, averaging per-item prices equally is different from calculating the average price across all units sold. Identify the denominator and weighting rule first, then build a formula from the actual data structure rather than treating every row as equally important.

Frequently Asked Questions

Why does Excel AVERAGE return #DIV/0!?
There are no numeric values in the cells the formula references. Check for an empty or incorrect range, numbers stored as text, or formulas that return text. Add or convert valid numeric inputs, then check the result again.

Does AVERAGE include zero values?
Yes. A numeric zero counts as an observation and affects the result. A truly blank cell is ignored. Decide whether zero represents a real measurement or a missing-value marker before excluding it.

Why is my average wrong when every cell looks like a number?
Some entries may be text, the range may omit rows or include a subtotal, or an outlier may be valid but unexpected. Inspect the formula bar and the range references, then verify questionable values against their source.

Why does my average not update after I change a value?
Check whether calculation is set to Manual under Formulas > Calculation Options. Set it to Automatic or recalculate with F9 on Windows. Also confirm that the edited cell is included in the formula’s range.

Does AVERAGE ignore filtered-out rows?
No. A normal AVERAGE range includes numeric values in filtered-out rows. Use SUBTOTAL when you want a calculation that responds to filtering, and confirm that its behavior matches whether rows are filtered or manually hidden.

Will formatting text as Number fix it?
Not by itself. Number formatting changes appearance, but a text value may remain text. Convert the entry to a number using Excel’s conversion option or a suitable conversion method, and then verify that AVERAGE includes it.

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.