Page 1 of 6
58 At Right Angles | Vol. 3, No. 1, March 2014
The Fibonacci Puzzle
The Fibonacci puzzle was posed by Leonardo of Pisa, also known as
Fibonacci (hence the name of the sequence). The puzzle describes
the growth of an idealized rabbit population. A newly born pair of
rabbits comprising a male and a female rabbit is put in the field and
are able to mate at the age of one month. At the end of the second
month the female rabbit produces a pair of rabbits (again a male
and a female). Rabbits never die and every mating pair always
produces a new pair every month from the second month on. How
many pairs will there be in one year?
Let us try to find out the number of pairs of rabbits at the end of
every month starting from the first month. To begin with there is
one pair of rabbits. At the end of the first month, they mate, but
there is still only 1 pair. At the end of the second month the female
produces a new pair, so now there are 2 pairs of rabbits in the
Exploring
Fibonacci Numbers
using a spreadsheet
Jonaki Ghosh
techspace
Keywords: sequence, Fibonacci, golden ratio, investigation, Excel
In this article we are going to explore a very interesting
sequence of numbers known as the Fibonacci sequence.
The exploration of the sequence can lead to an
absorbing classroom activity for students at the middle
school and secondary school level. Students can explore
many patterns within the sequence using a spreadsheet
like MS Excel and the observations can lead to an
enriching discussion in the classroom.
Page 2 of 6
Vol. 3, No. 1, March 2014 | At Right Angles 59
field. At the end of the third month, the female of the original pair produces a second pair, making 3 pairs
altogether in the field. Remember that the second pair which was born at the end of the second month is
only able to mate at the end of the third month. At the end of the fourth month, the female of the original
pair has produced yet another new pair and the female born at the end of the second month produces her
first pair, making 5 pairs in all. This process continues. To obtain the number of pairs at the end of any
given month, say n, we need to add the number of pairs at the end of month n−1 and the number of pairs
at the end of month n−2.
Hence the Fibonacci sequence can be written in the form of the recurrence relation
Fn = Fn − 1 + Fn − 2
The first two terms of the sequence are 1 and 1. So if F1= F2= 1, the recurrence relation can also be written as
Fn + 2 = Fn + 1 + Fn .
Sometimes F0 is taken to be 0. Here are the first fifteen Fibonacci numbers: 1, 1, 2, 3, 5, 8, 13, 21, 34, 55, 89,
144, 233, 377, 610.
Can you guess how large the 50th Fibonacci number will be? One can continue the process of adding pairs
of consecutive terms to find the next term, but after a while it can get quite cumbersome. Let us take the
help of a spreadsheet to generate the Fibonacci sequence and find its 50th term.
Generating the Fibonacci sequence on Excel: The following steps will help you obtain the sequence on
Excel.
Step 1: The first step is to create a column of integers
from 1 to 50 (in column A). This may be achieved in
the following manner.
Click on cell A2 and enter 1. Then enter = A2 + 1
in cell A3 and drag cell A3 till A51. This will create a
column of numbers 1 to 50.
Step 2: Enter 1 in cells B2 and B3. In cell B4, enter =
B3 + B2 and press enter. A double click on the corner
of cell B4 will generate the Fibonacci sequence as
shown in Figure 1. As you scroll down the sequence
in column B, you will notice that the column width is
too small to accommodate the numbers. For example
the 40th Fibonacci number appears as 1.02E + 08.
This means that Excel has approximated the number
and the number is close to 1.02 × 108
. To get the
actual terms of the sequence beyond the 40th term,
the column width needs to be increased. This can be
done by taking the cursor to the end of the column
(were the columns are named as A, B, C etc) and
dragging it to the required width.
Note that the 50th Fibonacci number is 12586269025.
Clearly, this is a very fast growing sequence. See
Figure 2. Figure 1: Generating the Fibonacci sequence on Excel.
Page 3 of 6
60 At Right Angles | Vol. 3, No. 1, March 2014
Step 3: We can also draw the graph of the sequence. For this, select column B by clicking on B, go to Insert
on the toolbar, select chart and then line. See Figure 3.
As you already know,the first 15 Fibonacci numbers are 1, 1, 2, 3, 5, 8, 13, 21, 34, 55, 89, 144, 233,
377, 610. Let us see what happens when we take the ratios of consecutive terms Fn+1
Fn
of the Fibonacci
sequence. They are:
.......
Let us compute the ratios on Excel as follows. In cell C3 of
column C, enter = B3 / B2 and double click on the corner
of the cell. You will observe that after a certain number of
terms the ratios become steady at 1.618034.
A natural question now arises whether this value (that
is, 1.618034) will remain the same if we change the
initial values of the Fibonacci sequence (which are 1 and
1). Let us try to investigate by taking different starting
values. Change the values in cells B2 and B3 in the Excel
sheet to 4 and 7. Observe that the ratios of successive
terms still approach 1.618034 (shown on Figure 5).
You may investigate by taking different starting values.
You will observe that the ratios of successive terms still
approach 1.618034. A graph of the ratios of successive
terms (obtained by selecting column C) reveals this
behavior of the sequence. See Figures 5 and 6. This is
indeed an interesting observation but we need to find a
mathematical explanation for it.
Figure 2: A part of the Excel sheet
showing the 39th to 50th terms of
the Fibonacci sequence. Figure 3: Graphing the Fibonacci sequence on Excel.
Figure 4: The ratios of consecutive terms of the
Fibonacci sequence become steady at 1.618034
(in column C)