Embedded Files
This worksheet is a guide to getting started with iterative numerical modeling. You can consider using this as a base for developing classroom materials. Navigate with the table of contents here:
Introduction: What is iterative numerical modeling?
Introduction: What is iterative numerical modeling?
The core idea behind numerical modeling is that if a time-dependent problem is too difficult to solve explicitly using calculus, it is still possible to obtain a fairly good approximate solution by calculating how each property of the system changes when taking small time steps.
For example, consider the problem of an object falling under the influence of air resistance. Most high school students would not have learned differential equations in calculus when they are first introduced to air resistance, so even if they know ΣF = - mg - bv, they would not be able to solve the differential equations to obtain the exponential decay relationship.
However, from ΣF = - mg - bv, it is possible to calculate the acceleration! So a simple numerical analysis can be done with small time steps:
Looking at this, we see that every step is done with a simple algebraic calculation:
For each time step, the current position and velocity are used to calculate the current net force, which is then used to calculate the current acceleration.
This position, velocity, and acceleration are then used to calculate the next position, velocity and acceleration.
The process is then repeated for the next position, velocity and acceleration to obtain subsequent values.
This is known as an iterative method because it iterates the same calculations for every step, taking the values acquired from previous steps to calculate new steps.
Putting this into practice, the expected exponential decay curve can be obtained without any use of calculus!
Guided example
Guided example
For best results, you should have either Google Sheets or Microsoft Excel open (or some similar spreadsheet software) and follow the steps.
Learning through doing is one of the best ways to pick up a new skill!
When following the steps, don't just follow the formula blindly - you should try to understand what the formula actually means.
Step 1: Identifying the problem
Step 1: Identifying the problem
First, consider what physical phenomena you would like to model.
For this worksheet, we will be using a falling object as an example.
Subsequent steps will have three levels of instruction:
By default, the basic steps for new users will be shown.
If you are new to spreadsheets, you can start learning by following these steps for a simple object falling in a vacuum without air resistance.
If you have some prior experience with spreadsheets, click here...
If you are more experienced, follow the full steps for an object falling with air resistance.
It will also be recommended that you put constant parameters in a separate section.
More advanced users may want to try the following hidden steps...
Going deeper by considering two dimensional projectile motion with air resistance (and perhaps rocket propulsion?).
Broadening your experience by instead applying these steps to model the charging or discharging of a capacitor through a resistor.
Minimal guidance will be provided for these examples in this worksheet, but as a physics teacher or student, you should be able to recognise the expected outcome and check your model accordingly.
Once you have identified the phenomena, you need to identify the variables and constants involved.
For an object falling in a vacuum, these are:
Click to show answer:
The current time, t
The object's current position, x
The object's current velocity, v
The object's current acceleration, a
The gravitational acceleration constant, g
Some of these, such as time or acceleration, are hidden in the background! Don't forget that you need to account for every single parameter involved.
You might also want to consider:
The force acting on the object, F
The object's current mass, m
In this case, because the acceleration is constant, you can just ignore force and mass. However, if you want to get some practice with spreadsheets, it would be good practice to include this as well.
As for the case with air resistance...
For an object falling with air resistance, you will also need to consider the following parameters:
The drag coefficient, b
The force applied by air resistance, F = -bv
It also becomes especially important for you to keep track of the sign convention for direction. For example, if you take upwards as positive, then
The force exerted by gravity should be negative F = -mg
However, you should still use F = -bv for the force applied by air resistance. Since the object will be falling downwards, v will be negative, so F = -bv will be positive, which correctly identifies the direction of this force as upwards.
Finally, you need to list out how these variables are interconnected.
For an object falling in a vacuum, the relevant equations are:
Click to show answer:
Acceleration is constant. Taking upwards as positive,
a = -g
You probably already know the "correct" answer from a physics perspective: v = at = -gt, and x = ½ at² = -½ gt²
However, this is not how iterative numerical modeling should be done! In numerical modeling, you will very rarely have any equations that explicitly depend on time t.
Instead, the numerical model should calculate x, v and a at each time instance based on the previous set of values. You should use the following relationships for position and velocity:
v₂ = v₁ + a₁ Δt
x₂ = x₁ + v₁ Δt
Or more generally:
vₙ₊₁ = vₙ + aₙ Δt
xₙ₊₁ = xₙ + vₙ Δt
Basically, for every row, you will have the same value of acceleration a, and the value of v and x will depend on the previous row.
When considering air resistance...
Acceleration is no longer constant. Since there are two forces, you need to consider the net force now. Taking upwards as positive,
Fₙ = - m g - b vₙ
aₙ = Fₙ / m
You can also combine these two into a single step if you want: aₙ = - g - (b/m) vₙ
The appropriate expressions for velocity and position are same as that for in a vacuum:
vₙ₊₁ = vₙ + aₙ Δt
xₙ₊₁ = xₙ + vₙ Δt
So you can see that regardless of the type(s) of force(s) used, we end up using the same relationship to iterate on position and velocity! The only difference is in the calculation of acceleration.
Finally, for more advanced users...
If you are investigating two-dimensional motion -
You will need to set up twice as many variables: for the x direction and y direction
Make sure you keep track of these variables clearly!
As for the charging and discharging of a capacitor, consider a battery V₀ connected in series with a capacitor C and a resistor R:
V₀ = Q/C + IR, where V₀, C and R are constants.
Rearrange to make current the subject:
I = V₀/R - Q/RC
The two variables are the charge Q stored on the capacitor and current I in the circuit, and you can take the current to be the rate of change of Q.
Qₙ₊₁=Qₙ + IΔt
Step 2: Implementation in a spreadsheet software
Step 2: Implementation in a spreadsheet software
This worksheet will use Google Sheets. However, the steps would be the same for Microsoft Excel.
When typing in the formula, please pay attention to the layout in the examples provided. If you use a different layout in your own work, you will need to change the formula accordingly.
You should create one column for every variable identified. For example, for an object falling in vacuum:
Let's break down what these equations mean!
All equations must start with an equal sign "=".
This lets the software know that you are typing an equation for calculating values, rather than regular text.
Every cell has a reference coordinate: columns are A, B, C..., rows are 1,2,3...
Under the time column (A):
We put the initial value as 0, since we let time start from 0s.
Here, we are using a time step of Δt = 0.1s. So in cell A3, the formula instructs the software to take "the value in time A2, plus 0.1". Similarly, in cell A4, the software takes the value from A3 and adds 0.1.
Under the position column (B):
We take the initial value as 0. This is arbitrary. You can start from any initial position and velocity you want.
Cell B3 calculates the first iteration for position. The formula "=B2 + C2*(A3-A2)" instructs the software to:
Take the value from B2 (previous position)
Add the value from C2 (previous velocity) multiplied by A3-A2 (current time minus previous time).
What is happening here is simply the equation you identified previously: x₂ = x₁ + v₁ Δt
Finally, under the velocity column (C), the process is similar as position, just that you are taking v₂ = v₁ + a₁ Δt instead.
This might seem tedious and complicated! However, the power of spreadsheet software is that you only need to set up the equations for one row. After that, when you copy and paste it to another row, the equations will be duplicated, with the cell references updated! For example:
Note that this only happens for copy and paste - it does not work for cut and paste. When you cut and paste, it will paste the exact wording of the formula, without updating the cell references. Give it a try to see what that means.
You can also replicate this by clicking and dragging the corner of the selection, but this method is less flexible:
It is convenient and easy, but can you imagine having to click, hold, and drag for thousands of rows?
Click for additional info about relative cell referencing
In Google Sheets and Microsoft Excel, cell references are saved based on their relative position to the current cell.
For example, when you type "=A2 + 0.1" into cell A3, what gets copied and pasted is not "A2". Instead, it copies and paste "the cell above this current cell + 0.1".
So if you copy from cell A3 into cell D5, "=A2 + 0.1" becomes "=D4 + 0.1" because D4 is the cell just above D5.
What if I want to change a constant value, such as Δt or g? Do I have to redo the whole formula?
In the example above, we provided the most basic solution: manually typing in Δt = 0.1 and g = 9.81 into the formula.
If you want your setup to be more flexible, it is recommended that you have a separate cell for constants:
This affects column A (time) and column D (acceleration).
For column A, instead of A6 being "=A5 + 0.1", we could have "=A5 + B1" instead. you can see that B1 is the cell where the time step is stored.
But if you do that, when you copy and paste, it will automatically change B1 into B2, B3, and so on! How do we prevent that? By telling Google Sheets or Microsoft Excel that we want to use an absolute reference rather than a relative reference.
If you use the equation "=A5 + B$1", the "$" in front of "1" tells the software not to change the "1" when you copy and paste.
Similarly, for column D, instead of "-9.81", we'll use "=-B$2".
Give this a try!
For other cases, you can also put "$" in front of "B" as well, or any combination of placement of "$". A "$" in front of each letter or number in a reference means you will 'lock' that letter or number when you copy and paste the formula.
If you want to take this further with air resistance...
Previously, we identified the following relationships:
Fₙ = - m g - b vₙ
aₙ = Fₙ / m
vₙ₊₁ = vₙ + aₙ Δt
xₙ₊₁ = xₙ + vₙ Δt
We actually visualised this earlier:
Implementing this into a spreadsheet:
As we saw earlier, you just need to set up the first two rows, and then copy and paste the 2nd row to iterate further.
By the way, in this example, Drag, Weight, Net Force and Acceleration are set up as separate columns for clarity. As you get more advanced with modeling, you can consider compressing it into a single column for acceleration, e.g. the first row of acceleration being "= - $B$2 - ($B$3/$B$4)/C6".
And if you are working on 2D motion, capacitance, or other examples...
By this point, you should be able to work it out for yourself! If not, look at the example(s) provided below.
Results and discussion
Results and discussion
Using these simulations
Using these simulations
As you can see, setting up models to simulate real-world phenomena is not difficult!
Some examples are shown below:
If you want, you can save a copy of this spreadsheet for yourself if you open it in a new window, as shown.
This way, you can inspect the equations used, and how the simulation was set up.
Now, how would you use this? There are several ways:
It is easy enough to identify the exponentially-slowing behaviour when there is air resistance, but what does the graph look like if there is some initial velocity upwards? What if it starts from some extremely large speed faster than terminal velocity? This simulation lets you or your students explore these options in an intuitive manner!
Similarly, if there are other interesting phenomena that you usually have to describe only qualitatively, this is a great way to create manipulable visualisations!
You can also introduce these methods to your students, with a worksheet similar to this, as long as it's appropriately scaffolded for your class. Some students will be excited at being able to tackle problems beyond their current math skills.
These simulations are not limited to physics! For example, see the following presentation and accompanying spreadsheet on modeling the spread of COVID-19:
What else can we do with spreadsheets?
What else can we do with spreadsheets?
Your use of spreadsheets for simulations is only limited by your understanding of spreadsheet functions. Here are some ideas:
Adding slider bars for more intuitive manipulation (as compared to having to type in values when changing parameters)
Using the random number generator function "=rand()" to simulate stochastic phenomenon (e.g. in thermodynamics or nuclear physics)
"Drawing" diagrams in your visualisation
This folder contains some sample spreadsheets that might serve as inspiration:
https://drive.google.com/drive/u/1/folders/1lyc_Zl-9qY2eyyCxf6Uroj8WcbgNJGkO
You can also combine this with data analysis. For example, if you have some existing data, you can try manually fitting a model to the data.
Feedback and correspondence
Feedback and correspondence
Produced by Nicholas Wong
Last updated Oct 2022
Page updated
Google Sites
Report abuse