How to Fix Excel AVERAGE Returning #DIV/0! When IFS Results Look Numeric
Question details
The user is encountering a #DIV/0! error when calculating the AVERAGE of a column generated by an IFS formula, while SUM and COUNT functions also unexpectedly return zero.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating the average, sum, or count of numerical results produced by an IFS function.
- Observed behavior
- The AVERAGE function returns a #DIV/0! error, and SUM/COUNT return zero because the apparent numbers are being returned or stored as text strings instead of actual numeric values.
Before modifying your formulas, check if the numbers in your target column are aligned to the left side of the cell. Left-aligned numbers are a strong indicator that Excel is reading the values as text rather than numerical data.
Remove Quotation Marks from the IFS Formula
The most direct way to fix this issue is to ensure your original IFS formula returns actual numbers instead of text strings.
When you enclose numbers in quotation marks within a formula (e.g., "5"), Excel treats them as text. Functions like AVERAGE, SUM, and COUNT ignore text values. If a range only contains text, the COUNT is zero, leading to a #DIV/0! error when AVERAGE attempts to divide the total sum by zero.
Click on the first cell in your column that contains the IFS formula.
Look at the formula bar at the top and remove any quotation marks around your numeric outputs. For example, change =IFS(C2>96,"5",C2>92,"4") to =IFS(C2>96,5,C2>92,4).
Press Enter to save the corrected formula. Then, click and drag the fill handle (the small square at the bottom-right corner of the cell) down to apply the updated formula to the rest of the column.
Your =AVERAGE() formula should now automatically update and display the correct numerical result without the #DIV/0! error.
Convert Text to Numbers Using the VALUE Function
If you cannot change the original formula or are dealing with copied static data, you can convert the text-stored numbers into real numbers using a helper column.
Easily Manage and Troubleshoot Formulas with WPS Office
WPS Spreadsheet is a powerful, free alternative to Microsoft Excel that fully supports IFS, AVERAGE, and VALUE functions. Its intuitive interface and built-in error checking make it incredibly easy to identify and fix numbers accidentally stored as text.
- 1. Open your workbook: Launch WPS Spreadsheet and open your existing Excel file.
- 2. Locate the error: Select the cell showing the #DIV/0! error to trace it back to the source IFS column.
- 3. Use Error Checking: Look for the small green triangle in the corner of text-formatted number cells. Click the warning icon that appears and select 'Convert to Number' to instantly fix the data type.
- 4. Apply formula updates: Alternatively, edit your IFS formula in the WPS formula bar to remove quotation marks around numbers, seamlessly recalculating your averages in real-time.
Frequently Asked Questions
Why does AVERAGE return #DIV/0! even when cells look like numbers?
The AVERAGE function calculates the sum of a range divided by the total count of numbers in that range. If all cells are stored as text (often caused by quotation marks in formulas or imported data), the count is zero. Dividing by zero triggers the #DIV/0! error.
How can I quickly tell if my numbers are formatted as text?
By default, Excel and WPS Spreadsheet align text to the left and numbers to the right. If your numbers are hugging the left side of the cell, or if they display a small green triangle in the top-left corner, the software is treating them as text.
Does changing the cell format to 'General' or 'Number' fix text numbers?
Not immediately. Changing the format from the ribbon dropdown only changes the display rules, not the underlying data type of existing entries. You must re-enter the data, use the 'Text to Columns' feature, or apply the VALUE function to force the conversion.
Can other error values in a range cause AVERAGE to fail?
Yes. If any single cell within the range you are averaging contains an error (such as #N/A, #VALUE!, or #DIV/0!), the AVERAGE function will halt calculation and return that exact same error.
