WPS Spreadsheet: Calculate Average Arrival Times by Hour in a PivotTable
Question details
The user needs to group arrival times by hour in a PivotTable, count arrivals, calculate the average arrival time, and visualize the results in a bar chart.
- Product
- WPS Spreadsheet PivotTable
- Device & OS
- not provided
- Scenario
- An arrival-time data column must be summarized by hour for an operational or time-based analysis.
- Observed behavior
- The desired hourly grouping, count, average-time calculation, and bar-chart summary are not yet configured.
Check that the Arrival Time column contains actual spreadsheet time values, not text, and that the source range has a clear header. Remove blank or invalid time entries before creating the PivotTable.
Group valid arrival times by hour and calculate the average in a WPS PivotTable
Use this method when the source arrival times are recognized as time values and you want hourly counts and averages in one summary.
A PivotTable can group time values into hourly buckets and summarize the same field with different calculations. Add the arrival-time field to Rows for grouping, then add it to Values more than once so one instance can count arrivals and another can calculate the average.
Select the arrival-time data range, open the Insert tab, choose PivotTable, select the source range and destination, then click OK.
In the PivotTable Fields pane, drag Arrival Time to the Rows area. Right-click a displayed time value in the PivotTable and choose Group.
In the Grouping dialog, select Hours and click OK. If the Group command is unavailable, verify that every source value is a valid time and that there are no blanks or text entries.
Drag Arrival Time to the Values area twice. Open the first value field's Value Field Settings and select Count; open the second and select Average. Format the average value as a time using the Home tab's Number Format controls.
Select the hourly PivotTable results, open the Insert tab, choose a Bar or Column chart, and select a chart style that displays the hourly count and average clearly.
Use an Hour helper column when time grouping is unavailable
Use this fallback when WPS Spreadsheet cannot group the original Arrival Time field because some values are stored as text or contain inconsistent formats.
Build an hourly arrival-time PivotTable and chart in WPS Spreadsheet
WPS Spreadsheet can group valid arrival times by hour, show separate Count and Average value fields, and turn the summarized results into a bar chart. It also supports familiar Excel-style PivotTable workflows and XLSX files.
- 1. Open the source data: Launch WPS Spreadsheet and open the workbook containing the Arrival Time column.
- 2. Insert a PivotTable: Select the source range, choose Insert > PivotTable, confirm the range, and click OK.
- 3. Group by hour: Drag Arrival Time to Rows, right-click a time value, choose Group, select Hours, and click OK.
- 4. Configure the summaries: Add Arrival Time to Values twice, then use Value Field Settings to set one field to Count and the other to Average.
- 5. Add a chart: Select the PivotTable results and choose Insert > Bar Chart or Column Chart to visualize the hourly data.
Frequently Asked Questions
Why can’t WPS Spreadsheet group my arrival times by hour?
The source values are usually text, blank, or inconsistent rather than valid spreadsheet times. Convert the entries to time values, remove invalid rows, refresh or recreate the PivotTable, and try right-clicking a time value followed by Group > Hours again.
How do I show both the number of arrivals and the average arrival time?
Drag Arrival Time into the Values area twice. Open the first field's Value Field Settings and choose Count, then set the second field to Average. Format the average result as a time instead of a decimal.
Can I calculate an average arrival time with a helper column?
Yes. Add a helper column containing the hour value, such as =HOUR(A2), and use that column in the PivotTable Rows area. Keep the original Arrival Time field in Values and set its summary to Average when the source values are valid times.
How do I make a bar chart from the hourly PivotTable?
Select the hourly row labels and the required count or average columns, open Insert, choose Bar Chart or Column Chart, and then add a descriptive chart title and axis labels.
