Traffic Problem - Solved Using Microsoft Excel

1. Statement of Problem

The problem is to find the maximum number of cars passing a point in a given amount of time. This number (x) changes with a differing velocity (v) and is dependent upon the length of a car (l) and the distance between cars (d). For simplicity’s sake, an average l is set at 5m and the set time is 60 seconds.

2. Goals of Activity with Connection to NCTM Standards

This activity greatly incorporates two algebra topics that NCTM has designated to receive increased attention in grades 9-12. First, the traffic problem is a real-world problem that requires the application of theory. Second, by solving this problem on Microsoft Excel, the activity uses computer utilities to develop a conceptual understanding of the information at hand.

3. Description of Approach to the Problem

As a means of attacking the problem, I decided that a given distance would be comprised of (x cars)*(l) + (x-1 spaces between cars)*(d). However, upon consulting Professor Hans-Georg Weigand on the problem, he advised us that a sufficient equation is (x cars)*(l + d). Still regarding this as a distance, we inserted it into the equation velocity = distance*time. Then, solving for x, we obtained the equation x = v*t/(l+d). After substituting constant values for t and l, we obtained x = v*60/(5+d). All that remained to be done was to evaluate for x with respect to 3 condition of d: 1) d is constant; 2) d is proportional to v; 3) d is proportional to v^2. However, as it turns out, a constant must be solved for when working with the third condition. This value was obtained by performing a simple calculation on data found in Road & Track magazine.

4. Description of Appropriateness for Secondary Level Audience

We concluded that this traffic problem is most suitable for an advanced algebra or college algebra class. The students should work in groups of four to solve the problem. When it comes to finding the stopping distance related to the velocity squared, not only could students gather data from a magazine, they could measure the stopping distance of a car in a parking lot.

5. Findings

There were several important findings in this activity. First, we learned that our value of d cannot be constant or proportional to v because the result is that the greater the velocity the greater number of cars. We know that this result is unrealistic because having 5m or even 40m between cars travelling at 40m/s is extremely dangerous and thus not a feasible result. Also, we learned that having distance proportional to velocity squared is an accurate situation, provided that a constant is included to account for the realistic stopping distance of a car. From this fact, our equation demonstrated that the maximum number of cars pass the point at 12m/s.

6. Effects of Using Excel

Excel proved to be extremely beneficial in efficiently solving this traffic problem. For one, it made hundreds of calculations faster than any human or calculator could have done. Excel makes it easy to see what happens for different values of car length, velocity, or any other variables. Having all the data in the spreadsheet form makes it easy to understand and convenient to display the results graphically.