May
25
2009

Using Spreadsheets to Introduce the Logistic Population Growth Model

This post is a continuation of exploring the use of spreadsheets in high school biology.  I’ve started with a rather obvious topic:  population growth.  What I present is only one possible scenario which is meant only as a starting point.  Two themes I hope are apparent as you read through these posts:  1.  I use questioning techniques to help the students connect to their previous knowledge while they are developing new understandings and 2.  I really work hard to have the patience to allow the students time to work out their own solutions on the spreadsheets with only a little intervention from me.  That’s the beauty of spreadsheets–they can quickly provide feedback to the students as to whether or not they’ve entered their formulas correctly or even if their proposed formulas work the way the student hoped.   In other words, making mistakes and fixing them is a critical part of these exercises.  Don’t cheat the students out of a learning opportunity by providing too much help/guidance.   In these posts I’ve suggested that you work out the spreadsheet yourself before checking out the embedded sheets.   In my experience, my mistakes help to inform my teaching as well.  I doubt that I’ve ever created an original spreadsheet model the first time from scratch that I didn’t subsequently correct or modify–that’s an essential part of the process.

Earlier posts in this series:

Sparrow Lab

Exponential Growth

At the end of the exponential growth post I mentioned that mathematical models can be additive–perhaps I should have said modular.  The exponential equation developed in the earlier sheet now serves as the core for more sophisticated models.

logistic

At this point with my students I enter a conversation that explores what they see in the real world.  Do populations continue to grow exponentially?  Why not?  What factors might limit population size?  Eventually, using guiding questions we follow a path that leads to a new concept:  carrying capacity.   At this point, with student input, I sketch a graph on the board that has the x-axis labeled time and the y-axis labeled population size.  I then draw a horizontal line across the top of the graph that I label carrying capacity.  I ask the student to do the same on a piece of paper and then challenge them to sketch a line that represents a population that grows exponentially at first but as the population size approaches the carrying capacity the population growth slows and the population size levels off.   Eventually, the class agrees that a likely scenario would be an S-shaped line, with an increasing slope early on, with a transition zone where the slope changes to a decreasing slope and an eventual leveling.

With the target in mind, I bring the class back to their earlier spreadsheet model of exponential growth that had two terms:  N and r.   I ask a number of question such as:  “Which of the two terms change as the exponential equation is recalculated”  “Which term is constant?”  “If we wanted to modify the exponential growth curve into the S-shaped curve what has to happen to r?” (no longer constant)   At this point I introduce a new variable to the work:  “K” which represents carrying capacity.  (Naturally, there is further discussion about carrying capacity in the real world and in the model.)

Now, for the hard part—having the students come up with the logistic expression themselves.  First I remind the students about the algebraic form of the exponential equation that they represented their earlier spreadsheet:

Nt = N(t-1) + r*N(t-1)

The discussion has already focused on the “r” term which is in the second expression.  I ask the questions such:  “What part of the graph is population growth maximal?  minimal?”  “How can we change ‘r’ to maximize growth? minimize growth?”  “Now if the spreadsheet has a constant value of ‘r’ how might we change that value during the calculations?”    At this point I will introduce the idea of adding another expression to the equation–the logistic.  “Is there some mathematical expression that we could add to this equation that maximizes ‘r’ early but minimizes ‘r’ in later generations?”  “Can you think of an expression that includes just the N variable and the K variable that can be multiplied times ‘r’ to fill the needs of the model?”  or  “Can you think of an expression that is approximately equal to “1″ when N (the population size) is small but approximately equal to “0″ when N approaches K in size?”  At this point I let the students “discover” this expression themselves.  I ask them to try out the expressions they think will work in their spreadsheet.  To evaluate their proposed expression put it in the spreadsheet and use the graph produced to evaluate whether the expression works as planned.

The first time I tried this, the students took most of an hour and went through quite a bit of frustration.  I’m not really sure why I thought they could “empirically” determine this expression or what I thought they’d get out of it but I realized part of the value of the exercise when all of a sudden, one of the girls jumped up and yelled “Yeaaaah, I’ve got it”.  I decided to not have her share her strategy with the others—but instead prompted them to keep trying.  Eventually the entire class came around to the logistic expression:  (K-N)/K    Definitely a powerful experience.  The students learn that they can solve seemingly impossible problems with hard work but they also learn how to think about mathematical models in of biology.  It’s fairly easy to discuss  now, the limitations and the power of the model.  BTW, that first student is now a professional biologist.

I hope that you try to create this spreadsheet yourself before you ask students to do so.  Here is an example of how the spreadsheet model might be formulated.

Link to the spreadsheet in case the embed feature is not working.

No Comments »

RSS feed for comments on this post. TrackBack URL


Leave a Reply

You must be logged in to post a comment.