Modeling the variables to calculate time for commuting to work

ShareThis

Ultimately we want our model to give output in terms of time - the number of minutes and seconds our commute will take.  Therefore to simplify the model, we'll convert each of the variables to time rather than MPH before adding the variables together.

The first variable in our commuting algorithm is this:  Drive 2 miles on a highway, with 90% probability you will be able to average 65 MPH the whole way, but with a 10% probability that a traffic jam will result in average speed of 20 MPH.  Converting each scenario into time rather than MPH requires simple arithmetic.  Driving 2 miles at 65 MPH requires (60 x 60) x 2 ÷ 65 seconds, or 111 seconds.  At 20 MPH, the time required is (60 x 60) x 2 ÷ 20 seconds, or 360 seconds.  To model the probabilities, we'll use the RAND() function to return a number between 0 and 1.  If the number is less than .9, we'll use the 65 MPH outcome, otherwise we'll use the 20 MPH outcome.  An Excel equation modeling this result is:

=IF(RAND() < .9, 111, 360)

The second variable follows a traffic light through a 120 second cycle where RED lasts 90 seconds and GREEN lasts 30 seconds.  If we hit it on green then there's no delay, but if we hit it on red we wait until it turns green.  This problem is a little tricky because if we make two separate references to RAND() in the equation, each reference will return a different value.  Thus we have to find an equation that uses RAND() only once.  The following equation does the trick:

=MAX(0, (RAND() * 120) - 30)

Let's analyze the equation.  The value "RAND() * 120" inside the inner parentheses simulates the number of seconds remaining in the 120 second cycle after we arrive at the traffic light.  If we arrive during the first 90 seconds of the cycle, we have to wait until the first 90 seconds of the full 120 seconds are elapsed.  This is equal to (TIME REMAINING) - 30.  If we arrive during the last 30 seconds of the cycle, we don't have to wait at all.  In this case, "TIME REMAINING - 30" is a negative number, so MAX(0, TIME REMAINING) returns 0 wait time.

Recapping the third leg of the commute: "Travel 2 more miles on a surface street.  70% of the time you travel at 30 MPH.  10% of the time you average 20 MPH, 10% of the time you average 40 MPH, and 10% of the time there's a traffic jam that takes you 30 minutes to travel these two miles."

This third variable is similar to the first, but contains four scenarios instead of two.  Therefore we will use  "nested if" statements to handle the second, third and fourth scenarios.  Converting the four scenarios into seconds gives us the following:

• (60 x 60) x 2 ÷ 30 = 240

• (60 x 60) x 2 ÷ 20 = 360

• (60 x 60) x 2 ÷ 40 = 180

• 30 x 60 = 1800

Now we construct the Excel equation to give us each of the above outcomes according to their probability.  Again we have the limitation of using only one instance of the RAND() function.  One way we can achieve this is by using the CHOOSE() function as follows:

=CHOOSE(INT(RAND()*10) + 1, 240, 240, 240, 240, 240, 240, 240, 360, 180, 1800)

Another way to achieve the same result is by using a lookup table.  We can create the lookup table as follows:

The Excel equation for the third variable using the lookup table approach is as follows:

=VLOOKUP(RAND(), $C$8:$D$11, 2)

In our construction of the model on the next page we will use the CHOOSE() function approach.

Prev | Contents | Next