Modeling Vampires
Here is a pdf version of the lesson.
This is a first model lesson for students. They should gain several things by the end of this lesson.
- Basic Excel skills
- entering data
- naming cells
- constructing and entering formulas
- creating charts from tabular data in Excel
- Basic understanding of what a model is and is not.
- "all models are wrong, some models are useful" George Box, 1979
- A computer model uses the computer as a tool to to represent something else. This example will model the growth of a vampire population on earth...
- Describe a line graph of exponential growth and make predictions.
We will use Excel to create a model to show what would happen if vampires exist. Before we begin, we must realize that models are never a perfect representation of real life - or vampire life for that matter - but they can be useful. To create our model we need to agree on some rules our vampires will follow in this limited world. We will call these rules assumptions.
Let's agree that...
- Vampires need to bite one new person a week to survive.
- If a vampire bites someone that person becomes a vampire.
Open Microsoft Excel. This is where we will create our vampire math model.
- On your new worksheet label columns A, B, and C as follows. Label column A as “Week” in cell A1, column B as “Number of People Bitten” in cell B1, and column C as “Total Number of Vampires” in cell C1. (Hint: Double-click on the top of the vertical line between columns to fit column widths to your headings.)
- Week 0 is what we will name the week the first vampire appears. In week 0 our vampire has not had a chance to bite anyone so under Number of People Bitten we type 0. That gives a total of 1 vampire for Week 0.
- Week 1 is the first week our vampire has a chance to bite anyone. In Week 1 our vampire bites 1 person. That makes our Total Number of Vampires 2.
- Now in Week 2 we start off with 2 vampires. If they each bite one person the Number of People Bitten will be 2. If we add those 2 new vampires to the 2 vampires that bit them we now have 4 vampires at large.
- In Week 3 we start off with 4 vampires. If they each bite one person the Number of People Bitten will be 4. If we add those 4 new vampires to the 4 vampires that bit them we now have 8 vampires on the prowl.
- Save your worksheet as yourname-vamp1 in your My Documents Folder.
- What to do: Using this Excel worksheet you started, continue the worksheet and fill in the data through Week 8, using the following basic steps.
- If there were 4 vampires at the end of Week 2, how many people would get bitten in Week 3? (4) And how many vampires would that make, total? (8)
- How many people would those vampires bite in Week 4, creating how many more of their kind? Carry it through to the end of Week 8. How many bloodsuckers do you end up with?
Graph Those Vamps!
Select your data in Column C, go to Insert, and choose Chart. Using Chart Wizard, select a type of chart that shows data over time. Normally you would think that is a line chart but not in Excel. In Excel, you need to choose the XY Scatter Chart. The chart here is one example.
- In order to select this type of chart you need to first select the data. Use the control key to select the Weeks column and the Total Vampires column.
- Now go to Insert > Chart > XY Scatter.
- Click Finish.
- Move your chart to the right side of your worksheet, so you can look at it side-by-side with your data. Create a graph to get a vivid picture of the vampire population boom.
- What kind of trend does your chart show? Where might the pattern go from here?
Formula, Bloody Formula
Now you get Excel to count seven more months worth of vampires for you -- in mere minutes!
- Look again at your statistics through week 8. In math terms, what is the relationship between vampires at the end of one week and the number of people bitten the next?
- What pattern do you see from week to week in the number of people bitten? What about the week-to-week vampire numbers?
- Now create a formula for Columns B and C in Week 9 (cells B11 and C11) that will extend the pattern.
- Check your formula to be sure that the pattern continues.
- You can use Excel's AutoFill feature to complete your worksheet. For Column A, highlight cells A9 and A10, point to the "handle" in the lower right corner of A10, and drag it through A37.
- Fill in Column B by using AutoFill to drag the formula through week 35 (cell B37). Fill Column C by dragging the formula in C10 down through C37.
- Format your vampire stats by selecting Columns B and C, going to Format, choosing Cells, and selecting Numbers on the list. Type in 0 decimal places, and select Use 1000 Separator.
- Save your work.
- How many vamps are there at the end of Week 35? If your formulas are correct, you should show 34,359,738,368. That’s 34 billion, 359 million, 738 thousand, 368 vampires!
- Make a new XY scatter chart with all your data and put this into a new sheet.
Vampire Reality Check
So how does your vampire census match up against the actual world population? Here's how to find out. Check your worldwide vampire census against the real thing.
- Launch a web browser, and go to http://www.census.gov/cgi-bin/ipc/popclockw the U.S. Census Bureau's World POPClock. This gives an up-to-the-second projected count of the world's human population, based on actual numbers plus estimated births and deaths.
- What number is showing in bold type? It should be about six and a half billion, give or take a few million. Select this number, go back to your Excel worksheet, and paste it into cell C38.
- In cell D38, type in the label People on Earth.
- Save and print your sheet.
- How many of the world's people are actually vampires? At which point in your chart did the whole population of Earth become vampires? Could this be true?
- Congratulations! You have just constructed what mathematicians call a "proof by contradiction."
Vampires Display
Now that you’ve got your data, format the information in a cool way. Play with fonts, colors, borders, and clipart to make your data chart and graph of all the data really tell a story.
You can look at examples of a finished product at http://www.plaidcircuitry.com/excel/vampire.pdf
This activity is a modified version of a lesson found here.
http://www.microsoft.com/education/default.asp?ID=vampire.More about modeling...
More Excel Lessons...
- Candy Lesson
- Fun with If and Then
- Interactive Crossword Puzzle of Excel Terms