Tag Archives: geek

Downloadable content Excel Random

Collatz Conjecture – An Excel model

The other day I was leafing through ‘Professor Stewart’s Hoard of Mathematical Treasures‘ whilst waiting for the bath to run and started reading about the Collatz Conjecture, a theory which states that, if you start with any number and apply the following logic to it:-

If the number is even, divide it by 2


If the number is odd, multiply it by 3 and add 1

…and continue to do this for each resulting number, you will eventually always get to number 1.  The theory, known also as The Ulam Conjecture, The Syracuse Problem and some others hasn’t as yet been proved, and still stumps mathematicians to this day.

I didn’t want to prove/disprove it, I just wanted to play with it a little, so I build this little plaything for fun.  Download it and have a go.  It takes any figure you enter and runs the process above on it and its results until it inevitably reaches one.  I put the sheet together using some pretty simple VBA, as well as using some conditional formatting and other tricks here and there.  The whole sheet is unprotected so you can have a look at how things work.  I added the chart for no reason other than showing the pattern of numbers – smoothing has been added to the line to make it look more pretty but other than that it’s no use other than to fill up some space on the sheet.

I find it really interesting that some initial ‘seeds’, if you like, can create some pretty wild reactions.  Look at what happens to 11, for example:-

This tool/model won’t solve the problem, by the way.  Every number you can possibly type into Excel will always return 1 – it’s still possible however that there is some huge number out here that doesn’t..