How can I calculate per hour, per day volumes if one or multiple days' data is selected?

[louwen]

hi. I have a scenario where i need to calculate the hourly and daily volume work effort of staff using specific criteria such as the days are 7 hours and days are 21 days in the month.

Easy enough i know, except i am using SLICERS to select the relevant month as well as the specific day in that month. This means that if I select one day only, the day data should only show that day and obviously the hourly average. But if I select multiple days in my SLICER I need some sort of formula to step in and then re-calculate the averages accordingly knowing that more than one day has been selected?
Below is an example of what I am looking at;

The Volume is for two days as selected via SLICER.



[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[/TR]
[TR]
[TD]Actual Volume
[/TD]
[TD]Per Hour (7 hrs)
[/TD]
[TD]Per Day (21 days)
[/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Andrew Poulsom]

What is your Slicer controlling?

 

[louwen]

Hi. Controlling multiple Pivot sheets that are linked to a database. Made them to keep users from having to filter on multiple sheets and by accident mess the whole process up.

 

[Andrew Poulsom]

Can you use GETPIVOTDATA to return the count of days from one of your pivot tables?

http://www.contextures.com/xlPivot06.html

 

[louwen]

I do have GETPIVOTDATA formulas, yes. they help consolidate some data onto this front sheet. however, in this case i use them to consolidate the relevant data to another sheet an from that sheet do a VLOOKUP to this Column.

 

[Andrew Poulsom]

So can you use it to count the days?

 

[louwen]

Hi. Unfortunately the days or months data are from a live database. so no. I can only select the specific day eg Monday September 15 2014 as part of the filter on the pivot.

 

[Andrew Poulsom]

Can't you create a pivot table (linked to your Slicer) like this to count the dates?

Excel 2010
	A	B	C	D	E	F
1	Date		Row Labels	Count of Date		7
2	01/09/2014		01/09/2014	1
3	02/09/2014		02/09/2014	1
4	03/09/2014		03/09/2014	1
5	04/09/2014		04/09/2014	1
6	05/09/2014		05/09/2014	1
7	06/09/2014		06/09/2014	1
8	07/09/2014		07/09/2014	1
9			Grand Total	7
Sheet6

Cell Formulas
Range	Formula
F1	=GETPIVOTDATA("Date",$C$1)

 

[louwen]

hi. Not sure if this will help, as it will need to be done for more than a year worth of dates as the data available goes back 3 years.

 

[Andrew Poulsom]

I don't see how the number of dates makes any difference.