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

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
You must log in or register to reply here.

Similar threads

Sandeep Warrier
  • Question Question
Calculate No. of Working Days based on Shift Pattern
Replies
2
Views
151
Tetra201
Tetra201
K
  • Question Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
607
awoohaw
awoohaw
M
  • Question Question
suggest excel formula to calculate cumulative interest on no of days etc ......
Replies
1
Views
664
Peter_SSs
Peter_SSs
K
  • Question Question
Dynamic Address for Use in Function Always returns ERROR
Replies
3
Views
549
Eric W
Eric W
R
  • Question Question
Pivot Table Slicer for Rows Field
Replies
0
Views
356
radonwilson
R

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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