Skip main navigation

Addressing transmission dynamics with numbers

In this article Jakob Zinsstag explains the principles of transmission dynamics of infectious diseases using a spreadsheet.
© University of Basel

What are the principles governing the transmission dynamics of infectious diseases? They are best understood if we exemplify them with numbers and fill those in to the spreadsheet of a table calculator program.

You can generate your own spreadsheet by following the steps below. Alternatively, you can download the spreadsheet template that we provided for you. Either view it with your own spreadsheet calculator programme or upload it to a free viewer like this. (If you calculate in this free online app, be aware that when you have a lot of formulas and calculations going on in your sheet, a change of variables may need up to 15 seconds to affect all the relevant cells).
You can create your own sheet with this online app, too.

  • Start by creating the first row of variable names: T (for Time),
    S (for Susceptibles), I (for Infectious), R (for Recovered), beta (for contact constant (beta)), mu (for birth and mortality rate (mu)) and recovery (for recovery constant (delta)).

  • In row two, insert the following data for each variable:

T = 0
S = 1000
I = 1
R = 0
beta ((beta))= 0.001
mu ((mu))= 0.05
recovery ((delta))= 0.1

Example of a chart First two rows of the spreadsheet

  • In row three, write the next time step for (S_{(t+1)}), (I_{(t+1)}) and (R_{(t+1)}) using a variation of the formulas from the previous video step. Important: in these formulas we show how to calculate S, R and I within a population. To calculate these values for the following year, you have to add the values of S, R or I of the the previous year to the formula as shown here:

$$S_{(t+1)} = S_t + mu (S_t + I_t + R_t) – beta I_tS_t – mu S_t$$ $$I_{(t+1)} = I_t + beta I_tS_t – mu I_t – delta I_t$$ $$R_{(t+1)} = R_t + delta I_t – mu R_t$$

Variation of the formula

When referring to a cell that always stays the same (in this case: (mu), (beta), or (delta)), we use the the convention of the table calculator program putting $ signs in front of the cell letter and before the cell number, ie $E$2 for beta ((beta)). This is done because when you are copying the formulas to the different years, the spreadsheet will automatically change the target cells for S, R and I but refers always to the same cells for (beta), (mu) and (delta).

  • Copy the formula for 40 time steps and then plot (S), (I) and (R) against (T).

What kind of dynamic do you observe? What are the shortfalls of this approach?

You find a solution to this exercise in the following step.

© University of Basel
This article is from the free online

One Health: Connecting Humans, Animals and the Environment

Created by
FutureLearn - Learning For Life

Our purpose is to transform access to education.

We offer a diverse selection of courses from leading universities and cultural institutions from around the world. These are delivered one step at a time, and are accessible on mobile, tablet and desktop, so you can fit learning around your life.

We believe learning should be an enjoyable, social experience, so our courses offer the opportunity to discuss what you’re learning with others as you go, helping you make fresh discoveries and form new ideas.
You can unlock new opportunities with unlimited access to hundreds of online short courses for a year by subscribing to our Unlimited package. Build your knowledge with top universities and organisations.

Learn more about how FutureLearn is transforming access to education