Exploring the Fibonnaci Sequence Using Spreadsheets

by

Scott Burrell

Generate a Fibonnaci sequence in the first column using f(0) = 1, f(1) = 1, and f(n) = f(n-1) + f(n-2)

 

a. Construct the ratio of each pair of adjacent terms in the Fibonnaci sequence. What happens as n increases? What about the ratio of every second term? etc.

 

b. Explore sequences where f(0) and f(1) are some arbitrary integers other than 1. If f(0)=1 and f(1) = 3, then your sequence is a Lucas Sequence. All such sequences, however, have the same limit of the ratio of successive terms.


I used Microsoft Excel to generate the Fibonnaci sequence from n=0 to n=30 in the table below. For the cell in column A row 1 and column A row 2, I entered a value of 1. For the column A row 3 cell, I entered the formula =a2+a1 to get a value of 2. Next, I took the lower right corner of the cell in column A row 3 and copied the formula all the way down to row 30. Now I can check to make sure my values are correct. For example, choose the value 75025 from a25. Now check this value using the formula f(25)=f(24)+f(23). Does, 46368+28657=75025? Yes it does, so the values are correct.

Notice below that I also constructed the ratio of each pair of adjacent terms in the Fibonnaci sequence. This was performed by selecting b2 and entering the formula =a2/a1. Again I took the lower right corner of cell b2 and copied all the way down the cell b30. We see that the ratio starts out at 1, increases to 2 and then decreases to 1.5 Then it varies between 1.6 and 1.7 for several values before it appears to stay at 1.618. We can make the observation that the ratio seems to remain the same as n gets larger.

1
1 1
2 2
3 1.5
5 1.66666666666667
8 1.6
13 1.625
21 1.61538461538462
34 1.61904761904762
55 1.61764705882353
89 1.61818181818182
144 1.61797752808989
233 1.61805555555556
377 1.61802575107296
610 1.61803713527851
987 1.61803278688525
1597 1.61803444782168
2584 1.61803381340013
4181 1.61803405572755
6765 1.61803396316671
10946 1.6180339985218
17711 1.61803398501736
28657 1.6180339901756
46368 1.61803398820532
75025 1.6180339889579
121393 1.61803398867044
196418 1.61803398878024
317811 1.6180339887383
514229 1.61803398875432
832040 1.6180339887482

Next, I chose some arbitrary integers for f(0) and f(1) other than 1. I decided to make f(0)=3 and f(1)=4. Notice that in the first column, the values are very different from the values of the Fibonnaci sequence in the first table. However, the interesting observation is that the ratio of the adjacent terms in the second column still approach the same limit of l.618 like the Fibonnaci sequence.

3
4 1.33333333333333
7 1.75
11 1.57142857142857
18 1.63636363636364
29 1.61111111111111
47 1.62068965517241
76 1.61702127659574
123 1.61842105263158
199 1.61788617886179
322 1.61809045226131
521 1.61801242236025
843 1.61804222648752
1364 1.61803084223013
2207 1.61803519061584
3571 1.6180335296783
5778 1.61803416409969
9349 1.61803392177224
15127 1.61803401433308
24476 1.61803397897799
39603 1.61803399248243
64079 1.61803398732419
103682 1.61803398929446
167761 1.61803398854189
271443 1.61803398882935
439204 1.61803398871955
710647 1.61803398876149
1149851 1.61803398874547
1860498 1.61803398875159
3010349 1.61803398874925

Lastly, I looked at a sequence where f(0) = 1 and f(1) = 3 which is called a Lucas Sequence. Notice again that a similar ratio exists such that as n gets large, the ratio stays very close to 1.618.

1
3 3
4 1.33333333333333
7 1.75
11 1.57142857142857
18 1.63636363636364
29 1.61111111111111
47 1.62068965517241
76 1.61702127659574
123 1.61842105263158
199 1.61788617886179
322 1.61809045226131
521 1.61801242236025
843 1.61804222648752
1364 1.61803084223013
2207 1.61803519061584
3571 1.6180335296783
5778 1.61803416409969
9349 1.61803392177224
15127 1.61803401433308
24476 1.61803397897799
39603 1.61803399248243
64079 1.61803398732419
103682 1.61803398929446
167761 1.61803398854189
271443 1.61803398882935
439204 1.61803398871955
710647 1.61803398876149
1149851 1.61803398874547
1860498 1.61803398875159


To conclude, we can say that the limit of the ratio for the adjacent terms of the Fibonnaci sequence and the Lucas sequence both approach 1.618, as well as, any other similar sequence, as n goes to infinity.

 

Return