Leonardo Fibonacci, an Italian mathematician who lived during the 12th and 13th centuries, made many contributions to number theory. He is best known for a numerical sequence that bears his name, The Fibonacci sequence. In the Fibonnaci sequence two numbers are required as seed numbers. Subsequent numbers are generated by summing the two preceding numbers. We will look at how to generate a Fibonnaci sequence in Mircrosoft Excel and some of the properties of these sequences.

The first Fibonnaci Sequence we will look at is when f(0) = 1 and f(1) = 1. Using Microsoft Excel, in the first column, first cell enter 1 and in the first column, second cell enter 1. This is your f(0) = 1 and f(1) = 1. We will then use the sequence f(n) = f(n-1) + f(n-2). We will enter this as an equation In the third cell. So the third cell = a1 + a2. Then, select the third cell where the equation is contained, many cells beneath this one (probably 20 or 25) and choose Edit, Fill Down. This will copy this equation into the cells and give a value for each cell.

Now that we have this done, we want to look at the ratio of each pair of adjacent terms in the Fibonnaci Sequence. We can also do this by using an equation in Excel. Go to the second column, first cell and enter = a2/a1. This finds the ratio between the 2nd and 1st term. Then, select the first cell and the 25 below it and choose Edit Fill Down. This fills in the ratio of the adjacent terms. For example, in cell 6 will be the ratio between the 6th term and the 5th term.

We can also look at the ratio of every second term. We do this similar to the way mentioned above. This time the equation in the first cell would be = a3/a1. This finds the ratio between the 3rd and 1st term. Again, Fill Down the following 25 cells.

Your spreadsheet should look like the following.

If you look at the ratio of the terms as n increases, we will notice some interesting things. As n increases, the limit of the ratio of the adjacent terms approaches 1.618 or (1 + sqrt 5)/ 2 . This is known as the golden ratio. Also, as n increases, the limit of the ratio of every second term approaches 2.618 or (3 + sqrt 5 )/ 2. If we find the ratio between every third term, the limit will approach 4.236 as n increases. Again, if we find the ratio between every fourth term, the limit will approach 6.85 as n increases. Try these in Microsoft Excel for yourself.

In the above Fibonnaci sequence, we looked at the case where f(0) = 1 and f(1 ) = 1. We can also look at other Fibonnaci sequences where f(0) and f(1) are arbitrary integers. Let's look at a case where f(0) = 1 and f(1) = 2. If we perform the same operations as above into a new spreadsheet in Excel, we will get a table as below. The first column is the terms in the sequence, the second column is the ratio of the each pair of adjacent terms, and the third column is the ratio of every second term.

If you notice the limit of the ratio of each adjacent term as n increases, the limit approaches 1.618. This was the same limit as in the first Fibonnaci sequence. We will also notice that as n increases, the ratio of every second term in this sequence approaches 2.618 which was the same value in the first sequence.

Let's take a look at one last sequence, the Lucas Sequence which is a special Fibonnaci Sequence. The Lucas Sequence occurs when f(0) = 1 and f(1) = 3. Do you think that the limit of the ratio in this sequence will be the same as the two above? Take a look at the table below that was created in Excel.

We can see that in fact the limit of the ratio of each pair of adjacent terms in this sequence is the same as the first two. We also see that the limit of the ratio of every second term is the same as in the first two sequences.

Exploring more on your own, we can conclude that all sequences where the nth term is obtained by adding the (n-1) term and the (n-2) term will result in the ratio of successive terms having the same limit. The ratio of every second term in the sequence will also be the same. What do you think will happen for every third term or every fourth term? Try this on Microsoft Excel.

Let's look at a proof of why the ratio of successive terms in a Fibonnaci sequence will approach the golden ratio, 1.618... By the definition of a Fibonnaci sequence, f (n+1) = f (n) + f(n-1). f(n+1) / f(n) = (f(n) + f(n-1))/f(n) = 1 + f(n-1)/f(n). So, f(n+1)/f(n) = 1 + f(n-1) / f(n). But the limit as n approaches infinity is f(n+1) / f(n) and the limit is also f(n) / f(n-1). So, the limit (L) is equal to 1 + 1/L and L^2 = L + 1. Thus, L^2 - L - 1 = 0 and using the quadratic equation, L = (1 + sqrt5)/2 or 1.618... . So, the limit as n approaches infinity of the ratio of successive terms will always be (1 + sqrt 5) / 2. I will leave it up to you to try and prove the limit of the raio of every second term.

Return to April's Home Page