Why Is AVERAGE Not Working in Excel? Common Fixes

If AVERAGE is not working, Excel may not recognize the selected cells as numbers, the formula may point to the wrong range, or the result may be an error or an old calculation. With 10, 20, and 30 in B2:B4, enter =AVERAGE(B2:B4) in another cell; the result is 20.

What Excel AVERAGE Actually Calculates

AVERAGE adds the numeric values it finds in its arguments and divides their sum by the number of those values. In a cell reference such as B2:B10, it counts numeric cells, ignores genuinely blank cells, and ignores text entries. A numeric zero is not blank: it contributes zero to the total and counts as one value in the divisor.

This distinction explains many results that look wrong. Suppose a range contains 10, 20, a blank cell, and the text N/A. AVERAGE returns 15 because it averages only 10 and 20. If the third cell contains the number 0 instead of being blank, the average is 10: the calculation is (10 + 20 + 0) divided by 3.

When values are supplied directly as arguments, rather than through a cell reference, Excel handles some inputs differently. For example, logical values and numbers entered as text can be evaluated as arguments, while text and logical values inside a referenced range are normally ignored. For predictable results, place data in cells and average a clearly defined range.

Check the Formula and Its Range First

Before changing the data, check that the formula is spelled correctly and refers to the cells you intend. AVERAGE needs at least one argument, and its range should include the actual data rather than a heading, a total, or an unrelated column. Selecting a range that includes a total can inflate the result because the total is treated as another observation.

For instance, if B2:B5 contains four daily sales figures and B6 contains their sum, averaging B2:B6 is not the same as averaging the four days. Check the formula bar, click each range reference in the formula, and confirm the highlighted cells. A misplaced colon, missing row, or reference to another sheet can make a valid formula answer the wrong question.

Excel for Microsoft 365 on Windows commonly uses commas between function arguments. Some regional settings use semicolons instead, because the list separator depends on the computer’s settings. If Excel rejects a formula copied from a website, try the separator configured for your region. The range operator colon in B2:B10 is not a list separator and should remain a colon.

Confirm the formula starts with an equals sign and uses AVERAGE.
Check that the selected range contains the intended observations, not labels or totals.
Look for a leading apostrophe or an accidental space before the equals sign.
Check whether a filter, another worksheet, or a named range changes which cells are included.

Numbers Stored as Text

A cell can look like a number but still contain text. This often happens after importing a CSV file, copying data from a webpage, or pasting values with leading apostrophes or extra spaces. AVERAGE ignores text in a referenced range, so a column that appears to contain several amounts may contribute only the entries Excel recognizes as numeric.

One clue is alignment: numbers are usually right-aligned by default and text is usually left-aligned, though formatting can override this. Another clue is Excel’s warning indicator in a cell. You can also test a suspicious cell with ISNUMBER: if the test returns FALSE, Excel does not regard its contents as a number. Formatting a text value as Number alone does not convert the underlying text.

To convert a small batch, select the cells and use the warning menu’s Convert to Number command when Excel offers it. For imported data, review the import settings and remove unwanted spaces or characters. A helper column is useful when you want to keep the original data unchanged. If A2 contains text that represents the number 12, enter =VALUE(A2) in B2; it returns the number 12, which can then be included in an average. VALUE will not repair arbitrary labels such as N/A, so inspect and handle nonnumeric entries separately.

Be careful with currency symbols, thousands separators, and decimal marks. A string such as 1,25 may be parsed differently depending on regional settings. Confirm that converted results are plausible before using them; a successful conversion does not prove the source data was interpreted as intended.

Understand Blanks, Zeros, and Text Entries

Blank cells and zero values have different effects. A genuinely empty cell is skipped, while zero is included in the count. If a formula returns an empty string, such as a formula result that looks blank, AVERAGE generally treats that result as text and does not count it. This can explain why the average changes when a blank-looking cell is replaced with an explicit zero.

Decide what an empty-looking entry means in the real dataset. If it means “not measured,” excluding it is often appropriate. If it means “measured and the result was zero,” the zero should normally remain. Do not delete zeros merely to raise an average, or replace missing observations with zero without a sound reason: either choice changes the meaning of the calculation.

Likewise, labels such as N/A or Pending are text and are ignored when they are ordinary values in a referenced range. But an actual Excel error such as #N/A is not just a text label; an error in the referenced data can cause AVERAGE to return that error. Find out whether the cell contains text or an Excel error before deciding how to clean it.

Fix Error Messages and Unexpected Results

The error message usually narrows down the cause. #DIV/0! means AVERAGE found no numeric values to divide by. The range may be empty, contain only text, or contain formulas that return text. Convert valid numeric text, check the range, and confirm that there is at least one number.

If the result is #N/A or another error, inspect the cells in the referenced range for the same error. AVERAGE does not automatically skip error values. Correct the source error if possible; suppressing it without understanding its cause can conceal missing or invalid data. A #NAME? result often points to a misspelled function name or a formula that Excel cannot interpret. A formula that appears as text instead of calculating may have been entered in a cell formatted as Text, or may have an apostrophe before the equals sign.

When a displayed average seems impossible, check for hidden assumptions too. The range may include a zero, a duplicate record, a subtotal, or a value with more decimal places than the cell display shows. Select the result and increase the displayed decimal places from Home > Increase Decimal to see whether rounding is only a display issue. Changing number format alters what you see, not the stored calculation.

Filtered Rows and Hidden Data

AVERAGE includes numeric values in hidden rows and in rows removed from view by a filter. If you intend to calculate only the visible filtered results, use SUBTOTAL instead. With 10, 20, 30, and 40 in B2:B5, and a filter hiding the row containing 20, =SUBTOTAL(1,B2:B5) returns 26.6667 (the average of 10, 30, and 40), rather than 25.

Function number 1 tells SUBTOTAL to calculate an average. Filtered-out rows are excluded. If you also want to exclude rows hidden manually, use function number 101 instead: with the same visible values and a manually hidden row containing 30, =SUBTOTAL(101,B2:B5) averages only the visible entries and returns 23.3333 (10, 20, and 40). Choose the version that matches how the rows were hidden. SUBTOTAL is not a replacement for AVERAGE when you want every row counted regardless of visibility.

Check Calculation and Display Settings

Sometimes the formula is correct but the result has not updated. In Excel for Microsoft 365 on Windows, check Formulas > Calculation Options and select Automatic. In Manual calculation mode, Excel may leave formulas showing an old result until recalculation. Use Formulas > Calculate Now to recalculate, or press F9 on Windows. A full recalculation shortcut is Ctrl+Alt+F9.

Also distinguish a formula from a displayed result. If you see the literal text =AVERAGE(B2:B4) in the cell, check whether Show Formulas is enabled, whether the cell is formatted as Text, and whether the entry has a leading apostrophe. In the first case, toggle Show Formulas from the Formulas tab; in the other cases, change the cell format to General, then re-enter the formula. Simply changing the format may not convert a formula that Excel already stored as text.

On Excel for the web, calculations normally update automatically, but formula text, cell formatting, and imported data can still cause the same underlying problems. Mac menu and keyboard details can differ from Windows, so use the Calculation and formula-display controls in the Mac ribbon or Excel settings if the Windows shortcut is unavailable.

When to Use a Different Average

AVERAGE gives every included value equal weight. That is appropriate for questions such as “What was the mean of these daily readings?” It is not always the right statistic for the decision at hand. If each observation represents a different number of units, a simple mean can mislead. For example, averaging two class percentages equally does not produce the overall percentage when the classes have different enrollment sizes.

For a weighted average, multiply each value by its weight, add those products, and divide by the sum of the weights. With scores in B2:B3 of 80 and 90, and student counts in C2:C3 of 10 and 30, enter =SUMPRODUCT(B2:B3,C2:C3)/SUM(C2:C3). The result is 87.5: (80 × 10 + 90 × 30) ÷ 40. This answers a different question from AVERAGE(B2:B3), which returns 85 because it gives the two class scores equal weight.

If outliers make the mean unrepresentative, consider whether the median better describes a typical value. The median is the middle value after sorting and is less affected by an unusually large or small observation. For a numeric dataset in B2:B6, =MEDIAN(B2:B6) returns the middle value; with 1, 2, 3, 4, and 100, it returns 3, whereas AVERAGE returns 22. A different result is not evidence that one function is broken: the functions answer different questions.

Frequently Asked Questions

Why does AVERAGE return #DIV/0!?
Excel has not found any numeric values in the supplied arguments. Check for an empty or incorrect range, numbers stored as text, or formulas that return text. Convert valid numeric text and verify that at least one genuine number is included.

Why is AVERAGE ignoring numbers in my cells?
The contents may be numbers stored as text, which commonly happens after copying or importing data. Test a cell with ISNUMBER, convert valid values to numbers, and check for spaces or symbols that prevent conversion. Applying a Number format by itself does not change text into a number.

Does AVERAGE count blank cells and zeros?
It ignores genuinely blank cells and counts numeric zeros. A formula that returns an empty string is generally treated as text and skipped. Decide whether a zero means a real measured value or whether an empty cell means missing data before interpreting the result.

Why does AVERAGE include rows hidden by a filter?
AVERAGE includes numeric values in hidden and filtered-out rows. Use SUBTOTAL with function number 1 to average filtered visible rows; use 101 if manually hidden rows should also be excluded. Confirm the row-visibility behavior you want before replacing the formula.

Why does my AVERAGE result look wrong even though there is no error?
Check the exact range, included zeros, duplicate values, hidden rows, and any subtotal accidentally included. Increase the displayed decimal places to rule out rounding, and consider whether a weighted average or median better fits the question. The formula may be calculating correctly while the chosen range or type of average does not match your intention.

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.