Simple for you, difficult for me

[GW1]

Hi Guys

I have a dataset with car usage on it (driver service). I created a distinct flag and aggregated accordingly, bringing my set down from 50,000 to 10,000. I have the following fields: Date, pick up time, drop off time and location (and some others). I have also created various diffferent flags.

I need to understand how many cars were being used at any given point in time (which is straight forward to do, via pivot table?) and also (more importantly), say there are 10 booked requests in a day (given that a car can be busy for 1 hour and 30 minutes at a time, how many cars are need to fulfil the 10 different journeys. I know that if all the cars were booked for the same time, that would be 10 cars, but how do I work it out for when the requests are spread over the day?

I tried created a matrix to illustrate this, but just can get it to work. The idea being this is that i tried to 'plot' the start and the end times (when a car is in use). My formula skills just aren't up to scratch.

Can anyone suggest an approach/formula I can use please?

Thanks very much

 

[GW1]

Hi shg4421

Thanks for the quick reply.

Oh, the dates are incorrect. Must have happened when I coped them over. Sorry. They are not like t hat on the master sheet I was working off originally.

Can you please disregard the rows that have this issue and input them into the sheet?

There are two sheets as they have different duration times. Also, the sheets are specific to a particular set of cars. If you feel they can be merged and then identified by another columm (flag) then I am happy for that approach.

Thanks

 

[GW1]

I originally just had a time stamp, but in order to replicate the layout of your sheet, I think I added in the start date to both the start and finish times, thus if the clock went past 00:00, the date stayed the same. Sorry.

I can add those times back in later.

 

[GW1]

Also, you have end times that are before the start times, e.g.,

       -------C-------- -------D--------
  11   10/01/2009 22:30 10/01/2009 00:00

That won't work.

...thinking about it, it may explain why the process was artifically stopping at 0...

In anycase, I hope it is easy enough for you to get into the input sheet...

 

[shg]

See http://www.box.net/shared/e71gora6h3

 

[GW1]

See http://www.box.net/shared/e71gora6h3

Thanks shg4421

I see where I went wrong. Did you take out the dates with 00:00 to make it work?

I can take this now an replicate for the next data set.

Is there a direct way to calculate the number of cars needed per day? I know 'Sim' refers to the number if overlaps. How can I convert it into 'e.g. 7 cars needed for 01/10/2009'?

Thanks again.

 

[GW1]

It's interesting that over the entire data set, there is nothing higher than 4 (Sim). That's a lot lower than I would expect from a car service.

 

[GW1]

See http://www.box.net/shared/e71gora6h3

Hi shg4421

Thanks for the sheet, I managed to also calculate the max number of cars required for any given day, regardless of the number of journeys that have been booked. I was a ray of light at 3am! I also resolved the 00:00 time/date issue.

I am trying to use the same sheet to input the next set of data (which I uploaded to you yesterday), which for some reason won't work....is there a 'range' issue given the sheet was setup for a larger data set?

When I paste the new data in and delete the old from the bottom of the list (as there are far fewer records this time), I get an error in the #Sim: "Invalid" and "Circular Reference". Can you advise please.

 

[shg]

Post a link to what you've done.

 

[GW1]

Post a link to what you've done.

Morning shg4421

Sorry, I can't upload files from this terminal.

All I did was paste (the data from the second spreadsheet tab) into the sheet you did yesterday.

 

[GW1]

Post a link to what you've done.

Hi there, hold on a minute, I am uploading now....