Counting based on date ranges

Hi all, I'm building a graph off a pivot table in excel (because Salesforce.com's reporting tools are woefully inadequate).

I've attached a picture to give an idea of the pivot table I'm trying for
Capture.PNG


The column I haven't created yet, which I call "Average # of Open Cases " - which is different than the # of cases opened that month (happy to clarify if necessary). I have the Case open date and the case close date in my source data, which should be enough. Ideally I'd be able to give an average # based on how many cases were open on any given day of each month, but I can also work with the number of cases open at a fixed date each month (like the beginning or middle of each month).


To help clarify, here's how I would get the values for "# of Open Cases" manually if I had to:

- I would create a separate table of dates covering the entire span of my open dates (ie, a row for each day between Oct 2012 and Aug 2013)

- For each date on this table, I would go through the case record in my source data to see if it was open on that date (ie, if the given date was between the Open Date and Close Date of the case)

- With that done, I can add the tallies to each date record, so my table would show the number of open cases for each date.

- Then I could average the open case count across the days in each month, to get my desired average count of open cases per month


I'm hoping there's a way to handle this without the manual process! Is this something Pivot Tables can help with?

Thanks!

61 Views and no responses? Am I in the wrong section? Was my question unclear or inappropriate?

I appreciate any feedback you can give me!