Count magic bunnies in LibreOffice Calc

I love working in spreadsheets, and my favorite spreadsheet is LibreOffice Calc. I can use spreadsheets to do all kinds of work, as long as it involves a calculation.

One power feature of modern spreadsheets is “AutoFill.” That’s where you can enter a calculation in a cell, then click on it, and use the little “drag handle” (in the lower right of the cell) to extend the calculation to adjacent cells. Spreadsheets have had a “translate calculations to other cells” feature since VisiCalc, the first desktop spreadsheet – but the “AutoFill” feature makes this easier and more transparent.

Let’s do a simple calculation to demonstrate how AutoFill works.

The magic bunny

Imagine an enchanted forest, which happens to have zero bunnies. Then one day, a magic bunny moves in. Rabbits breed quickly, but especially so for the magic breed of bunny. The magic bunny is already pregnant – it is born pregnant – and all its children will also be born pregnant. The bunny produces a new generation of more magical bunnies once a year.

Let’s refer to the year before the bunny moved in as “year zero,” or “iteration zero,” when there were no bunnies in the forest. A year later, we’re at “year one” or “iteration one,” with our first bunny.

The population of the magic bunny grows like this: A baby bunny grows into an adult bunny after just one year. An adult bunny will continue to the next generation, but will produce another baby bunny of its own. We can track this iteration of bunnies by using the letter b for a baby bunny and the letter A for an adult bunny. In “year zero,” there’s nothing, but in “year 1” we have a baby bunny (b). Then the population grows like this:

1. b
2. A
3. A b
4. A b A
5. A b A A b

… and so on.

Counting bunnies in a spreadsheet

As you can see, the bunny population grows quickly. In just a few years, the forest will quickly be filled with magic bunnies. How many bunnies will we have after 5, 10, or 20 years?

Italian mathematician Fibonacci modeled this population growth using an equation. Looking at iteration, the number of bunnies in any generation is the sum of the two previous generations. In other words:

Fib(n) = Fib(n−1) + Fib(n−2)

… where Fib(0) = 0 and Fib(1) = 1.

This is pretty easy to calculate in a spreadsheet. Open LibreOffice Calc can enter the first two iterations: 0 and 1. Let’s create the iteration or n in column A, and the count or Fib(n) in column B.

For the first two iterations: year zero had 0 bunnies; year one had 1 bunny. Enter 0 in cell A2 and 0 in B2 for year zero, and enter 1 in cell A3 and 1 in B3 for year one.

With these values, we can enter the Fibonacci formula for the next year. Because the count in each successive year is the sum of the two previous years, we can enter this calculation in cell B4:

=B2+B3

Now the magic happens

Having entered 0 and 1 in column A to represent year zero and year one, we can let AutoFill do the rest of the work for us. With your mouse, highlight cells A2 and A3 by clicking on A2 and keep holding down the mouse button as you move the mouse into cell A3.

Do you see the heavy “dot” in the lower right corner of A3? That’s the “grab handle” that you can use to extend this sequence of two cells into adjacent cells. Drag the “dot” down into the next few cells until you fill 20 years. The AutoFill feature will detect the step between 0 and 1 is 1, and increment each successive year by 1.

Now click in cell A4 where we entered the Fibonacci calculation. Drag the “grab handle” downwards and let AutoFill continue the calculation through year 20. In each successive year, AutoFill will translate the calculation so that each new calculation is the sum of the two cells above it. That’s how AutoFill makes it easy!

And with that, we see that after 20 iterations, the forest will have 6,765 magic bunnies. That’s a lot of bunnies! Over 6,000 bunnies in 20 years represents a very fast population growth, and demonstrates how the Fibonacci sequence can add up quickly. Yet the calculation was made simple using AutoFill and LibreOffice Calc.

This article is adapted from Count magical bunnies with LibreOffice Calc by Jim Hall, and is republished here with the author’s permission.