Bus Timing Calculation - Help!!

[Eddie G.]

I am banging my head against the wall with this....and I need help..... I will try to explain this correctly:

I have a bus timing sheet where I track the arrival time and departure time of buses at airport curbs. I get the calculated time by subtracting the latest departure time from the latest arrival time (arrival intervals are recorded:
EXAMPLE

ARRIVAL DEPARTURE TIMING
10:00:00 10:00:05
10:01:00 10:02:00 0:00:55
10:03:00 10:05:00 0:01:00
10:03:00 10:04:00 ########
10:06:00 10:08:00 0:02:00

The timing is derived for example by subtracting the latest arrival time from the last departure time examplecell E5-cell F4). The problem is, when two or more buses arrive at the same time the ##### occurs. I need the timing formula to search above, and if ###### exists, then for the above example, change the calculation to (cell E5-cell F3). In other words, find the latest departure time in the departure column to subtract. I hope this makes sense.

[Brian from Maui]

when you subtract times values must be a positive value, or Excel returns ######

[Eddie G.]

correct brian, the dilemna as outlined in the example, is if a second bus arrives while another is already there, and the latter leaves before the first one, then the #### occurs. what I need is a formula that looks for the last departing bus time in the daparture column.

[Brian from Maui]

On 2002-04-13 13:49, Eddie G. wrote:
I am banging my head against the wall with this....and I need help..... I will try to explain this correctly:

I have a bus timing sheet where I track the arrival time and departure time of buses at airport curbs. I get the calculated time by subtracting the latest departure time from the latest arrival time (arrival intervals are recorded:
EXAMPLE

ARRIVAL DEPARTURE TIMING
10:00:00 10:00:05
10:01:00 10:02:00 0:00:55
10:03:00 10:05:00 0:01:00
10:03:00 10:04:00 ########
10:06:00 10:08:00 0:02:00

The timing is derived for example by subtracting the latest arrival time from the last departure time examplecell E5-cell F4). The problem is, when two or more buses arrive at the same time the ##### occurs. I need the timing formula to search above, and if ###### exists, then for the above example, change the calculation to (cell E5-cell F3). In other words, find the latest departure time in the departure column to subtract. I hope this makes sense.

Eddie,

From your example, E5-F3 will still be negative. If you want departure intervals, subtract times in column F. If you want first in, first out with no regard to bus A,B,C...etc Column F should be sorted

[Eddie G.]

I finally figured the darn thing out by myself

=sum(e5-max(f1:f4)) and so on with max being adjustable to look for the latest departure time as far up as I want in the departure column.

[Brian from Maui]

Eddie,

Would this work:

A1 - first departure time
B1 - second arrival time
C1 - B1-A1

Then you wouldn't have to adjust your range.
I've used your examples, but there maybe other criteria which might not give you the desired times.

[nisht]

Against the name of luanne download the file
'Ship Time'

he had similar problem like you.. it was related to ships.. you can study the formula there and still if you have difficulty you can write to me
nisht@pexcel.com
nishith desai
http://www.pexcel.com

[nisht]

Against the name of luanne download the file
'Ship Time'

http://www.pexcel.com/guestdownload.htm

he had similar problem like you.. it was related to ships.. you can study the formula there and still if you have difficulty you can write to me
nisht@pexcel.com
nishith desai
http://www.pexcel.com