MrExcel.com - Your One Stop for Excel Tips & Solutions

Average Using Pivot Table

October 30, 2001 - by

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.

Pivot Table Layout
  • 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.