Math Forum Discussions

Rex Boggs

Posts: 80
Registered: 12/6/04

Differential Equations with a Spreadsheet
Posted: Aug 20, 1996 7:22 AM

Plain Text

I am currently teaching first order differential equations with my 12
Maths C students, and heard about a nifty technique for solving
differential equations using a spreadsheet.
I have developed a spreadsheet to do this, but I am not entirely sure
that what I am doing is the best that can be done. I would appreciate
some advice.

Below are the first few rows of an Excel spreadsheet for solving a
differential equation -
dN/dt = .0001N(5000-N), which is supposed to be a mathematical model for
how a flu might spread through a population of 5000 people. I have no
idea if this model is appropriate.

Column A is time, in intervals of 0.01. Column B is the change in N -
essentially dN/dt * delta t. Column C is dN/dt from the differential
equation above. Column D adds the number of newly infected people to
the existing number to determine the current number of infected
people. Column E is the 'correct' answer found using calculus. I
included it as a check on the iterative technique using the spreadsheet.

It seems to me that the formula for D7 should be D7 = D6 + B7.
Unfortunately this results in a circular reference. I tried D7 = D6 +
(B6 + B8)/2 to sneak around this problem, but I still have a circular
reference.

The result that I get isn't too bad. When t = 14 (ie after 1400
iterations), the correct result is 900 infected folk, while the
numerical technique gives 887. Can anyone suggest a better spreadsheet
method (that isn't too difficult for high school students)?

Cheers

Rex

Here are some of the rows of the spreadsheet:

A B C D E F G

3 dt= 0.01
4 FORMULAS
5 T dN dN/dt N1 N2 Time A7=A6 + $B$3
6 0 0.00 0.50 1.00 1 dN B7=C7*$B$3
7 0.01 0.01 0.50 1.00 1.005 dN/dt C7=(differential equation)
8 0.02 0.01 0.50 1.01 1.010 N1 D7=D6 + B6
9 0.03 0.01 0.51 1.02 1.015 N2 E7=***see below***

E7==(1.0002*EXP(0.5*A7))/(0.0002*EXP(0.5*A7) + 1)