Blog

The Blog is a collection of News, Blog posts, technical tips and tricks and other writings from my sphere of interest.
- Esben Jannik Bjerrum

Jan

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[1]. 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.

Chaos in a Spreadsheet

Spreadsheet with interactive graphs for experimenting with the chaotic behavior of the Logistic Equation

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

Have fun

Esben Jannik Bjerrum

Esben Jannik Bjerrum

CEO, 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

[1] May, Robert M. 1976. “Simple mathematical models with very complicated dynamics.” Nature 261(5560):459-467.


Comment

  1. john
    October 13, 2016 at 22:39 Reply

    the images don’t show up

Leave a Reply

Your email address will not be published. Required fields are marked *