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

 

[shg]

Here's one way:

       ---------A---------- -B-- ---------C---------- --D--- -------------------------------------------------E-------------------------------------------------
   5          Begin         Dur          End          In Svc                                                                                                    
   6   Mon 11/22/2010 10:15 0:35 Mon 11/22/2010 10:50     1  D6 and down: =SUMPRODUCT((rgnBeg < myEnd) * (rgnEnd > myBeg))                                      
   7   Mon 11/22/2010 11:15 0:30 Mon 11/22/2010 11:45     1  conBig RefersTo: =9.99999999999999E+307                                                            
   8   Mon 11/22/2010 16:00 1:45 Mon 11/22/2010 17:45     2  rgnInp RefersTo: =INDEX($A:$A, ROW(Sheet1!$A$5)+1):INDEX(Sheet1!$C:$C, MATCH(conBig, Sheet1!$A:$A))
   9   Mon 11/22/2010 17:15 1:05 Mon 11/22/2010 18:20     2  rgnBeg RefersTo: =INDEX(rgnInp, 0, 1)                                                              
  10   Mon 11/22/2010 18:50 1:20 Mon 11/22/2010 20:10     1  rgnEnd RefersTo: =INDEX(rgnInp, 0, 3)                                                              
  11   Mon 11/22/2010 21:35 1:05 Mon 11/22/2010 22:40     1                                                                                                     
  12   Tue 11/23/2010 02:50 1:20 Tue 11/23/2010 04:10     2

 

[shg]

There's a more rigorous example at http://www.box.net/shared/x81ru6uuec

 

[GW1]

There's a more rigorous example at http://www.box.net/shared/x81ru6uuec

Hi shg4421

Thanks for the great examples. I think this is the direction I need to take!

Really appreciate your guidence.

 

[GW1]

Hi shg4421

Thanks again for your help. I'm having trouble linking the tables in the input sheet with the output sheet (from the link above).

I used the SUMIF formula on the input sheet, but this does not translate into the output sheet. All I get is 'NAME?' in all the cells.

 

[shg]

What version of Excel are you using?

 

[GW1]

Version 2007

Below is a sample of data I have, would the SUMIF formula work in the same way as in your example? (where the formula would illustrate the number of cars needed per day)
<TABLE style="WIDTH: 563pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=749><COLGROUP><COL style="WIDTH: 33pt; mso-width-source: userset; mso-width-alt: 1877" width=44><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2773" width=65><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2602" width=61><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 4565" width=107><COL style="WIDTH: 93pt; mso-width-source: userset; mso-width-alt: 5290" width=124><COL style="WIDTH: 88pt; mso-width-source: userset; mso-width-alt: 4992" width=117><COL style="WIDTH: 76pt; mso-width-source: userset; mso-width-alt: 4309" width=101><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 5546" width=130><TBODY><TR style="HEIGHT: 28.5pt; mso-height-source: userset" height=38><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; WIDTH: 33pt; HEIGHT: 28.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27 height=38 width=44> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; WIDTH: 49pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27 width=65> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; WIDTH: 46pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27 width=61> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; WIDTH: 80pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27 width=107> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; WIDTH: 93pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27 width=124> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; WIDTH: 164pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl30 width=218 colSpan=2>(busy between pickup and drop off time)</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; WIDTH: 98pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27 width=130> </TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; HEIGHT: 12pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27 height=16>ID</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27>Date</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27>Direction</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27>Location (to/from)</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27>appointment time</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27>Pickup time</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27>drop off time</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27>duration (time in use)</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; HEIGHT: 12pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27 height=16>a00001</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl29>11/10/2010</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27>Inbound</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27>Home</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl28>09:00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl28>09:15</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl28>10:30</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27>1.15</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; HEIGHT: 12pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27 height=16>a00002</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl29>11/10/2010</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27>Outbound</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27>Business</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl28>09:20</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl28>06:20</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl28>07:50</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27>1.3</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; HEIGHT: 12pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27 height=16>a00003</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl29>11/10/2010</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27>Inbound</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27>Home</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl28>10:30</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl28>10:45</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl28>11:15</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27>0.3</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; HEIGHT: 12pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27 height=16>a00004</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl29>11/10/2010</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27>Inbound</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27>Home</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl28>12:00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl28>12:15</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl28>13:30</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27>1.15</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; HEIGHT: 12pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27 height=16>a00005</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl29>12/10/2010</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27>Outbound</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27>Business</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl28>14:00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl28>11:00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl28>12:30</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27>1.3</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; HEIGHT: 12pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27 height=16>a00006</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl29>12/10/2010</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27>Inbound</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27>Home</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl28>14:20</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl28>14:35</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl28>15:45</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27> </TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; HEIGHT: 12pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27 height=16>a00007</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl29>12/10/2010</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27>Outbound</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27>Business</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl28>14:20</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl28>11:20</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl28>12:50</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27> </TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; HEIGHT: 12pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27 height=16>a00008</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl29>12/10/2010</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27>Outbound</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27>Business</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl28>15:00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl28>12:00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl28>13:30</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #909090; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27> </TD></TR></TBODY></TABLE>

Thanks

 

[shg]

Grab the file at http://www.box.net/shared/98lsidtngx and see if you can put your data in that format.

 

[GW1]

Grab the file at http://www.box.net/shared/98lsidtngx and see if you can put your data in that format.

Hi shg4421

I tried putting a sample of my data in. It looked like it worked (there are figures in both the input and output sheets), but I am not sure how to interpret it (does the sumif formula illustrate how many cars are needed to cover all journeys in any given day?).

Also, I am not sure what my min/max/round should be given that I have two types of journey, both inbound and outbound. The journey times for both are different:

Outbound = 1:30 hours in duration
Inbound = 1:45 hours in duration

Thanks again for your help and your time.

 

[shg]

The SUMPRODUCT formula on the Output tab tells the number of overlapping events.

The Min/Max/Round values are there purely to generate random event durations for test. You would enter the actual start time and duration for each event on the Input tab.