MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Average Using Pivot Table


October 30, 2001 - by Bill Jelen

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.


Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.