Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Bus Timing Calculation - Help!!

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    OKC
    Posts
    98
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  2. #2
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    OKC
    Posts
    98
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  4. #4
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    OKC
    Posts
    98
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  6. #6
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Ahmedabad Gujarat
    Posts
    303
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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
    ni****h desai
    http://www.pexcel.com

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Location
    Ahmedabad Gujarat
    Posts
    303
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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
    ni****h desai
    http://www.pexcel.com

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •