Hardy-Weinberg Population Modeling Lab for AP Bio

I decided that this year, we would bite the bullet and do the AP Biology mathematical modeling lab for populations.  Last year I found a YouTube video of a teacher explaining how to set up the spreadsheet to start the lab.  He did a nice job of explaining what the formulas that students would need to use meant.  For his class, they used Excel, but we generally use Sheets in our class.

I was stoked to see a new resource this December in the College Board AP Biology Community. This resource, done by Brittany Franckowiak was step by step instructions for students to set up a population in Sheets that was in Hardy-Weinberg equilibrium.  Then students are set free to figure out formulas to represent populations that are exhibiting heterozygote advantage, fatal recessive, or small population size (genetic drift).

When I'm doing a lab for the first time, I like to do it myself before I ask my students to. It was easy to follow the step by step directions to create the 5 generations of populations in Hardy-Weinberg Equilibrium.

Then I started thinking about how to represent heterozygote advantage.  In the card based Hardy-Weinberg lab we did last year, for heterozygote advantage we tossed a coin when a homozygous individual was born.  If the coin was heads, they lived, tails, they died.  In the first part of the mathematical model we create, we used an "if" function with the "random" function. I decided that I could add an extra "if" and "random" function to the existing "if" statement that counted homozygous genotypes of zygotes. It would act like a coin toss to decide if the individual would survive or not.

I excitedly shared my newly created formula with my computer programmer husband who regularly manages spreadsheets with a million rows or more.  He gave me a further suggestion.  My formula is done with the assumption that homozygous individuals have a 50% chance of survival, but I can change rand()>=0.5 to rand()>=0.3 if they have a 70% chance of survival (or a 30% chance of dying). I could adjust that number to match whatever a known survival rate is.  I loved this suggestion, although I'm not sure if my students will get that far.  I don't plan to share my formula with them, since I want them to figure out a way to model heterozygote advantage themselves.

Fatal recessive and small population size should be a little easier for them to figure out how to model. I may offer some incentives to encourage them to stick with determining how to model the heterozygote advantage.

I only made a few changes to the document by Brittany Franckowiak and it's here. Also, here is the sample Google Sheet I was working on.  In my sample, I added an additional tab to show heterozygote advantage, but I will have my students make a copy of their original sheet and then modify it for different scenarios.  Keeping it all in one sheet makes A LOT of rows to update every time someone makes a change and can be slow going.