On an Excel Kick

Since I talked about using Excel for grading yesterday, kind of a natural and something that a lot of instructors do, let me talk about Excel for teaching, especially in some ways that may be less expected. In the process, let me get back to one of my key themes --- students as content creators.

Excel can be used as a numerical simulation tool and it's not half bad at it. Furthermore, I would argue that it has some distinct pedagogic advantages because one can make overt to the students how the simulation is generated. Here is a simple example of this sort of thing. (You must Enable Macros. If Excel won't let you do this, do the following. Launch Excel separately, go to the Tools Menu, then Macro, then Security and switch your security level from High to Medium. Then quit Excel and try again.) There are two tabs. Choose Simple_Motion, because this is easier for exposition.

Push the Run Sim button. Then move your attention first to the graph itself, then cell A1 which has the Time Counter number in it (and which changes during the simulation) and the Point Position which is given in cells A21 and B21. If necessary, reset the simulation and run again. You should have a sense of what is changing.

Next you can click on cells A21 and look at the formula bar to see how the Point Position depends on the value of the Time Counter. Do likewise for cell B21.

Now right click on the graph and choose Source Data and select the Series Tab. By clicking on the little boxes to the right on the X Values line and the Y values line, you can see exactly which cells are generating the graph.

Finally, run the simulation one more time (reset if you have to) but this time look at cell A2. This will explain how the time counter works and provide understanding for the underlying macro. The value placed in cell A2 is one plus the value in cell A1. Then cell A2 is copied and pasted back into cell A1 but as a number, rather than as a formula. After that the content of cell A2 is deleted (which is why you see it flash on and off). Then the entire thing repeats. There are 1000 iterations done via a Do Loop.

The point here is that students can readily hack the spreadsheet and figure out what is going on. for themselves. (They can also look at the Macros directly and hack those, but that's a little more detailed so I won't discuss here.) The hacking is a different type of learning than most instructors encourage. I think it has real value. It is the obvious first step toward the goal of getting the students to make their own simulation.

Let me turn to the other worksheet quickly, the one called Random_Walk. This is more complex than the other in several ways: (1) It shows the complete path not just the current position. (2) To record the entire path cells are inserted at each step and the old cells are pushed down. (3) It has real dynamics in that there is a recursive equation that generates the current position. The current position is not just a function of the time counter. The previous position matters. (3) It has a stochastic process as a driver of the dynamics. The model uses Excel's pseudo random number generator. A random number is generated during each iteration of the Time Counter.

This second simulation uses a fair amount of the processing power of the viewer's computer. With modern CPUs it runs with no sweat. With a computer that is 5 or 6 years old, it would be more like the Little Engine that Could. The point of considering this second simulation is that interesting complexity can be introduced. If one were trying to teach this stuff with equations, it would be a bear and the students would almost certainly not get it. But when they can see the process unfold, it is very easy to get it and that facility should encourage students to think directly about the dynamics.

One other thought on this. Because we are talking about spreadsheets that can reside essentially anywhere and are readily transferred, one can imagine class projects where the goal is to design simulations that high school students would use. A simulation would need an accompanying narration and would have to visibly convey the underlying idea. Having projects of this sort, I believe, would really engage the students and could very readily create a collection of simulations that would give some nice external visibility to the class.