Rabbits, Fibonacci and Schematiq Published 3rd Feb 2016

What do breeding rabbits and Schematiq have in common?

I hear you ask, or maybe you didn’t.

Good question either way - and it begins with considering how rabbits ‘breed’ (yes, it’s that kind of article), and how this ties in with a famous sequence in mathematics. Suppose that we start with two rabbits and, after a year they give birth to two baby rabbits. Let’s count heads – we have

YEAR 1: 1 pair of adult rabbits, and 1 pair of baby rabbits

Next year the baby rabbits have grown, and the original pair have given birth to two more:

YEAR 2: 2 pairs of adult rabbits, and 1 pair of baby rabbits,

On to next year, and both pairs of adult rabbits have each given birth to two baby rabbits each; meanwhile the baby rabbits have grown up:

YEAR 3: 3 pairs of adult rabbits, and 2 pairs of baby rabbits.

Continuing on, next year we expect:

YEAR 4: 5 pairs of adult rabbits, and 3 pairs of baby rabbits.

So: how many rabbits do we have in our garden if we leave these breeding year after year? This is where the Fibonacci sequence comes in:

1*,1,2,3,5,8,13,21,…

(*the adult rabbits had to be babies at some point!) The next number in this sequence is constructed by adding together the two previous numbers, so 21 = 13 + 8, and so on.

So how many rabbits can I expect?

In a given year, the number of pairs of adult rabbits will be increased by the number of baby rabbits the previous year. But, this is exactly the number of pairs of adult rabbits the year before, which has given birth to them.

So the number of pairs of adult rabbits in year 3, is the number of pairs of adult rabbits in year 2 PLUS the number of pairs of adult rabbits in year 1. This is why the number of rabbits matches our Fibonacci sequence exactly! Let’s use F(n) to represent the number of pairs of adult rabbits in a given year n, so

F(n) = F(n-1) + F(n-2).

In year 4, the number of pairs of adult rabbits is 3 (from year 3) plus 2 (from year 2).

That’s getting to be a lot of rabbits in our garden, sounds like we need a spreadsheet! Fortunately we have Schematiq to model this explosion of rabbit populations. We need some way to create a table which stores the number of rabbits in previous years, and from which we can calculate the number of rabbits in the next year - exactly where the tbl.CalculateColumn comes in.

First, we create a table listing the years in one column

B3: “=tbl.Create(rng.Sequence(10,0),"Year")”

Let’s leave the Fibonacci formula for now, and look at the calculate column formula:

D3: “=tbl.CalculateColumn(B3,{"[-1]","[-2]"},C3,"Number of Rabbits!")”

This uses the table B3, and a formula given in cell C3 to produce a new column “Number of Rabbits!”. The negative numbers tell the formula to use the previous two rows to calculate the next one. 

undefined

We would like the formula in cell C3 to give the sum of the previous two rows. BUT: for the first two rows (for which these previous rows do not exist) the values provided will always be 0, so we need to make sure that initial values are set to 1 or more. That is to say, our formula must add two cells together, but return a value 1, if it’s the first two cells:

C3: “(x, y) => MAX(x, 1) + y”

(We could also do this by adding a check on the value of $Row)

undefined

Without using Schematiq, the same operation would require copying and pasting a formula down every cell in the table.

So, we have a table of the Fibonacci numbers, representing the pairs of adult rabbits in a given year. An interesting fact about these numbers is that the division of one of these numbers by the previous one, gets closer and closer towards a specific number called the golden ratio:

2/1 = 2

3/2 = 1.5

5/3 = 1.666…

8/5 = 1.6…

13/8 = 1.625…

The first few digits of this golden ratio is 1.61803….Let’s see how close to this we can get:

C6: “(x, y) => x/ MAX(y, 1)” (being careful not to divide by zero on the first row.

D6: “=tbl.CalculateColumn(D3,{"Number of Rabbits!","Number of Rabbits![-1]"},C6,"Golden ratio approximation")”.

undefined

After 10 rows, we have correctly matched two decimal places of the golden ratio. And by the simplicity of Schematiq, to get more rows we only have to change the length of the sequence in cell B3 – no copying and pasting required!

More rabbits please!

To conclude, here is a very simple generalisation, which shows how easily this simply Schematiq sheet can be adapted. The formula is only slightly more complex. Let’s consider a new sequence where each new term is generated from the previous three terms:

F(n) = F(n-1) + F(n-2) +F(n-3), the first few numbers are

0, 0, 1, 1, 2, 4, 7, 13, 24, 44, 81, 149…

These are called the tribonacci numbers and consecutive ratios tend towards 1.83929…Let’s calculate this, and output the best approximation (the final row of the table)

C3: “(x, y, z) => MAX(x, 1) + y + z”

D3: “=tbl.CalculateColumn(B3,{"[-1]","[-2]","[-3]"},C3,"Number of Rabbits!")”

C9: “=tbl.GetValue(D6,A3,"Golden ratio approximation")”

Where for ease we set the number of rows of the table in cell A3. This allows us to change the size of the table easily!

undefined

With small modifications these sheets can be used for a generalised Fibonacci sequence:

F(n) = a*F(n-1) + b*F(n-2), where the values a and b can be any values we choose. The simplicity here is key: any of these requires only a small modification, and no need to copy and paste formulas down columns – tbl.CalculateColumn does all the hard work for us!

No rabbits were harmed during the writing of this blog post. Nor do we assume or enforce that rabbits breed at this rate. We chose rabbits purely to highlight the mathematical cuteness of Fibonacci numbers.

 

blog comments powered by Disqus
Writing some notes

Blog & News

Find out what we're talking about on the blog