How to Average Only Minutes from Time Values in WPS Office Spreadsheets
Question details
The user wants to calculate an average of only the minute components from time values in a spreadsheet, ignoring the hour portion, using WPS Office Spreadsheets.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has time values such as 12:10 AM and 2:05 PM and wants to average only the minutes, for example returning 7.5 instead of a full time average.
- Observed behavior
- Spreadsheet applications normally average complete time values including hours, so the goal is to extract and average only the minute component before calculating the result.
Confirm that your time values are stored as actual time cells rather than text, because Excel and WPS Spreadsheets store times as serial numbers that the MINUTE function can read correctly.
Average Only Minutes with a MINUTE Helper Column
Use this method in any version of WPS Office Spreadsheets when you want a clear, auditable calculation that separates the minute component before averaging.
The MINUTE function returns the minute component of a time value as a number from 0 to 59. By placing each result in a helper column, you can use a standard AVERAGE formula on those extracted values.
Click the cell next to your first time value, such as B2, type =MINUTE(A2), and press Enter.
Drag the fill handle from B2 down through the last time value to extract the minute component for each time.
In an empty cell, type =AVERAGE(B2:B10) and press Enter. The result is the average of only the minute components.
Use a Single Array Formula with AVERAGE and MINUTE
Use this method if your WPS Office version supports dynamic arrays or legacy array formulas and you prefer not to add a helper column.
Calculate Minute-Only Averages in WPS Office Spreadsheets
Open your workbook in WPS Office Spreadsheets, use the MINUTE function to extract minutes from time values, and apply AVERAGE to get the minute-only average. WPS Office supports the same formula workflow and Excel file compatibility.
- 1. Open your workbook in WPS Office Spreadsheets: Launch WPS Office and open the Excel file that contains the time values you want to average.
- 2. Add a MINUTE helper column: Click the first cell of an empty column, type =MINUTE(A2), and press Enter, then fill it down alongside your time values.
- 3. Average the extracted minutes: Click an empty cell, type =AVERAGE(B2:B10), and press Enter to return the minute-only average.
- 4. Review the result: Check the formula result. For 12:10 AM and 2:05 PM, the result is 7.5, excluding the hour components.
Frequently Asked Questions
Why does a normal AVERAGE formula include hours when averaging time values?
Excel and WPS Spreadsheets store time as a fraction of a day, so AVERAGE calculates the full time serial value. Use the MINUTE function to isolate only the minute component before averaging.
Can I average only minutes without adding a helper column?
Yes. In current WPS Office versions with dynamic array support, use =AVERAGE(MINUTE(A2:A10)) and press Enter. In older versions, press Ctrl+Shift+Enter to enter it as an array formula, or use a helper column.
What result should I get for 12:10 AM and 2:05 PM?
The MINUTE function returns 10 and 5, so the average of only the minute components is 7.5.
Does this formula work if my time values are formatted as text?
No. The MINUTE function requires actual time serial values. If the cells are text, convert them with the TIMEVALUE function or change the cell format to a time format first.
Does WPS Office support dynamic array formulas like Excel 365?
Current versions of WPS Office support many dynamic array formulas. If your version does not support a particular spill formula, use the helper column method or commit the formula with Ctrl+Shift+Enter.
