Fix Excel AVERAGE Returning 0 Instead of the Expected Result
Question details
The user needs to identify why an AVERAGE formula returns 0 instead of the expected values, such as 1.4 or 3.89.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user is averaging values in a worksheet and has already tried IFERROR without correcting the result.
- Observed behavior
- The AVERAGE formula displays 0 even though the source data should produce a nonzero average.
Create a small copy of the workbook with sensitive information removed. In the copy, note the exact AVERAGE formula and inspect the cells in its referenced range, including any hidden or filtered rows.
Check the AVERAGE range and convert text values to numbers
Use this solution when the source cells may contain numbers stored as text or when the formula references the wrong cells.
AVERAGE calculates numeric values but can ignore entries stored as text when they are included through a cell reference. IFERROR only handles errors; it does not convert text to numbers or correct an incorrect range.
Select the formula cell and check the formula bar. Confirm that the reference, such as =AVERAGE(B2:B10), includes the intended cells and does not point to blank cells or a different column.
Select the source cells and open Home > Number Format. Look for a warning icon or values aligned as text; use the warning menu and choose Convert to Number when available.
In an empty helper column, enter =VALUE(B2), fill the formula down, and then average the converted results with =AVERAGE(C2:C10). Remove extra spaces or nonnumeric characters if VALUE reports an error.
Open Formulas > Calculation Options and select Automatic, then press F9 to recalculate the workbook.
Check hidden, filtered, and excluded rows
Use this check when the visible worksheet contains values but the formula may be excluding them or referencing only a partial range.
Calculate accurate averages in WPS Spreadsheet
WPS Spreadsheet can inspect AVERAGE references, identify text-formatted values, convert source data to numbers, and recalculate an .xlsx workbook without changing the familiar spreadsheet workflow.
- 1. Open the workbook: Launch WPS Office, choose Spreadsheet, and open the affected .xlsx file from File > Open.
- 2. Inspect the formula: Select the result cell and review the formula bar to confirm that AVERAGE references the intended range.
- 3. Convert source values: Select text-formatted values, use the warning menu to choose Convert to Number, or enter =VALUE(cell_reference) in a helper column.
- 4. Check worksheet visibility: Open Data > Filter to clear filters and use the row-header context menu to unhide rows that may contain expected values.
- 5. Recalculate and save: Choose Formulas > Calculation Options > Automatic, press F9, and save the corrected workbook in .xlsx format.

Frequently Asked Questions
Why does Excel AVERAGE ignore some numbers in my range?
The cells may contain numbers stored as text, extra spaces, currency symbols, or other nonnumeric characters. Select the cells and use the warning menu's Convert to Number option, or convert them with VALUE before using AVERAGE.
Can hidden rows cause an AVERAGE formula to return 0?
Hidden rows can affect what data is included depending on the formula and how the rows were hidden. Clear filters from Data > Filter, unhide the relevant rows, and verify that the formula range includes them.
What should I check if the AVERAGE formula references the wrong cells?
Select the result cell and inspect the formula bar. Compare the referenced range with the actual data, then edit the cell references or select the correct range directly in the worksheet.
Does IFERROR convert text values into numbers?
No. IFERROR only replaces a formula error with another value or message. It cannot convert text-formatted numbers, repair an incorrect range, or include filtered-out data.
How can I share an AVERAGE problem safely for troubleshooting?
Save a small copy of the workbook, remove names and sensitive data, and leave only the relevant formula and sample values. Share that copy or provide a file-sharing link if an attachment is unavailable.




