Wolfster63
New Member
- Joined
- May 2, 2018
- Messages
- 24
Thought I'd repost this as the formatting did not come through correctly in the original post...apologies.
One thing I did try was to use MAX and MIN on the date fields to determine number of shifts being looked at and therefore get an accurate value:
=DAYS360(MIN(C3:C17),MAX(C3:C17))
It works on the spreadsheet, but not on the calculated field in the Pivot Table. I keep getting a Divided by 0 error.
+++++++++++
I have a spreadsheet that calculates the time a surgical case takes on a particular shift (Thanks @shg;!). I am attempting to create a Pivot table that could analyze how much of the available room time is taken in a particular room or group of rooms during a given time period
I inserted a calculated field that would render a percent of the room time used. Each room has a finite number of minutes that can be used on a particular day during a particular shift.
Here is a sample spreadsheet to demonstrate the spreadsheet structure:
<tbody>
</tbody>
Here is the Pivot table structure:
<tbody>
</tbody>
My problem is that only 510 minutes are available for OR 3 on this day. So the precentage show be higher (43%) but the available time is added up per entry. I want the table to show only a total available for the day (510 minutes), but also if the time frame is a whole week of dates the total minutes would be (7x510 or 3570 minutes for each OR room selected). I need the denominator to correspond to the days available in the choice of filters and not cumulative.
Further, it would be nice to be able to select the shift as well as one of the parameters.
Any ideas on how to go about this?
Thanks for any assistance/advice offered.
Will
One thing I did try was to use MAX and MIN on the date fields to determine number of shifts being looked at and therefore get an accurate value:
=DAYS360(MIN(C3:C17),MAX(C3:C17))
It works on the spreadsheet, but not on the calculated field in the Pivot Table. I keep getting a Divided by 0 error.
+++++++++++
I have a spreadsheet that calculates the time a surgical case takes on a particular shift (Thanks @shg;!). I am attempting to create a Pivot table that could analyze how much of the available room time is taken in a particular room or group of rooms during a given time period
I inserted a calculated field that would render a percent of the room time used. Each room has a finite number of minutes that can be used on a particular day during a particular shift.
Here is a sample spreadsheet to demonstrate the spreadsheet structure:
Doctor | OR | SDate | Edate | Month | Year | Qtr | Count | DOW | Week | Shift1 | Shift 2 | Shift 3 | Avail1 | Avail2 | Avail3 |
Kutt | 1 | 01/02/2018 | 01/02/2018 | Jan | 2018 | 1 | 1 | Tue | 1 | 81 | 0 | 0 | 510 | 240 | 300 |
Slash | 2 | 01/02/2018 | 01/02/2018 | Jan | 2018 | 1 | 1 | Tue | 1 | 88 | 0 | 0 | 510 | 240 | 300 |
Maim | 3 | 01/02/2018 | 01/02/2018 | Jan | 2018 | 1 | 1 | Tue | 1 | 106 | 0 | 0 | 510 | 240 | 300 |
Slicer | 1 | 02/04/2018 | 02/04/2018 | Feb | 2018 | 1 | 1 | Sun | 6 | 75 | 21 | 0 | 510 | 240 | 300 |
Slash | 2 | 02/04/2018 | 02/04/2018 | Feb | 2018 | 1 | 1 | Sun | 6 | 72 | 111 | 0 | 510 | 240 | 300 |
Maim | 2 | 02/05/2018 | 02/05/2018 | Feb | 2018 | 1 | 1 | Mon | 6 | 103 | 0 | 0 | 510 | 240 | 300 |
Kutt | 3 | 02/05/2018 | 02/05/2018 | Feb | 2018 | 1 | 1 | Mon | 6 | 94 | 0 | 0 | 510 | 240 | 300 |
Kildare | 3 | 02/05/2018 | 02/05/2018 | Feb | 2018 | 1 | 1 | Mon | 6 | 79 | 0 | 0 | 510 | 240 | 300 |
Slash | 1 | 03/07/2018 | 03/07/2018 | Feb | 2018 | 1 | 1 | Mon | 6 | 163 | 0 | 0 | 510 | 240 | 300 |
Maim | 1 | 03/07/2018 | 03/07/2018 | Feb | 2018 | 1 | 1 | Mon | 6 | 58 | 0 | 0 | 510 | 240 | 300 |
Slicer | 2 | 03/07/2018 | 03/07/2018 | Mar | 2018 | 1 | 1 | Wed | 10 | 0 | 14 | 0 | 510 | 240 | 300 |
Slash | 2 | 03/07/2018 | 03/07/2018 | Mar | 2018 | 1 | 1 | Wed | 10 | 160 | 0 | 58 | 510 | 240 | 300 |
Kutt | 3 | 03/07/2018 | 03/07/2018 | Mar | 2018 | 1 | 1 | Wed | 10 | 143 | 37 | 12 | 510 | 240 | 300 |
Slash | 3 | 03/07/2018 | 03/07/2018 | Mar | 2018 | 1 | 1 | Wed | 10 | 113 | 8 | 0 | 510 | 240 | 300 |
Maim | 3 | 03/07/2018 | 03/07/2018 | Mar | 2018 | 1 | 1 | Wed | 10 | 65 | 0 | 222 | 510 | 240 | 300 |
<tbody>
</tbody>
Here is the Pivot table structure:
Month | (All) | ||
Week | (All) | ||
DOW | (All) | ||
Doctor | (All) | ||
SDate | (Multiple Items) | ||
Edate | (All) | ||
OR | Time Used | Sum of Avail1 | Sum of Utilization% |
1 | 221 | 1020 | 22% |
2 | 160 | 1020 | 16% |
3 | 321 | 1530 | 21% |
Grand Total | 702 | 3570 | 20% |
<tbody>
</tbody>
My problem is that only 510 minutes are available for OR 3 on this day. So the precentage show be higher (43%) but the available time is added up per entry. I want the table to show only a total available for the day (510 minutes), but also if the time frame is a whole week of dates the total minutes would be (7x510 or 3570 minutes for each OR room selected). I need the denominator to correspond to the days available in the choice of filters and not cumulative.
Further, it would be nice to be able to select the shift as well as one of the parameters.
Any ideas on how to go about this?
Thanks for any assistance/advice offered.
Will