Wolfster63
New Member
- Joined
- May 2, 2018
- Messages
- 24
I have a spreadsheet that calculates the time a surgical case takes on a particular shift (Thanks [MENTION]shg[/MENTION]!). 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 a basic Pivot Table Set to the date of March 7:
<tbody>
</tbody>
My problem is that only 510 minutes are available 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? I thought a more complex calculated field would help, but I don't even know where to begin...
Thanks for any assistance/advice offered.
Will
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 | Date | Month | Year | Qtr | Count | DOW | Week | Shift1 | Shift 2 | Shift 3 | Avail1 | Avail2 | Avail3 |
Kutt | 1 | 01/02/2018 | Jan | 2018 | 1 | 1 | Tue | 1 | 81 | 0 | 0 | 510 | 240 | 300 |
Slash | 2 | 01/02/2018 | Jan | 2018 | 1 | 1 | Tue | 1 | 88 | 0 | 0 | 510 | 240 | 300 |
Maim | 3 | 01/02/2018 | Jan | 2018 | 1 | 1 | Tue | 1 | 106 | 0 | 0 | 510 | 240 | 300 |
Slicer | 1 | 02/04/2018 | Feb | 2018 | 1 | 1 | Sun | 6 | 75 | 21 | 0 | 510 | 240 | 300 |
Slash | 2 | 02/04/2018 | Feb | 2018 | 1 | 1 | Sun | 6 | 72 | 111 | 0 | 510 | 240 | 300 |
Maim | 2 | 02/05/2018 | Feb | 2018 | 1 | 1 | Mon | 6 | 103 | 0 | 0 | 510 | 240 | 300 |
Kutt | 3 | 02/05/2018 | Feb | 2018 | 1 | 1 | Mon | 6 | 94 | 0 | 0 | 510 | 240 | 300 |
Kildare | 3 | 02/05/2018 | Feb | 2018 | 1 | 1 | Mon | 6 | 79 | 0 | 0 | 510 | 240 | 300 |
Slash | 1 | 03/07/2018 | Feb | 2018 | 1 | 1 | Mon | 6 | 163 | 0 | 0 | 510 | 240 | 300 |
Maim | 1 | 03/07/2018 | Feb | 2018 | 1 | 1 | Mon | 6 | 58 | 0 | 0 | 510 | 240 | 300 |
Slicer | 2 | 03/07/2018 | Mar | 2018 | 1 | 1 | Wed | 10 | 0 | 14 | 0 | 510 | 240 | 300 |
Slash | 2 | 03/07/2018 | Mar | 2018 | 1 | 1 | Wed | 10 | 160 | 0 | 58 | 510 | 240 | 300 |
Kutt | 3 | 03/07/2018 | Mar | 2018 | 1 | 1 | Wed | 10 | 143 | 37 | 12 | 510 | 240 | 300 |
Slash | 3 | 03/07/2018 | Mar | 2018 | 1 | 1 | Wed | 10 | 113 | 8 | 0 | 510 | 240 | 300 |
Maim | 3 | 03/07/2018 | Mar | 2018 | 1 | 1 | Wed | 10 | 65 | 0 | 222 | 510 | 240 | 300 |
<tbody>
</tbody>
Here is a basic Pivot Table Set to the date of March 7:
Month | (All) | ||
Week | (All) | ||
DOW | Mon | ||
Date | (Multiple Items) | ||
Doctor | (All) | ||
Row Labels | Sum of Shift1 | Sum of Avail1 | Sum of Utilization% |
1 | 221 | 1020 | 22% |
Grand Total | 221 | 1020 | 22% |
<tbody>
</tbody>
My problem is that only 510 minutes are available 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? I thought a more complex calculated field would help, but I don't even know where to begin...
Thanks for any assistance/advice offered.
Will