Excel as a Simulation Tool

Blogger had problems last night and I lost my post. So I'm writing it a day later but dating it for yesterday.

At the UofI Online conference I attended earlier in the week, an Engineering faculty member from UIC complained that most of the attention was on collaboration tools and pedagogy, but what he really wanted was some high end simulation tools - so he could simulate a nuclear reactor online.

I don't know diddly about nuclear reactors but that put a bug in my head and I started to futz around with Excel based on what I had already done with Excelets. After a fashion I learned how to do simple do loops in Macros and with that was able to make a timer that in a particular cell (I chose A1) it would count off...0,1,2,... etc. That turned out to be the key. Once I could do that, Voila!

The first simulation I made was really extremely simple. It's of a ball that follows a parabolic arc as if under the influence of gravity after being tossed into the air, with no wind resistance or other force operating on it. The next one, however, is much more complex. But utilizing the built in random number generators in Excel [the formula is '=RAND()'], which gives a random number between 0 and 1, and then letting all values above 0.5 mean step up, while all smaller values mean step down, I was able to generate a random walk that looks pretty impressive.

In the second case I plotted the entire path of motion as in the way people chart the price of a stock over the course of a day. There are 1,001 time periods in the simulation and 1,000 steps, either up or down. The graph is drawn dynamically as you watch. The screen looks very frenetic because there is a lot going on and tracking the entire time path requires inserting some cells at each step. You can see both of these at this Time Counter spreadsheet. I think they are cool.

But really for teaching and learning simulations of this sort need to allow the student to experiment. So I made another one called Predator and Prey where the viewer can vary some parameters before running the simulation. The results are much different if the predator is slower than the prey as compared to the case where the predator is faster than the prey.

I haven't done this yet but I believe I can make examples where the student can run controls during the simulation. (Think about the old game of pong and controlling the paddle, not a current video game.) So on the one hand this looks amateurish compared to dynamics that these kids are used to. But on the other hand, the mathematics behind the dynamics is much more explicit and therefore teachable. That is the value. Furthermore, once you know how to generate the difference equation for the dynamic process it is very easy to set up the simulation, with or without randomness. So the students themselves could make these simulations as part of their course work. That would be awesome.