1 / 28

Modeling Time Dependent Systems in MS Excel

Modeling Time Dependent Systems in MS Excel. Jake Blanchard University of Wisconsin – Madison blanchard@engr.wisc.edu. Outline. Sample problems Solution techniques for Differential Equations Cellular Automata exercises. Files. The following files can be downloaded from my web site

Download Presentation

Modeling Time Dependent Systems in MS Excel

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related