A wee-bit of Math Geekery–Modeling Populations with Spreadsheets

I started teaching in the late 70′s but even then I was looking for math applications in Biology.  The BSCS Green version textbook had (and still has, I imagine) a very rudimentary exercise that introduced students to exponential models of population growth–we called it the sparrow lab.


Sparrow from Andreas Solberg's Flickr Photostream

The exercise introduces some of the main points of developing a model—deciding on your assumptions/simplifications, approximating but simplifying real world conditions and introducing the limitations of models, while introducing the power of models.  Secondarily, this lab provided for most students their first introduction to semi-log plots.  If you are not familiar with this exercise it doesn’t take long to explain or to model using a spreadsheet.


  • An island with unlimited resources and no limiting factors
  • Introduce 10 sparrows to the island:  1/2 male and 1/2 female
  • Each year, each pair of sparrow produce 10 offspring
  • All offspring survive to reproduce the next year
  • However, the parents all die before the next year (every sparrow reproduces and then dies)
  • No new sparrows immigrate to or emmigrate away from the island

It does not take long tease out these assumptions from a discussion with students and to write these on your board.  Note that the assumptions about births tend to balance out the survival assumption.  The students are then instructed to calculate how many birds will be alive on the island at the end of each year–for the next ten years.   Back before computers or calculators this calculation along with the graphing took the rest of the hour.  Scaling the graphs was particularly difficult.  When confronted with their inaccurate scales some of my students used to respond by taping 6 or 7 sheets of graph paper together.  Describing how to set up and plot on semi-log paper was another difficult challenge.  Today, with spreadsheets, this exercise seems almost trivial but it is not at all.  The exercise introduces a way of thinking about biological problems mathematically and helps to build a more intuitive sense of exponential processes that are fundamental to biology specifically and life skills in general.  Likewise, as trivial as this exercise may seem you’ll be amazed at the range of approaches that your students will take as they tackle this challenge.  Once you try this, I think you’ll agree it is time well spent.

After the discussions of our assumptions, as a class we sketch out how we might create a spreadsheet that would model the hypothetical sparrow population.  Generally, up on the board I sketch out a spreadsheet (don’t demonstrate this on an actual spreadsheet, just yet.)

We usually start something like this:

First we decide our labels:

year sparrows pairs offspring

(I usually prompt them for the “pairs” label–left over from the days when we did this by hand.  While it is not necessary to calculate “pairs” it slightly simplifies the offspring calculation.)

Next we simply calculate the first two rows in our collective heads and fill the rows in during a class discussion.

year sparrows pairs offspring
0 10 5 50
1 50 25 250

The conversation usually goes something like this:

  • “So, in year zero, how many birds do we put on the island?”
    • “10″ (I write down the 10 on the board.)
  • “How many pairs is that”"
    • “5″ (I fill in the 5.)
  • “How many offspring are produced?”
    • “50″  (You’d be surprised how often the student stumble here for a minute, remember it is all in their head at this point.)
  • “How many birds start in year 1?” (Again, the students sometimes stumble here but they get it pretty quickly.)
    • “50″ (I start and finish out the second row.)

At this point then I simply suggest that they create a spreadsheet that calculates and correctly graphs the hypothetical sparrow population on the island for 10 years.  In recent years, all of my students have had some sort of spreadsheet introduction but they’ve seldom had to create their own from scratch.  I provide very little guidance at this point.  It is important that each student has a chance to make missteps while constructing this spreadsheet–recognizing and correcting these errors are when valuable learning takes place.  Most often the first student constructed sheets have some fundamental error.  I use probing questions to help the students recognize these errors but generally let the students propose their own solutions.  What I’m trying to achieve is a mindset in the students whereby they propose possible solutions, enter them in the spreadsheet and then use the results from the spreadsheet calculations to evaluate their original proposed solution–sounds a bit like the essence of scientific thinking, eh?  As simple as this particular exercise is there are still a number of students in my classes that struggle a bit with this.  In fact, I’ve had teachers in workshops struggle a bit at this point as well.  It’s for this reason that it is important to not provide to much direct instruction in this exercise–it is accessible enough that the students can struggle a bit but still succeed.  This success is key to building the tenacity needed to solve problems and the  skill sets needed later in this modeling exercise.

Here’s some of the common problems.  Remember to use questions to the students to help them see these problems:

  1. Students generally just fill in the numbers from the board and fail to create formulas in the cells.
  2. Students copy cells incorrectly.
  3. Later, students fail to recognize fixed references vs. relative cell references.
  4. They have a difficult time evaluating their graph.

At this point you should create your own spreadsheet model.  In my experience, I’ve found that I can better teach the modeling process if I create the sheets from scratch myself.  Like doing any lab procedure you need that experience to teach it.  Once you’ve created your own, you can compare your spreadsheet to the following spreadsheet.  Since it is a collaborative spreadsheet you can check out the formulas in the different cells if you’d like.

You can find it here, if the actual spreadsheet isn’t showing up:

I ask the students to graph the model using a scatter plot and graph the model with the “y” axis logged.




So, what are questions that you’d want ask your students as they built this model? What are questions you might ask about the two types of graphs?  Please feel free to contribute some suggestions or questions in the comments. This is just the first installment of an multi-day lesson on population modeling and where it can take you.  I’ll cover more in a later post.