Search All of the Math Forum:
Views expressed in these public forums are not endorsed by
Drexel University or The Math Forum.



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


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(5000N), 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)



