# Pivot Table Average by day week and hour

#### karkas

##### Board Regular
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.

Source Data table example:

Example of data for Sundays from midnight until 1am.

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
Last bump in case someone is able to help.

Replies
1
Views
486
Replies
0
Views
127
Replies
1
Views
207
Replies
3
Views
140
Replies
7
Views
89

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.

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.

### Which adblocker are you using?

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

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