Summarize Excel Time by Hour
Kevin writes: I use Excel to monitor my glucose levels several times a day. After a month of readings, I want to see an Excel table showing the average glucose level in each hour of the day.
Update: If you are using Excel 97 or higher, the new Excel grouping function will solve this problem with one less step.
The sample file that Kevin sent had a date and time in column A and the glucose level in column B. I suggested the following:
Add a new column C. The heading in C1 is Hour. The formula in C2 is =HOUR(A2)/24. Copy this formula down to all of your data points.
Next, create a pivot table to calculate the average by hour. To do this, highlight a single cell in your data. From the Data Menu, select Pivot Table. For XL95/97, click Next until you get to the layout dialog. In Excel 2000, click the Layout button in step 3.
- Drag the Hour box over to the Row section of the layout.
- Drag the Reading box to the Data section of the layout.
- Double click Sum of Reading and change the summarize option to Average.
- Click Finish
Presto! You will quickly have a table showing your average glucose level at each hour of the day.
For more tips like this page, check out MrExcel's book:
