Pivot Table Help

[Wolfster63]

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:
[TABLE="width: 496"]
<tbody>[TR]
[TD="class: xl64, width: 51, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=8DB4E2]#8DB4E2[/URL] , align: center"]Doctor
[/TD]
[TD="class: xl64, width: 25, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=8DB4E2]#8DB4E2[/URL] , align: center"]OR
[/TD]
[TD="class: xl64, width: 75, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=8DB4E2]#8DB4E2[/URL] , align: center"]Date
[/TD]
[TD="class: xl64, width: 49, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=8DB4E2]#8DB4E2[/URL] , align: center"]Month
[/TD]
[TD="class: xl64, width: 35, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=8DB4E2]#8DB4E2[/URL] , align: center"]Year
[/TD]
[TD="class: xl64, width: 27, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=8DB4E2]#8DB4E2[/URL] , align: center"]Qtr
[/TD]
[TD="class: xl64, width: 44, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=8DB4E2]#8DB4E2[/URL] , align: center"]Count
[/TD]
[TD="class: xl64, width: 40, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=8DB4E2]#8DB4E2[/URL] , align: center"]DOW
[/TD]
[TD="class: xl64, width: 44, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=8DB4E2]#8DB4E2[/URL] , align: center"]Week
[/TD]
[TD="class: xl64, width: 43, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=8DB4E2]#8DB4E2[/URL] , align: center"]Shift1
[/TD]
[TD="class: xl64, width: 46, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=8DB4E2]#8DB4E2[/URL] , align: center"]Shift 2
[/TD]
[TD="class: xl64, width: 46, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=8DB4E2]#8DB4E2[/URL] , align: center"]Shift 3
[/TD]
[TD="class: xl64, width: 45, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=8DB4E2]#8DB4E2[/URL] , align: center"]Avail1
[/TD]
[TD="class: xl64, width: 45, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=8DB4E2]#8DB4E2[/URL] , align: center"]Avail2
[/TD]
[TD="class: xl64, width: 45, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=8DB4E2]#8DB4E2[/URL] , align: center"]Avail3
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: center"]Kutt
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]1
[/TD]
[TD="class: xl65, bgcolor: transparent, align: center"]01/02/2018
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]Jan
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]2018
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]Tue
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]1
[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]81
[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]0
[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]0
[/TD]
[TD="class: xl66, bgcolor: transparent, align: center"]510
[/TD]
[TD="class: xl66, bgcolor: transparent, align: center"]240
[/TD]
[TD="class: xl66, bgcolor: transparent, align: center"]300
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: center"]Slash
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]2
[/TD]
[TD="class: xl65, bgcolor: transparent, align: center"]01/02/2018
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]Jan
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]2018
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]Tue
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]1
[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]88
[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]0
[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]0
[/TD]
[TD="class: xl66, bgcolor: transparent, align: center"]510
[/TD]
[TD="class: xl66, bgcolor: transparent, align: center"]240
[/TD]
[TD="class: xl66, bgcolor: transparent, align: center"]300
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: center"]Maim
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]3
[/TD]
[TD="class: xl65, bgcolor: transparent, align: center"]01/02/2018
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]Jan
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]2018
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]Tue
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]1
[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]106
[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]0
[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]0
[/TD]
[TD="class: xl66, bgcolor: transparent, align: center"]510
[/TD]
[TD="class: xl66, bgcolor: transparent, align: center"]240
[/TD]
[TD="class: xl66, bgcolor: transparent, align: center"]300
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: center"]Slicer
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]1
[/TD]
[TD="class: xl65, bgcolor: transparent, align: center"]02/04/2018
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]Feb
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]2018
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]Sun
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]6
[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]75
[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]21
[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]0
[/TD]
[TD="class: xl66, bgcolor: transparent, align: center"]510
[/TD]
[TD="class: xl66, bgcolor: transparent, align: center"]240
[/TD]
[TD="class: xl66, bgcolor: transparent, align: center"]300
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: center"]Slash
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]2
[/TD]
[TD="class: xl65, bgcolor: transparent, align: center"]02/04/2018
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]Feb
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]2018
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]Sun
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]6
[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]72
[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]111
[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]0
[/TD]
[TD="class: xl66, bgcolor: transparent, align: center"]510
[/TD]
[TD="class: xl66, bgcolor: transparent, align: center"]240
[/TD]
[TD="class: xl66, bgcolor: transparent, align: center"]300
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: center"]Maim
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]2
[/TD]
[TD="class: xl65, bgcolor: transparent, align: center"]02/05/2018
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]Feb
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]2018
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]Mon
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]6
[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]103
[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]0
[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]0
[/TD]
[TD="class: xl66, bgcolor: transparent, align: center"]510
[/TD]
[TD="class: xl66, bgcolor: transparent, align: center"]240
[/TD]
[TD="class: xl66, bgcolor: transparent, align: center"]300
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: center"]Kutt
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]3
[/TD]
[TD="class: xl65, bgcolor: transparent, align: center"]02/05/2018
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]Feb
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]2018
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]Mon
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]6
[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]94
[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]0
[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]0
[/TD]
[TD="class: xl66, bgcolor: transparent, align: center"]510
[/TD]
[TD="class: xl66, bgcolor: transparent, align: center"]240
[/TD]
[TD="class: xl66, bgcolor: transparent, align: center"]300
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: center"]Kildare
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]3
[/TD]
[TD="class: xl65, bgcolor: transparent, align: center"]02/05/2018
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]Feb
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]2018
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]Mon
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]6
[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]79
[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]0
[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]0
[/TD]
[TD="class: xl66, bgcolor: transparent, align: center"]510
[/TD]
[TD="class: xl66, bgcolor: transparent, align: center"]240
[/TD]
[TD="class: xl66, bgcolor: transparent, align: center"]300
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: center"]Slash
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]1
[/TD]
[TD="class: xl65, bgcolor: transparent, align: center"]03/07/2018
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]Feb
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]2018
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]Mon
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]6
[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]163
[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]0
[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]0
[/TD]
[TD="class: xl66, bgcolor: transparent, align: center"]510
[/TD]
[TD="class: xl66, bgcolor: transparent, align: center"]240
[/TD]
[TD="class: xl66, bgcolor: transparent, align: center"]300
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: center"]Maim
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]1
[/TD]
[TD="class: xl65, bgcolor: transparent, align: center"]03/07/2018
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]Feb
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]2018
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]Mon
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]6
[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]58
[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]0
[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]0
[/TD]
[TD="class: xl66, bgcolor: transparent, align: center"]510
[/TD]
[TD="class: xl66, bgcolor: transparent, align: center"]240
[/TD]
[TD="class: xl66, bgcolor: transparent, align: center"]300
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: center"]Slicer
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]2
[/TD]
[TD="class: xl65, bgcolor: transparent, align: center"]03/07/2018
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]Mar
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]2018
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]Wed
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]10
[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]0
[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]14
[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]0
[/TD]
[TD="class: xl66, bgcolor: transparent, align: center"]510
[/TD]
[TD="class: xl66, bgcolor: transparent, align: center"]240
[/TD]
[TD="class: xl66, bgcolor: transparent, align: center"]300
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: center"]Slash
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]2
[/TD]
[TD="class: xl65, bgcolor: transparent, align: center"]03/07/2018
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]Mar
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]2018
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]Wed
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]10
[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]160
[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]0
[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]58
[/TD]
[TD="class: xl66, bgcolor: transparent, align: center"]510
[/TD]
[TD="class: xl66, bgcolor: transparent, align: center"]240
[/TD]
[TD="class: xl66, bgcolor: transparent, align: center"]300
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: center"]Kutt
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]3
[/TD]
[TD="class: xl65, bgcolor: transparent, align: center"]03/07/2018
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]Mar
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]2018
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]Wed
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]10
[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]143
[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]37
[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]12
[/TD]
[TD="class: xl66, bgcolor: transparent, align: center"]510
[/TD]
[TD="class: xl66, bgcolor: transparent, align: center"]240
[/TD]
[TD="class: xl66, bgcolor: transparent, align: center"]300
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: center"]Slash
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]3
[/TD]
[TD="class: xl65, bgcolor: transparent, align: center"]03/07/2018
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]Mar
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]2018
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]Wed
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]10
[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]113
[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]8
[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]0
[/TD]
[TD="class: xl66, bgcolor: transparent, align: center"]510
[/TD]
[TD="class: xl66, bgcolor: transparent, align: center"]240
[/TD]
[TD="class: xl66, bgcolor: transparent, align: center"]300
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: center"]Maim
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]3
[/TD]
[TD="class: xl65, bgcolor: transparent, align: center"]03/07/2018
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]Mar
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]2018
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]Wed
[/TD]
[TD="class: xl63, bgcolor: transparent, align: center"]10
[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]65
[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]0
[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]222
[/TD]
[TD="class: xl66, bgcolor: transparent, align: center"]510
[/TD]
[TD="class: xl66, bgcolor: transparent, align: center"]240
[/TD]
[TD="class: xl66, bgcolor: transparent, align: center"]300
[/TD]
[/TR]
</tbody>[/TABLE]

Here is a basic Pivot Table Set to the date of March 7:

[TABLE="width: 330"]
<tbody>[TR]
[TD="width: 92"]Month
[/TD]
[TD="width: 125"](All)
[/TD]
[TD="width: 92, bgcolor: transparent"][/TD]
[TD="width: 131, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD]Week
[/TD]
[TD](All)
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD]DOW
[/TD]
[TD]Mon
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD]Date
[/TD]
[TD](Multiple Items)
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD]Doctor
[/TD]
[TD](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]Row Labels
[/TD]
[TD]Sum of Shift1
[/TD]
[TD]Sum of Avail1
[/TD]
[TD]Sum of Utilization%
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]1
[/TD]
[TD="bgcolor: transparent, align: right"]221
[/TD]
[TD="bgcolor: transparent, align: right"]1020
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]22%
[/TD]
[/TR]
[TR]
[TD="class: xl65"]Grand Total
[/TD]
[TD="align: right"]221
[/TD]
[TD="align: right"]1020
[/TD]
[TD="class: xl66, align: right"]22%
[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[Wolfster63]

Thought I'd repost this as the formatting did not come through correctly...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. 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="width: 976"]
<tbody>[TR]
[TD="class: xl72, width: 128, bgcolor: #8DB4E2"]Doctor
[/TD]
[TD="class: xl72, width: 128, bgcolor: #8DB4E2"]OR
[/TD]
[TD="class: xl73, width: 128, bgcolor: #8DB4E2"]SDate
[/TD]
[TD="class: xl72, width: 128, bgcolor: #8DB4E2"]Edate
[/TD]
[TD="class: xl72, width: 75, bgcolor: #8DB4E2"]Month
[/TD]
[TD="class: xl72, width: 75, bgcolor: #8DB4E2"]Year
[/TD]
[TD="class: xl72, width: 64, bgcolor: #8DB4E2"]Qtr
[/TD]
[TD="class: xl72, width: 64, bgcolor: #8DB4E2"]Count
[/TD]
[TD="class: xl72, width: 64, bgcolor: #8DB4E2"]DOW
[/TD]
[TD="class: xl72, width: 64, bgcolor: #8DB4E2"]Week
[/TD]
[TD="class: xl72, width: 64, bgcolor: #8DB4E2"]Shift1
[/TD]
[TD="class: xl72, width: 64, bgcolor: #8DB4E2"]Shift 2
[/TD]
[TD="class: xl72, width: 64, bgcolor: #8DB4E2"]Shift 3
[/TD]
[TD="class: xl72, width: 64, bgcolor: #8DB4E2"]Avail1
[/TD]
[TD="class: xl72, width: 64, bgcolor: #8DB4E2"]Avail2
[/TD]
[TD="class: xl72, width: 64, bgcolor: #8DB4E2"]Avail3
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Kutt
[/TD]
[TD="class: xl68, bgcolor: transparent"]1
[/TD]
[TD="class: xl69, bgcolor: transparent"]01/02/2018
[/TD]
[TD="class: xl69, bgcolor: transparent"]01/02/2018
[/TD]
[TD="class: xl68, bgcolor: transparent"]Jan
[/TD]
[TD="class: xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: xl68, bgcolor: transparent"]1
[/TD]
[TD="class: xl68, bgcolor: transparent"]1
[/TD]
[TD="class: xl68, bgcolor: transparent"]Tue
[/TD]
[TD="class: xl68, bgcolor: transparent"]1
[/TD]
[TD="class: xl70, bgcolor: transparent"]81
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Slash
[/TD]
[TD="class: xl68, bgcolor: transparent"]2
[/TD]
[TD="class: xl69, bgcolor: transparent"]01/02/2018
[/TD]
[TD="class: xl69, bgcolor: transparent"]01/02/2018
[/TD]
[TD="class: xl68, bgcolor: transparent"]Jan
[/TD]
[TD="class: xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: xl68, bgcolor: transparent"]1
[/TD]
[TD="class: xl68, bgcolor: transparent"]1
[/TD]
[TD="class: xl68, bgcolor: transparent"]Tue
[/TD]
[TD="class: xl68, bgcolor: transparent"]1
[/TD]
[TD="class: xl70, bgcolor: transparent"]88
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Maim
[/TD]
[TD="class: xl68, bgcolor: transparent"]3
[/TD]
[TD="class: xl69, bgcolor: transparent"]01/02/2018
[/TD]
[TD="class: xl69, bgcolor: transparent"]01/02/2018
[/TD]
[TD="class: xl68, bgcolor: transparent"]Jan
[/TD]
[TD="class: xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: xl68, bgcolor: transparent"]1
[/TD]
[TD="class: xl68, bgcolor: transparent"]1
[/TD]
[TD="class: xl68, bgcolor: transparent"]Tue
[/TD]
[TD="class: xl68, bgcolor: transparent"]1
[/TD]
[TD="class: xl70, bgcolor: transparent"]106
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Slicer
[/TD]
[TD="class: xl68, bgcolor: transparent"]1
[/TD]
[TD="class: xl69, bgcolor: transparent"]02/04/2018
[/TD]
[TD="class: xl69, bgcolor: transparent"]02/04/2018
[/TD]
[TD="class: xl68, bgcolor: transparent"]Feb
[/TD]
[TD="class: xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: xl68, bgcolor: transparent"]1
[/TD]
[TD="class: xl68, bgcolor: transparent"]1
[/TD]
[TD="class: xl68, bgcolor: transparent"]Sun
[/TD]
[TD="class: xl68, bgcolor: transparent"]6
[/TD]
[TD="class: xl70, bgcolor: transparent"]75
[/TD]
[TD="class: xl70, bgcolor: transparent"]21
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Slash
[/TD]
[TD="class: xl68, bgcolor: transparent"]2
[/TD]
[TD="class: xl69, bgcolor: transparent"]02/04/2018
[/TD]
[TD="class: xl69, bgcolor: transparent"]02/04/2018
[/TD]
[TD="class: xl68, bgcolor: transparent"]Feb
[/TD]
[TD="class: xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: xl68, bgcolor: transparent"]1
[/TD]
[TD="class: xl68, bgcolor: transparent"]1
[/TD]
[TD="class: xl68, bgcolor: transparent"]Sun
[/TD]
[TD="class: xl68, bgcolor: transparent"]6
[/TD]
[TD="class: xl70, bgcolor: transparent"]72
[/TD]
[TD="class: xl70, bgcolor: transparent"]111
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Maim
[/TD]
[TD="class: xl68, bgcolor: transparent"]2
[/TD]
[TD="class: xl69, bgcolor: transparent"]02/05/2018
[/TD]
[TD="class: xl69, bgcolor: transparent"]02/05/2018
[/TD]
[TD="class: xl68, bgcolor: transparent"]Feb
[/TD]
[TD="class: xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: xl68, bgcolor: transparent"]1
[/TD]
[TD="class: xl68, bgcolor: transparent"]1
[/TD]
[TD="class: xl68, bgcolor: transparent"]Mon
[/TD]
[TD="class: xl68, bgcolor: transparent"]6
[/TD]
[TD="class: xl70, bgcolor: transparent"]103
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Kutt
[/TD]
[TD="class: xl68, bgcolor: transparent"]3
[/TD]
[TD="class: xl69, bgcolor: transparent"]02/05/2018
[/TD]
[TD="class: xl69, bgcolor: transparent"]02/05/2018
[/TD]
[TD="class: xl68, bgcolor: transparent"]Feb
[/TD]
[TD="class: xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: xl68, bgcolor: transparent"]1
[/TD]
[TD="class: xl68, bgcolor: transparent"]1
[/TD]
[TD="class: xl68, bgcolor: transparent"]Mon
[/TD]
[TD="class: xl68, bgcolor: transparent"]6
[/TD]
[TD="class: xl70, bgcolor: transparent"]94
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Kildare
[/TD]
[TD="class: xl68, bgcolor: transparent"]3
[/TD]
[TD="class: xl69, bgcolor: transparent"]02/05/2018
[/TD]
[TD="class: xl69, bgcolor: transparent"]02/05/2018
[/TD]
[TD="class: xl68, bgcolor: transparent"]Feb
[/TD]
[TD="class: xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: xl68, bgcolor: transparent"]1
[/TD]
[TD="class: xl68, bgcolor: transparent"]1
[/TD]
[TD="class: xl68, bgcolor: transparent"]Mon
[/TD]
[TD="class: xl68, bgcolor: transparent"]6
[/TD]
[TD="class: xl70, bgcolor: transparent"]79
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Slash
[/TD]
[TD="class: xl68, bgcolor: transparent"]1
[/TD]
[TD="class: xl69, bgcolor: transparent"]03/07/2018
[/TD]
[TD="class: xl69, bgcolor: transparent"]03/07/2018
[/TD]
[TD="class: xl68, bgcolor: transparent"]Feb
[/TD]
[TD="class: xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: xl68, bgcolor: transparent"]1
[/TD]
[TD="class: xl68, bgcolor: transparent"]1
[/TD]
[TD="class: xl68, bgcolor: transparent"]Mon
[/TD]
[TD="class: xl68, bgcolor: transparent"]6
[/TD]
[TD="class: xl70, bgcolor: transparent"]163
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Maim
[/TD]
[TD="class: xl68, bgcolor: transparent"]1
[/TD]
[TD="class: xl69, bgcolor: transparent"]03/07/2018
[/TD]
[TD="class: xl69, bgcolor: transparent"]03/07/2018
[/TD]
[TD="class: xl68, bgcolor: transparent"]Feb
[/TD]
[TD="class: xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: xl68, bgcolor: transparent"]1
[/TD]
[TD="class: xl68, bgcolor: transparent"]1
[/TD]
[TD="class: xl68, bgcolor: transparent"]Mon
[/TD]
[TD="class: xl68, bgcolor: transparent"]6
[/TD]
[TD="class: xl70, bgcolor: transparent"]58
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Slicer
[/TD]
[TD="class: xl68, bgcolor: transparent"]2
[/TD]
[TD="class: xl69, bgcolor: transparent"]03/07/2018
[/TD]
[TD="class: xl69, bgcolor: transparent"]03/07/2018
[/TD]
[TD="class: xl68, bgcolor: transparent"]Mar
[/TD]
[TD="class: xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: xl68, bgcolor: transparent"]1
[/TD]
[TD="class: xl68, bgcolor: transparent"]1
[/TD]
[TD="class: xl68, bgcolor: transparent"]Wed
[/TD]
[TD="class: xl68, bgcolor: transparent"]10
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]14
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Slash
[/TD]
[TD="class: xl68, bgcolor: transparent"]2
[/TD]
[TD="class: xl69, bgcolor: transparent"]03/07/2018
[/TD]
[TD="class: xl69, bgcolor: transparent"]03/07/2018
[/TD]
[TD="class: xl68, bgcolor: transparent"]Mar
[/TD]
[TD="class: xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: xl68, bgcolor: transparent"]1
[/TD]
[TD="class: xl68, bgcolor: transparent"]1
[/TD]
[TD="class: xl68, bgcolor: transparent"]Wed
[/TD]
[TD="class: xl68, bgcolor: transparent"]10
[/TD]
[TD="class: xl70, bgcolor: transparent"]160
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]58
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Kutt
[/TD]
[TD="class: xl68, bgcolor: transparent"]3
[/TD]
[TD="class: xl69, bgcolor: transparent"]03/07/2018
[/TD]
[TD="class: xl69, bgcolor: transparent"]03/07/2018
[/TD]
[TD="class: xl68, bgcolor: transparent"]Mar
[/TD]
[TD="class: xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: xl68, bgcolor: transparent"]1
[/TD]
[TD="class: xl68, bgcolor: transparent"]1
[/TD]
[TD="class: xl68, bgcolor: transparent"]Wed
[/TD]
[TD="class: xl68, bgcolor: transparent"]10
[/TD]
[TD="class: xl70, bgcolor: transparent"]143
[/TD]
[TD="class: xl70, bgcolor: transparent"]37
[/TD]
[TD="class: xl70, bgcolor: transparent"]12
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Slash
[/TD]
[TD="class: xl68, bgcolor: transparent"]3
[/TD]
[TD="class: xl69, bgcolor: transparent"]03/07/2018
[/TD]
[TD="class: xl69, bgcolor: transparent"]03/07/2018
[/TD]
[TD="class: xl68, bgcolor: transparent"]Mar
[/TD]
[TD="class: xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: xl68, bgcolor: transparent"]1
[/TD]
[TD="class: xl68, bgcolor: transparent"]1
[/TD]
[TD="class: xl68, bgcolor: transparent"]Wed
[/TD]
[TD="class: xl68, bgcolor: transparent"]10
[/TD]
[TD="class: xl70, bgcolor: transparent"]113
[/TD]
[TD="class: xl70, bgcolor: transparent"]8
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Maim
[/TD]
[TD="class: xl68, bgcolor: transparent"]3
[/TD]
[TD="class: xl69, bgcolor: transparent"]03/07/2018
[/TD]
[TD="class: xl69, bgcolor: transparent"]03/07/2018
[/TD]
[TD="class: xl68, bgcolor: transparent"]Mar
[/TD]
[TD="class: xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: xl68, bgcolor: transparent"]1
[/TD]
[TD="class: xl68, bgcolor: transparent"]1
[/TD]
[TD="class: xl68, bgcolor: transparent"]Wed
[/TD]
[TD="class: xl68, bgcolor: transparent"]10
[/TD]
[TD="class: xl70, bgcolor: transparent"]65
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]222
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
</tbody>[/TABLE]


Here is the Pivot table structure:

[TABLE="width: 320"]
<tbody>[TR]
[TD="class: xl65, width: 79"]Month
[/TD]
[TD="class: xl65, width: 125"](All)
[/TD]
[TD="width: 92, bgcolor: transparent"][/TD]
[TD="width: 131, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65"]Week
[/TD]
[TD="class: xl65"](All)
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65"]DOW
[/TD]
[TD="class: xl65"](All)
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65"]Doctor
[/TD]
[TD="class: xl65"](All)
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65"]SDate
[/TD]
[TD="class: xl65"](Multiple Items)
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65"]Edate
[/TD]
[TD="class: 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: xl65"]OR
[/TD]
[TD="class: xl65"]Time Used
[/TD]
[TD="class: xl65"]Sum of Avail1
[/TD]
[TD="class: xl65"]Sum of Utilization%
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]1
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]221
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1020
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]22%
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]2
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]160
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1020
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]16%
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]3
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]321
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1530
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]21%
[/TD]
[/TR]
[TR]
[TD="class: xl67"]Grand Total
[/TD]
[TD="class: xl65, align: right"]702
[/TD]
[TD="class: xl65, align: right"]3570
[/TD]
[TD="class: 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? 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