(modified from a post that originally appeared at http://www.KABT.org)

On numerous occasions I have argued that trying to model H-W equilibrium in classroom with activities such as the AP Biology H-W lab, the M & M’s labs (http://www.accessexcellence.org/AE/AEPC/WWC/1994/mmlab.php) or with beans suffer from too small of sample size (population) or the models are simply too tedious for the students to explore. Computer spreadsheets provide a unique environment that allow students to build and test their own models on how a population’s gene pool can change. The testing, in particular, provides for a powerful learning experience for teacher and student.

Most spreadsheets have a “Random” function that can generate random numbers to model stochastic events. Like flipping a coin or drawing a card at random in the AP Biology Lab 8 H-W lab the “Random” function serves as the basis for our spreadsheet model. Unlike our physical models/simulations (like the M and M’s lab) the computer can generate thousands of samples in a very short time. The benefits to learning are worth the challenge of trying to learn how to build the spreadsheet.

In this post, I’ll present the essential parts of an EXCEL spreadsheet (other spreadsheets will work as well) that can be used to explore some of the first principles of the effects of population size on genetic drift. In addition, this post is long-winded because I’m attempting to provide the strategies and questions I would use to encourage my students to develop their own computer-based models. This is not presented as the definitive spreadsheet model or approach but rather a rather simplistic model to be constructed and modified by your students. The idea is that if the students can find their way through building this model it can serve as a foundation as they extend the model to explore more of the parameters that affect H-W.

I’ve tried to break down the more complex model into a series of manageable steps. I’ll cover the extensions to this model and others in future posts. BTW, it takes longer to read this post than it takes to make this relatively simple spreadsheet. I suggest that you bring up EXCEL or some other spreadsheet in a different window and try to create this worksheet as you follow follow along. Be thinking what questions you will ask your students so they can develop their own version of this spreadsheet. Once you’ve mastered this and can create or modify it at will, then try it out with your students–they can handle this level of difficulty. They just don’t know it, yet–that’s your challenge as their instructor. And when they do succeed, with your guidance, they will have an effective tool to explore the basic principles of H-W equilibrium–one they have created themselves.

Step by step instructions follow, below the fold: