Opinions on Pivot Table Analysis

I work for a company that rents cars by the hour (à la ZipCar) and I'm trying to find the following metric: Revenue per Car per Day.

The problem is as follows: The cars are numbered but they do switch locations (i.e car 20 could be parked at x street in February but it would be moved to y street in March if it performed poorly in February).

The solution to this was to perform the analysis firstly by location, then subhead by car number. The pivot table field list had Date in columns, Location followed by Car ID in rows, and Sum of Revenue in Values.

This showed me how much each location was generating per month and would let me see how each car at that location was doing, and if the car was moved in a certain month, it would give me an empty field. This was great.

Recently, my boss asked me to find Revenue per Car per Day. To me, this means that I would need to find Revenue per Location per Day and then have that divided up into cars per location.

I'm struggling to do this. Here are the headings of the data I currently have:

'Car ID used for Trip' 'Car Location' 'Date of Trip' 'Revenue per Trip'

The pivot table sums it all up per month. I would like to have it divide the number it gives me by the number of days in that month. For example, if in September 2013, we generate $3,000 at location x, I would like it to show me that we generated $100/day ($3000/30 days).

Any help would be greatly appreciated.

HI, welcome to the forum

To me, "Revenue per Car per Day" means simply just that - how much is each car making him per day. Sounds like he already has the location info, now just needs what each car is making?

see if the attached will help too, I added 2 new sheets, 1 for a daily car summary, and the 2nd for the overall summary

Thank you, gentlemen. I'm in the middle of studying your files.