Modeling Time Dependent Systems in MS Excel

1. Modeling Time Dependent Systems in MS Excel Jake Blanchard University of Wisconsin – Madison blanchard@engr.wisc.edu

2. Outline • Sample problems • Solution techniques for Differential Equations • Cellular Automata • exercises

3. Files • The following files can be downloaded from my web site • These powerpoint slides • Word Doc on Differential Equations and Cellular Automata • Spreadsheet on Differential Equations • Spreadsheet on Cellular Automata • http://silver.neep.wisc.edu/~jake/workshop

4. Some Sample Problems • Filling a Water Tank • Radioactive Decay • Carbon Dating • Terminal Velocity • Population Growth • Worm Population Growth

5. The water tank Qin A=area of tank a=effective area of orifice

6. Radioactive Decay

7. Carbon Dating • All living organisms have a fixed ratio of radioactive carbon to stable carbon • After they die, the ratio changes as the radioactive carbon decays • This can be used to determine the age of formerly living things • Procedure: determine initial activity, guess age, solve DE to determine current activity, update guess for age until activity matches measured activity

8. Terminal Velocity • Falling objects reach a terminal velocity when drag forces match gravity

9. Population Growth Growth Rate Reduction Due to Overcrowding

10. Procedure for Solving DE’s y h time

11. 4th Order Runge Kutta Scheme

12. These must match VBA routine Function f(t, y) r = [h10].Value lambda = [h12].Value f = r - lambda * y End Function Grab value from worksheet Calls f(t,y) from Module 6 Function rk(h, t, y) k1 = h * Module6.f(t, y) k2 = h * Module6.f(t + h / 2, y + k1 / 2) k3 = h * Module6.f(t + h / 2, y + k2 / 2) k4 = h * Module6.f(t + h, y + k3) rk = y + (k1 + 2 * (k2 + k3) + k4) / 6 End Function

13. Simple Worksheet

14. Another Approach • Have macro carry out several steps and write solution back to sheet • Can add button on sheet to run macro • View/Toolbars/Forms

15. The Calling Routine Sub rungekutta_first() Range("b13", Range("c13").End(xlDown)).Clear [b13].Select steps = [g4].Value tnot = [g5].Value tend = [g6].Value Yo = [g7].Value h = (tend - tnot) / steps t = 0 y = Yo For i = 1 To steps Call Module5.rk2(h, t, y, ynew) ActiveCell.Offset(i - 1, 0).Value = t ActiveCell.Offset(i - 1, 1).Value = y t = t + h y = ynew Next [a1].Select End Sub

16. Spreadsheet

17. Exercises • Radioactive Decay • PET scans are used more and more for studying brain activity • 18F produces positrons – it has a half-life of 1.8 hours • If I need 1 gram of 18F, at what rate must I produce it and how long will it take me to accumulate 1 gram? • How much of what I produce decays before I complete production?

18. Exercises • Water Tank • Suppose a tank has an area of 1 m and there is an outlet pipe with an area of 4 cm2 • At what rate must I fill the tank to achieve a height of 2 m in the tank? • Starting from scratch, how fast will the tank fill at this flow rate?

19. Exercises • Terminal Velocity • What is the terminal velocity of a 1 cm diameter hailstone? How about 2 cm? • What is the terminal velocity of a 1 cm steel ball? • What would be the terminal velocity of a 1 cm steel ball on the moon? • What would be the terminal velocity of a 1 cm steel ball in water?

20. Exercises • Carbon Dating • If an old sample has an activity of 2.1 and a new sample has an activity of 5.3, what is the age of the sample? • What is the age of a similar sample with an activity of 1.05?

21. Cellular Automata • These model a system by changing the state of a cell depending on the states of its nearest neighbors • A typical model uses a 2 by 2 grid of cells • Each cell has a representative value which indicates its state • The model then steps through time, updating all the states in each step • Stephen Wolfram (of Mathematica fame) just wrote a lengthy book on these

22. Example 1 - Population • Each cell is either a “1” (populated) or “0” (unpopulated) • If a populated cell has more than 5 neighbors (overcrowded) or less than 2 (isolated), it perishes • Others spawn children into empty neighboring cells (with assumed probability) http://classes.entom.wsu.edu/529/529Homework4.html

23. Example 2 - Fire • Cell values are: • 3-burning • 2-burnt • 1-re-grown • 0-susceptible • Susceptible cells with a burning neighbor burn in next step • Other values are reduced by 1 http://www.ecu.edu/si/cd/excel/tutorials/forestfire_model.html

24. Solution Approach • Models generally consist of series of “If” statements • These can be done right in cells, but formulas get convoluted • I prefer to use VBA macros

25. Sample Fire Macro For i = 2 To ncells - 1 For j = 2 To ncells - 1 If values(i, j) <> 0 Then nuvalues(i, j) = values(i, j) - 1 Else c = (values(i - 1, j - 1) - 3) c = c * (values(i, j - 1) - 3) c = c * (values(i + 1, j - 1) - 3) c = c * (values(i - 1, j) - 3) c = c * (values(i + 1, j) - 3) c = c * (values(i - 1, j + 1) - 3) c = c * (values(i, j + 1) - 3) c = c * (values(i + 1, j + 1) - 3) If c = 0 Then nuvalues(i, j) = 3 Else nuvalues(i, j) = 0 End If End If Next Next

26. Sample “Fire” Sheet Use conditional formatting to color cells

27. Exercises • Fire • What will a fire in the center do? • What will happen with random initial conditions? • What will a U-shaped fire do? • What if we start with this in the center?

28. Wrap-Up • Lots of interesting problems can be solved using either differential equations or cellular automata • If you would like help developing more of these, feel free to contact me