Coffeehouse Post

Single Post Permalink

View Thread: Simulations using Excel
  • User profile image

    Hi members,

    I am a high school teacher ( more details in my profile ).

    I have one student who is modelling travel times between bus stops on a particualar bus route. The model assumes that the Total Travel time between two bus stops is the Passenger loading time plus the Bus travel time between the stops.

    The Passenger loading time is dependent on the number of passengers ( No. of passengers ) who wish to board the bus multiplied by the time is takes to serve each of them. This Serving time per passenger is taken to be a constant ( 10 seconds per passenger, for illustrative purposes ). No considersation is made for the time it takes to off load passengers. ( We assume that the time it takes for off-loading passengers to leave the bus does not influence the model )

    The Bus travel time between bus stops is taken to be a constant. So no consideration for varying amounts of traffic on route or speed of the bus is made.

    So in brief,
    Total Travel time = Passenger loading time + Bus travel time


    Passenger loading time = No. of Passengers * Serving time per passenger

    No. of Passengers = RANDBETWEEN(0,15)
    (RANDBETWEEN(0,15)  generates a random number between some limits )

    FINALLY...., ( sorry, if it was too much of mouthful )

    The point is I wish to use Excel to run the simulation a 1000 times. If you like refresh the calculations so that new random numbers of passengers will be generated and hence different Passenger loading times. And then collect a frequency distribution for different classes of observations ( Total Travel times ) in table from which a histogram could be generated using the Excel wizard chart functionality.

    How can this be done in code ? Using a subroutine and perhaps a single button to start the simulation on the spreadsheet.. ?

    I hope it makes sense. And I hope it is not too much to ask this forum.