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)?