How to Calculate the Correct Average of Times in Excel
Question details
The user needs to calculate an accurate average of time values in Excel, but is encountering incorrect results due to text formatting or hidden date components.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Averaging time values or total durations in a spreadsheet.
- Observed behavior
- The AVERAGE function returns unexpected or incorrect results because the time values are stored as text strings, contain hidden date components, or the final result is formatted incorrectly.
Ensure that your time data is recognized by Excel as actual numbers or serial time values, not plain text. You can quickly test this by checking if the values align to the right (numbers/times) or left (text) by default.
Extract Real Time Values and Calculate the Average
Use the MOD function to strip out any hidden date components, ensuring you are only averaging the actual time values.
Excel stores dates and times as serial numbers, where the integer portion represents the date and the decimal portion represents the time. If your cells contain both, averaging them directly will skew the time result.
In an empty column next to your time data (e.g., E2 for data in D2), enter the formula =MOD(D2,1) and press Enter.
Click the small square at the bottom-right corner of cell E2 and drag it down to apply this formula to all cells in your data range.
In a new cell, type =AVERAGE(E2:E100) (adjusting the range to match your newly extracted times) to calculate the final average time.
Right-click the cell containing the average result, select 'Format Cells', choose 'Custom', and enter [h]:mm to properly display averages that may exceed 24 hours.
Convert Text Strings to Time Values Before Averaging
If your time data was imported from another system, it might be stored as text. You must convert it to serial time values before the AVERAGE function can process it.
Calculate Time Averages Seamlessly with WPS Spreadsheet
WPS Office offers a highly compatible and intuitive Spreadsheet application that effortlessly handles complex time and date calculations. Easily format, extract, and average time values with zero hassle.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your workbook containing the time data.
- 2. Apply the MOD formula: Type =MOD(A2,1) in an adjacent helper column to extract pure time values from dates.
- 3. Use the AVERAGE function: Select a blank cell and enter =AVERAGE(B2:B100) to instantly calculate the mean time.
- 4. Set custom time formatting: Right-click the result, select Format Cells, and apply the [h]:mm custom format to display the correct duration.
Frequently Asked Questions
Why does my average time calculation show a #DIV/0! error?
This error occurs if the AVERAGE function only encounters text values or blank cells. Excel ignores text strings, so if all your times are formatted as text, it divides by zero. You need to convert them to actual time values using TIMEVALUE or by multiplying the text by 1.
How do I display an average time that goes beyond 24 hours?
By default, Excel rolls over time formats every 24 hours. To display cumulative hours, right-click the cell, click Format Cells, select Custom, and use the format code [h]:mm.
Can I type a fixed time directly into an Excel formula?
Yes, you can include fixed times inside formulas by using the TIME function, such as TIME(15,21,0) for 15:21:00, or by using the TIMEVALUE function like TIMEVALUE("3:21 PM").
