Chaos in Excel, Deliberately!
Yes, I deliberately put Chaos in Excel! In form of a simulation of the logistic equation which shows non-linear behaviour for certain values of the feedback constant;-) The Excel workbook is available for download and make it possible to play around with the feedback constant and hopefully get a better understanding of non-linear behaviour a.k.a. Chaos. Have fun 😉
The Logistic Equation
It seems my life is full of the logistic equation in one form or the other at the moment, from pH calculations, over multiple linear classification learners to neural networks and docking functions. The function was named by Pierre François Verhulst, who studied it in relation to population growth from 1844 forward.
The Logistic Map
The first derivative of the logistic function is used in the logistic map (1), which is the basis of the simulation in the Excel sheet. It was used for modelling of populations, where the next generation is directly proportional to the previous r*Xn, but if the population gets too large the (1- Xn) term will diminish the next generation due to resource depletion. For values of r between the population will die for obvious reasons, for values between 1 and 3 the population will sooner or later reach a stable population at (r-1)/r eventually with fluctuations around the value for some time. Above three the equation starts to fluctuate between one or more values and above 3.5 it seems to vary randomly. Its just not random, but non-linear, so there’s hidden order in the seemingly wild fluctuations. This last behavior was not acknowledged before it was used by the biologist, Robert May, in a paper from 1976. With r above 4, the population will sooner or later reach a size of 0, where it becomes extinct.
I find it very fascinating that such a simple equation can hide such interesting dynamics, and I keep seeing chaos and non-linearity around me, from biology and disease over stock-markets and economic systems to climate systems.
In the Excel sheet (Logistic Equation.xlsm), its possible to tune initial population and the value of r, and see what happens with the dynamics of the feedback system in a couple of different diagrams. The simulation works without macros, although the animated graphs are dependent on VBA macros. In libreoffice Calc the simulation and sliders works, but not the buttons.
Download: Logistic Equation.xlsm
Esben Jannik Bjerrum
Esben is CEO of Wildcard Pharmaceutical Consulting, who amongst other services also makes custom Excel templates for data management and analysis in research laboratories. http://www.wildcardconsulting.dk/excel-template-consultant
 May, Robert M. 1976. “Simple mathematical models with very complicated dynamics.” Nature 261(5560):459-467.