How to summarize groups when some or all values are blank
Calculate each team's minimum, maximum and average processing time without treating missing durations as zero. Check what happens when an entire group is blank.
Keep missing durations blank rather than entering zero. In JOIN SHEET, group by Team and add Minimum, Maximum and Average in one Summarize step. These calculations use the recorded numbers. If a team has records but all its durations are blank, it stays in the result with blank summary values.
Include both partly blank and entirely blank groups
In these fictional records, Team A takes 10, 20 and 30 minutes. Team B takes 8 and 12 minutes and has one missing duration. Team C has one record with no duration entered. All recorded durations use minutes.
| Team | Processing time |
|---|---|
| Team A | 10 |
| Team A | 20 |
| Team A | 30 |
| Team B | 8 |
| Team B | 12 |
| Team B | Blank |
| Team C | Blank |
Check that Processing time is numeric. Put units in the header and leave numeric cells such as 10 without a unit suffix. If a cell contains the text "null" or "missing", clean it in the source before proceeding. The blanks in this example are empty cells.
Add three measures to one summary
- Load and select the sheet. Open Refine results, add Summarize, and select Team under Group by.
- Set the first summary method to Minimum for Processing time and name it Minimum time.
- Use Add summary within that step to add Maximum for the same field, named Maximum time.
- Add Average for Processing time, name it Average time, and choose Apply.
Keep all three calculations in the same step using Add summary. Each calculation will then use the original Processing time values, producing one result row per team.
Expected result
| Team | Minimum time | Maximum time | Average time |
|---|---|---|---|
| Team A | 10 | 30 | 20 |
| Team B | 8 | 12 | 10 |
| Team C | Blank | Blank | Blank |
Team B's average is (8 + 12) ÷ 2 = 10, using its two recorded durations. Team C has no numeric duration, so its minimum, maximum and average are all blank.
Why can a group's count be three but its average use only two values?
Count includes Team B's record with no duration, giving three records. Average uses only the two recorded numbers. Team C's blank measures mean that no duration was entered. If Team C had no source row at all, it would also be absent from the summary; prepare a separate list if a report must show every team or week.
For a weekly breakdown, add a Week column to the source and include it under Group by. This workflow uses the available minimum, maximum and average summaries; use a statistical tool if you also need measures such as median or standard deviation.
Try it with your own files
Start with JOIN SHEET ↗Use a desktop computer for Excel and CSV work.
Opens in a new tab without replacing your current work.
Example last verified:
Sources
Discussion of grouped minimum, maximum, averages and blank values — Reddit r/excel
The source asks about errors in GROUPBY results for blank weeks. This fictional example uses numeric values and actual empty cells, then calculates the summaries through the interface.
