Pivot Table Help with a Calculated Field...

W

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:[TABLE="class: cms_table"]
<tbody>[TR]
[TD="class: cms_table_xl72"]Doctor
[/TD]
[TD="class: cms_table_xl72, width: 128, bgcolor: #8DB4E2"]OR
[/TD]
[TD="class: cms_table_xl73, width: 128, bgcolor: #8DB4E2"]SDate
[/TD]
[TD="class: cms_table_xl72, width: 128, bgcolor: #8DB4E2"]Edate
[/TD]
[TD="class: cms_table_xl72, width: 75, bgcolor: #8DB4E2"]Month
[/TD]
[TD="class: cms_table_xl72, width: 75, bgcolor: #8DB4E2"]Year
[/TD]
[TD="class: cms_table_xl72, width: 64, bgcolor: #8DB4E2"]Qtr
[/TD]
[TD="class: cms_table_xl72, width: 64, bgcolor: #8DB4E2"]Count
[/TD]
[TD="class: cms_table_xl72, width: 64, bgcolor: #8DB4E2"]DOW
[/TD]
[TD="class: cms_table_xl72, width: 64, bgcolor: #8DB4E2"]Week
[/TD]
[TD="class: cms_table_xl72, width: 64, bgcolor: #8DB4E2"]Shift1
[/TD]
[TD="class: cms_table_xl72, width: 64, bgcolor: #8DB4E2"]Shift 2
[/TD]
[TD="class: cms_table_xl72, width: 64, bgcolor: #8DB4E2"]Shift 3
[/TD]
[TD="class: cms_table_xl72, width: 64, bgcolor: #8DB4E2"]Avail1
[/TD]
[TD="class: cms_table_xl72, width: 64, bgcolor: #8DB4E2"]Avail2
[/TD]
[TD="class: cms_table_xl72, width: 64, bgcolor: #8DB4E2"]Avail3
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl67, bgcolor: transparent"]Kutt
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]01/02/2018
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]01/02/2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Jan
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Tue
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]81
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl67, bgcolor: transparent"]Slash
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]01/02/2018
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]01/02/2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Jan
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Tue
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]88
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl67, bgcolor: transparent"]Maim
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]3
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]01/02/2018
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]01/02/2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Jan
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Tue
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]106
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl67, bgcolor: transparent"]Slicer
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]02/04/2018
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]02/04/2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Feb
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Sun
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]6
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]75
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]21
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl67, bgcolor: transparent"]Slash
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]02/04/2018
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]02/04/2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Feb
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Sun
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]6
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]72
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]111
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl67, bgcolor: transparent"]Maim
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]02/05/2018
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]02/05/2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Feb
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Mon
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]6
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]103
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl67, bgcolor: transparent"]Kutt
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]3
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]02/05/2018
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]02/05/2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Feb
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Mon
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]6
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]94
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl67, bgcolor: transparent"]Kildare
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]3
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]02/05/2018
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]02/05/2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Feb
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Mon
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]6
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]79
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl67, bgcolor: transparent"]Slash
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]03/07/2018
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]03/07/2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Feb
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Mon
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]6
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]163
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl67, bgcolor: transparent"]Maim
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]03/07/2018
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]03/07/2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Feb
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Mon
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]6
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]58
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl67, bgcolor: transparent"]Slicer
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]03/07/2018
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]03/07/2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Mar
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Wed
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]10
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]14
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl67, bgcolor: transparent"]Slash
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]03/07/2018
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]03/07/2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Mar
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Wed
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]10
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]160
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]58
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl67, bgcolor: transparent"]Kutt
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]3
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]03/07/2018
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]03/07/2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Mar
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Wed
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]10
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]143
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]37
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]12
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl67, bgcolor: transparent"]Slash
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]3
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]03/07/2018
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]03/07/2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Mar
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Wed
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]10
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]113
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]8
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl67, bgcolor: transparent"]Maim
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]3
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]03/07/2018
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]03/07/2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Mar
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Wed
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]10
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]65
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]222
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
</tbody>[/TABLE]



Here is the Pivot table structure:

[TABLE="class: cms_table"]
<tbody>[TR]
[TD="class: cms_table_xl65"]Month
[/TD]
[TD="class: cms_table_xl65, width: 125"](All)
[/TD]
[TD="width: 92, bgcolor: transparent"][/TD]
[TD="width: 131, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65"]Week
[/TD]
[TD="class: cms_table_xl65"](All)
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65"]DOW
[/TD]
[TD="class: cms_table_xl65"](All)
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65"]Doctor
[/TD]
[TD="class: cms_table_xl65"](All)
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65"]SDate
[/TD]
[TD="class: cms_table_xl65"](Multiple Items)
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65"]Edate
[/TD]
[TD="class: cms_table_xl65"](All)
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65"]OR
[/TD]
[TD="class: cms_table_xl65"]Time Used
[/TD]
[TD="class: cms_table_xl65"]Sum of Avail1
[/TD]
[TD="class: cms_table_xl65"]Sum of Utilization%
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl65, bgcolor: transparent, align: right"]221
[/TD]
[TD="class: cms_table_xl65, bgcolor: transparent, align: right"]1020
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent, align: right"]22%
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66, bgcolor: transparent"]2
[/TD]
[TD="class: cms_table_xl65, bgcolor: transparent, align: right"]160
[/TD]
[TD="class: cms_table_xl65, bgcolor: transparent, align: right"]1020
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent, align: right"]16%
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66, bgcolor: transparent"]3
[/TD]
[TD="class: cms_table_xl65, bgcolor: transparent, align: right"]321
[/TD]
[TD="class: cms_table_xl65, bgcolor: transparent, align: right"]1530
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent, align: right"]21%
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl67"]Grand Total
[/TD]
[TD="class: cms_table_xl65, align: right"]702
[/TD]
[TD="class: cms_table_xl65, align: right"]3570
[/TD]
[TD="class: cms_table_xl68, align: right"]20%
[/TD]
[/TR]
</tbody>[/TABLE]


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
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
You must log in or register to reply here.

Similar threads

K
  • Question Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
600
awoohaw
awoohaw
M
  • Question Question
suggest excel formula to calculate cumulative interest on no of days etc ......
Replies
1
Views
648
Peter_SSs
Peter_SSs
K
  • Question Question
Dynamic Address for Use in Function Always returns ERROR
Replies
3
Views
532
Eric W
Eric W
R
  • Question Question
Pivot Table Slicer for Rows Field
Replies
0
Views
348
radonwilson
R
E
  • Question Question
FIFO Macro giving different results each time it runs
Replies
7
Views
1K
Lee Wei Sin
L

Forum statistics

Threads
1,223,898
Messages
6,175,272
Members
452,628
Latest member
dd2

Share this page

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
Back
Top