Results 1 to 4 of 4
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    (Trip planner) Bus Schedules (Excel2000+)

    Bus/Plane/Train/Car Schedules
    The worker needs to travel by bus from home to office, and back. Happily the trip will involve three separate bus routes, which doesn’t faze the worker, because he/she/it is an avid book-reader.

    The worker obtains the data for the bus schedules (in the case study here Mississauga Transit’s bus routes 3 West, 9 North and 38 South, all available from Mississauga Public Transit and look for Click’N’Ride Online Trip Planner).
    The first leg, 3West, has frequent service. The second leg, 9 North, has less frequent service, and the third and final leg, 38 South, has an intermediate service.

    The worker would like to optimize the trip subjectively in terms of start/arrival time and length of trip. (In the case study, I’d choose the 6:35 AM bus that gets me to the office at 8:41, rather than the slightly later 6:44 that squeezes me in the door at 8:59, but there you go ...)

    We note with interest that the MINIMUM time waiting for a bus at a transfer station is 5 minutes, and we figure this is enough as a safety margin for small delays on any one leg. Remember – we read books!
    Of course, the Excel Worksheet Functions can be collapsed to reduce the number of columns visible to the user.
    Of course we can eliminate the ugly #NA and #REF!.
    Of course we can encode the Excel Worksheet Functions into a nifty little bit of VBA code that takes into account any number of legs for a multi-leg trip (sozz the ankle, Jezza!), ...

    But can you come up with a better set/sequence of functional steps which render a mass of transit data into a desktop trip planner?
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: (Trip planner) Bus Schedules (Excel2000+)

    The attached version uses the same idea, but simplifies the formulas a bit by sorting the tables descending, so that MATCH can be used with -1 as last argument. This avoids the extra step of adding 1 to the index. I didn't need the original columns J and O any more, and used these instead to add a safety margin of 5 minutes (configurable) to the arrival time. The MATCH functions use these columns so that you'll always have at least 5 minutes for the transfer.
    I also corrected the error in column S.
    Attached Files Attached Files

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: (Trip planner) Bus Schedules (Excel2000+)

    Hans, thank you for the diligent response. My delay in replying is due to my travel by diligence.

    >sorting the tables descending
    Thought of that but elected to stay ascending as that's how the data arrives from the schedules. Less room for error if I reduce the manipulation of source data. And yes, I am capable of error ...

    >didn't need the original columns J and O any more
    (from memory) these were working-data columns anyway and would have been absorbed into the final formula

    >safety margin of 5 minutes (configurable)
    My usual ploy too. But then the schedules gave me thought. In some of the transfers I merely need to walk across the street, or even wait at the same stop. Too, Mississauga transit is notorious for being on schedule, to the extent of idling at specific stops to let the clock catch up. That is, they are regular as cloche-work. In those cases where there is a 2-minute wait and the arriving bus proves tardy, one would have to switch to a more reliable set of legs/route. Waiting 5 minutes in mid-January weather is no joke. That's why, too, I mentioned subjective. For some people (me!) a seven-minute wait in the shopping centre transit point is preferable to a 2-minute wait in drenching rain on the prairie.

    All in all I am attracted by letting the user choose the route after offering the data. This is a very subjective area. Mississauga transit drivers have been known to flash/signal their buddies when they are running late and have a regular passenger making a transfer, even stopping mid-roadway so the passenger can hop across the road. In winter time they refuse to stop at the scheduled stops, instead, dropping individual passengers at their ploughed office driveways. It's a very personal system.



    >I also corrected the error in column S.
    >I also corrected the error in column S.
    >I also corrected the error in column S.

    Trip-ly (hah!) obliged to your eagle eyes for this.
    How did you spot it?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: (Trip planner) Bus Schedules (Excel2000+)

    You could always write a tiny macro to sort the timetables in descending order. But on the other hand, ascending is a more 'natural' way to look at them.

    > How did you spot it?

    I filled down the formula from the top cell and noted that the result contained too many #N/A's.

Posting Permissions

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