I'll do my best to explain.
I have data which has the Start Time and Period of Time an activity is carried out for. e.g.
Name Start Time Activity Length (mins)
James 08:00 45
Phil 10:10 30
Angela 10:20 50
I want to display a graph with Time on the x-axis and a count on the y-axis for every time an activity is in progress during that time period.
I'd like to have this in a PivotTable as the data will change often (currently saved as an Excel Table Table1), and it'll interact with slicers connected to other graphs. I've shown below what I would expect the PivotTable to look like based on the data above (not including James).
e.g.
Time Count
10:00 0
10:05 0
10:10 1
10:15 1
10:20 2
10:25 2
10:30 2
10:35 2
10:40 2
10:45 1
10:50 1
10:55 1
11:00 1
11:05 1
11:10 0
11:15 0
11:20 0
This is simple enough without Pivot Tables using a countif :
=countifs(StartTime,"<="&A1,StartTime+(ActivityLength/1440),"<"&A1),
but looks like I would need a Pivot if the data is constantly changing and several filters need applying. By the way, I'm using Excel 2010