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

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

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.
 

Forum statistics

Threads
1,148,160
Messages
5,745,122
Members
423,927
Latest member
Pra56

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