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]

OK, I see, thanks.

I have copied the actual start and end times into the sheet you sent. For some reason, there is nothing in the output sheet chart (blank).

In addition and just to confirm, the sheet is designed to determine whether there are any 'overlaps' during any particular time, but does not indicate exactly how many cars would be required for the day. I noticed that there are a few '0's' which means that there is no overlap, but does that mean that a car would still be required for the trip? How would I sum that to give an overall day total?

Thanks

 

[GW1]

Hi shg4421

Firstly, thanks again for your last reply. I have pasted my own values on the the input sheet, but there is no output graph on the output sheet, infact, the sumproduct numbers look different to the ones I also calculated on the input sheet (just of out interest, I was trying to see how the sheet worked) but I am not sure if that is relvent. What I am doing wrong?

Also, just to confirm my understanding of the spreadsheet, it is designed to identify 'overlaps' at any given time. I noticed that there were a few '0' on the sumproduct column. Is that because there was no overlap BUT a car was still used? How can I determine the exact number of cars required for any given day? Will this sheet allow me to do that?

Thanks again, hopefully this is the last time I bug you over this.

 

[shg]

The output graph was kind of ad-hoc for testing, and the start and end dates are fixed. You don't need it, but you might take a look at the original data to understand what's happening.

Each trip has two events, a start and an end. The Output sheet generates a sorted list of start and stop events with no duplicates. When the #Sim column says zero, it means that there are no trips active following the event (which must mean it was a stop event).

My sheet had start times and durations, with end times calculated from those; if you want to enter start and end times manually, that's fine, but you shouldn't be overwriting any other formulas; you should only need to copy existing formulas down.

 

[GW1]

Hi there

Sorry, have been on a call. Just read this but sent a private message about 30 mins ago.

You mentioned on a previous post that I could add both start times and end times (pasting original data) into the input sheet, which is what I did.

The data I have is an 'actual' trip so there should not be any zero's on the #Sim in the output sheet, so I must be deleting a formula somewhere?

Will I be able to gauge the number of cars needed to cover all trips? Of is the maximum number of overlaps the maximum number of cars needed also?

 

[shg]

See my reply to your PM.

 

[GW1]

just replied back, thanks.

 

[shg]

You can post a link here to the workbook. You can include all the data or a portion, as you wish.

 

[GW1]

Hi shg4421

I have uploaded a raw file with with two different worksheets. There are also some descriptives.

http://www.box.net/shared/svn6azxyax

Please confirm that you can download the data else I can try to send you another link.

Thank you

 

[shg]

What is the relevance of "to" and "away"?

Why not combine them on a single sheet and add a column to distinguish?

 

[shg]

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.