How to Fix Excel AVERAGE Returning #DIV/0! for Time Values
Question details
The user needs to resolve a #DIV/0! error that occurs when attempting to calculate the average of a range of time values.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating the average of time data imported from another system, where the data appears as standard times but is internally stored as text.
- Observed behavior
- The AVERAGE function returns a #DIV/0! error because it ignores the text values, finds no numeric data in the range, and consequently attempts to divide by zero.
Ensure your time values do not contain invisible leading or trailing spaces, which can prevent spreadsheet software from recognizing them as numbers even after conversion attempts.
Convert Text to Numbers Using Paste Special
Force Excel to coerce text-formatted time values into real numeric data by adding zero to the range using the Paste Special feature.
When data is imported from external software, times and dates are frequently stored as text. Because the AVERAGE function completely ignores text strings, it calculates zero values to divide by, resulting in the #DIV/0! error. Using the Paste Special trick forces Excel to evaluate the text as a mathematical expression, converting it into a time serial number.
Find a completely blank cell anywhere in your worksheet, click on it, and press Ctrl+C (or right-click and select Copy). This cell holds a value of zero.
Select the entire range of time values (e.g., B2:B46) that are causing the #DIV/0! error in your AVERAGE formula.
Right-click anywhere within the highlighted time range and select 'Paste Special' from the context menu.
In the Paste Special dialog box, locate the 'Operation' section, select the 'Add' radio button, and click OK.
If the numbers turn into decimals, right-click the range, select 'Format Cells', and apply your desired Time format. Your =AVERAGE(B2:B46) formula will now calculate correctly.
Use the VALUE Function
Extract the numeric time value from the text string into a helper column.
Fix Formula Errors Quickly in WPS Spreadsheet
WPS Spreadsheet provides excellent compatibility with Excel formulas and offers an intuitive one-click data conversion tool to easily fix issues like text-stored times without needing complex workarounds.
- 1. Open your Document in WPS: Launch WPS Spreadsheet and open the file containing your time data.
- 2. Highlight the Problematic Cells: Select the range of time values that are currently formatted as text.
- 3. Use Smart Error Checking: Click the small exclamation mark warning icon that appears next to your selection.
- 4. Convert to Number: Select 'Convert to Number' from the dropdown menu to instantly fix the data format.
- 5. Calculate the Average: Your existing AVERAGE formula will instantly update and display the correct result instead of a #DIV/0! error.
Frequently Asked Questions
Why does the AVERAGE function ignore my time values?
The AVERAGE function is designed to only process numeric values. If it encounters text strings, blank cells, or logical values, it skips them. When times are imported from external systems, they often come in as text, leading the function to find zero numeric values to average.
How can I easily tell if my time values are stored as text?
By default, spreadsheet applications align text to the left side of a cell and numbers (including dates and times) to the right. If your time values align to the left without you manually changing the alignment, they are likely stored as text. You can also test a cell by typing =ISNUMBER(A1); if it returns FALSE, it is text.
Can I use the AVERAGEA function to prevent the #DIV/0! error?
While AVERAGEA will not return a #DIV/0! error (because it counts text cells as zero), it will give you an incorrect mathematical result. It will average the text cells as '0' instead of calculating the actual time value, resulting in a significantly skewed final average.
What if Paste Special doesn't fix the time conversion?
If the Paste Special method doesn't work, your data likely contains hidden non-breaking spaces or web characters. You will need to use the Find and Replace tool (Ctrl+H) to replace spaces with nothing, or use the TRIM and CLEAN functions in a helper column to strip out hidden formatting before converting.
