Fix the Excel AVERAGEIF #DIV/0! Error Caused by Text Numbers
Question details
The AVERAGEIF function is returning a #DIV/0! error, primarily because the numerical values in the calculation range are stored as text or the criteria do not precisely match the data.
- Product
- Excel
- Device & OS
- Desktop
- Scenario
- Calculating the average of a specific data range based on text or numerical criteria using the AVERAGEIF function.
- Observed behavior
- The AVERAGEIF formula correctly calculates in Excel for the Web but evaluates to a #DIV/0! error in the desktop application.
Before altering your formulas, ensure that your data range actually contains numbers and that your criteria exact-match the text in your dataset, without any unintended spaces.
Convert Text to Numbers and Remove Currency Symbols
Use this solution to remove text characters from numerical values and properly format the cells so Excel can recognize them in formulas.
A common cause of the #DIV/0! error in AVERAGEIF is that the values in the average range are formatted as text (e.g., manually typed '$100'). Excel cannot average text strings, which leads to an empty calculation pool and triggers a divide-by-zero error.
Highlight the entire column or range of cells containing the numbers you wish to average.
Press the Ctrl + H shortcut on your keyboard to open the Find and Replace dialog box.
In the 'Find what' field, type the currency symbol or text character causing the issue (like '$'). Leave the 'Replace with' field completely blank, then click 'Replace All'.
Keep the range selected, right-click, and choose 'Format Cells'. Navigate to the 'Number' or 'Currency' tab to correctly format the values as numeric data.
Clean Up Extra Spaces in Criteria and Data Ranges
Use this method if your numbers are correctly formatted but the formula still fails due to hidden spaces in your matching criteria.
Fix the AVERAGEIF #DIV/0! Error in WPS Spreadsheet
WPS Spreadsheet provides powerful built-in error checking to help you effortlessly detect text stored as numbers and resolve formula calculation errors. Enjoy a seamless experience with an interface that supports all standard spreadsheet functions.
- 1. Open Your Data File: Launch WPS Spreadsheet and open the workbook containing your AVERAGEIF calculation.
- 2. Locate Text Numbers: Highlight the range of cells showing a small green triangle in the top-left corner, which indicates a number stored as text.
- 3. Click the Warning Icon: Click the yellow warning icon that pops up immediately beside your highlighted selection.
- 4. Convert Data Format: Select 'Convert to Number' from the drop-down menu to instantly fix the data type.
- 5. Verify Formula: Check your AVERAGEIF formula cell; the #DIV/0! error will automatically resolve, displaying the correct average.
Frequently Asked Questions
Why does AVERAGEIF give a #DIV/0! error when my criteria clearly exist?
This happens if the cells containing the values you want to average are formatted as text instead of numbers, or if the lookup criteria contain hidden spaces that prevent an exact match, resulting in zero matching cells.
How can I hide or ignore #DIV/0! errors in my spreadsheet totals?
You can wrap your AVERAGEIF formula inside the IFERROR function to handle the error gracefully. For example, using =IFERROR(AVERAGEIF(range, criteria, average_range), 0) will display a zero instead of the error code.
Why does the AVERAGEIF formula work in Excel for the Web but not the Desktop app?
Excel for the Web sometimes processes implicit data type conversions differently or ignores minor regional formatting discrepancies, while the desktop version strictly requires numbers to be formatted as numerical data types to calculate properly.
How do I quickly spot cells formatted as text?
Both Excel and WPS Spreadsheet typically display a small green triangle in the top-left corner of a cell if a number is being incorrectly stored and read as text.
