Excel Level 2: Susceptible Infected Recovered Model
Excel Level 2: Susceptible Infected Recovered (SIR) Model
This lesson will guide the students to build a Susceptible, Infected, Recovered (SIR) Model of the spread of a disease, by finding and graphing the number of susceptible, infected, and recovered people in the model over time.
- To build on the basic Excel skills from Lesson 1
- Introduce concepts of epidemiology
- Some familiarity with Excel.
- Basic knowledge of algebra (use of variables and functions)
The teacher will need to be familiar with some extra features of Excel beyond the most widely known. The Excel Tutorial can familiarize the teacher with these features:
- Naming Cells
- Slider Bars
You can also see the finished model here:
- The Excel tutorial could be helpful for students but is not required
- Whiteboard and markers
- An overhead projector is recommended
- A computer for the teacher
- One computer for each student or pair of students
- Each computer should be equipped with Microsoft Excel (preferably Excel 2004)
No safety issues in this lesson
Introduction to the SIR Model (5 min)
- At your School - if one person is sick, how long will it take for the disease to spread through the whole school?
- What do we need to know in order to find that out? (write on the board)
- How many people are at the school? (Total Number of People)
- TOTAL = (Students provide)
- A good number might be around 1000.
- How many people are sick at first? (Infected)
- INITIAL INFECTED = (Students provide)
- A good number would probably be 1.
- How many aren't sick right now, but could get sick? (Susceptible)
- SUSCEPTIBLE = TOTAL - INFECTED
- In other words, out of the total population of the school, whoever isn't sick must be well and susceptible to getting sick.
Start building your model!
- Create 3 columns
- A = "Time"
- B = "Susceptible"
- C = "Infected"
- In Column G enter the names of the constants. In column H, write the constants themselves.(Don't forget to name them!)
- Enter values for Total and Initial Infected
- A2 = 0 (Initial Value of Time)
- B2 = TOTAL - INFECTED
- C2 = INITIAL INFECTED
- What happens if we change the Initial Infected?
- More people are initially sick.
- What happens if we change the Total?
- More people in the school who could possibly get sick.
- Add scroll bars to Total and Initial Infected.
Physical Modeling and Measurement
The standard 50-minute lesson does not include physical modeling. If there is extra time, students can play the SIR game during this time (see attached material)
Build the full model (30 min)
- How do we find out how many people will get sick each day?
- To figure this out, we are first going to have to figure out how many possible interactions between infected and susceptible people there are (we don't care about interactions between infected people and other infected people or susceptible people and other susceptible people, because those won't change the number of infected or susceptible people). How do we find out how many possible interactions there are? Use this example to help students: Hold up two fingers on one hand and three on another. If you want to touch each finger on one hand (infected) to each finger on the other hand (well) once, how many times would you touch your fingers together? Answer: 3*2 = 6 times. In our model, it works the same way.
- How do we calculate the number of possible interactions between infected and susceptible people?
- Interactions between susceptible and infected
- Possible Interactions = S * I
- Actual Interactions = Possible * Interaction Rate
- Infectious Interactions = Actual * Infection Rate
- delta Infected = S * I * Interaction rate * Infection Rate
- Interaction rate = % people you interact with
- Infection rate = % interactions result in infection
- Equations are the same as before!
- Infected = Infected + delta Infected * delta Time
- Susceptible = Susceptible - delta Infected * delta Time
- Constants (name these!):
- Initial Infected (slider bar)
- Delta Time (slider bar)
- Interaction Rate (slider bar)
- Infection Rate (slider bar)
- Enter Time
- Use formulas in Susceptible and Infected Columns
- Add slider bars
- Demonstrate how they need to be converted to get a fraction
- Build a graph
Allow students to explore their models and make observations about the shape of the graph lines. Here are some example questions:
- Why do the graph lines level off at the end?
- (constant population, disease can't spread beyond the limit)
- What could be added to this model to make it more realistic?
- (vaccination, recovered become susceptible again, randomness)
- How could scientists use this model?
- (Study the affects of different diseases on a population, according to contagiousness)
With extra time, you can also build a recovered column:
- Adding Recovered
- delta Recovered = I Before * Recovery Rate
- Infected = I Before + (delta Infected - delta Recovered) * delta Time
- Recovered = R Before + delta Recovered * delta Time