Pivot Table Average by day week and hour

karkas

Board Regular
Joined
Oct 17, 2007
Messages
170
Office Version
  1. 2013
Platform
  1. Windows
I have a large table of data (30k rows), where I am trying to display sums and averages per day of the week by each hour in the day. I can get the table to display the sums, but I can't figure out how to make it show the average over that time period.

These are medication dispenses from a hospital pharmacy. The orders are placed at a specific date/time (Orig order dt/tm), dispensed from the pharmacy at another time (Disp dt/tm) and scheduled to start at another time (Start dt/tm). I am evaluating each of these date/time parameters, but looking at hard numbers is of limited value. I need to see the averages by each day of the week in one hour increments. I should be able to figure out how to make the other dates work once I get this first one based on Start date/time figured out.

You can see in my first image, I don't have any trouble getting the pivot table to show me the total dose sums per time slot, but I can't figure out how to get it to display a useful average.
pivotsum.png



pivotavg.png


Source Data table example:
sourcedata.png



Example of data for Sundays from midnight until 1am.
SundayMidnightData.png


Thanks in advance for any help.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

karkas

Board Regular
Joined
Oct 17, 2007
Messages
170
Office Version
  1. 2013
Platform
  1. Windows
Last bump in case someone is able to help.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,770
Messages
5,766,379
Members
425,350
Latest member
procha

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top