Exploring Spreadsheets in Mathematics
By
Brooke Norman
Program such as Excel are excellent spreadsheet
programs that can be used to generate tables of numbers.
In this assignment, we are going to use Excel to
generate a Fibonnaci sequence.
I
used Microsoft Excel to generate a Fibonnaci sequence
from n=1 to n=34. You can see the
sequence in the table below. How did I
do this? First, I listed a number 1 in
block A2. In block A3, I entered a 1
also. In block A4, I entered the formula
=A2+A3. This gives block A4 a value of
2. I then highlighted the blocks in
column A from A4 down to A34. I then used the “fill down” command to copy the
formula into each of these blocks.
I can check this to make sure it is
correct. Let’s choose block A23 for
example. We will add block A21+A22 and
see if it gives us A23.
[6765+10946=17711]. If we look at
the number in A23, the value is 17711.
The values are correct!
I
then found the ratio of each pair of adjacent terms in the Fibonnaci
sequence. This was done by entering a 1
in block B2. In block B3, I entered the
equation of =A3/A2. I used the same
technique of highlighting block B3 and dragging the highlight all the way down
to B34. Take a look at the different
ratios. It appears that one increases
and the next decreases and continues in that pattern until they begin to reach
common number. This is called a limit. They
seem to approach a limit of 1.618033989 as n becomes larger or
approaches infinity.
Fibonnaci Seq |
Ratio |
1 |
1 |
1 |
1 |
2 |
2 |
3 |
1.5 |
5 |
1.666666667 |
8 |
1.6 |
13 |
1.625 |
21 |
1.615384615 |
34 |
1.619047619 |
55 |
1.617647059 |
89 |
1.618181818 |
144 |
1.617977528 |
233 |
1.618055556 |
377 |
1.618025751 |
610 |
1.618037135 |
987 |
1.618032787 |
1597 |
1.618034448 |
2584 |
1.618033813 |
4181 |
1.618034056 |
6765 |
1.618033963 |
10946 |
1.618033999 |
17711 |
1.618033985 |
28657 |
1.61803399 |
46368 |
1.618033988 |
75025 |
1.618033989 |
121393 |
1.618033989 |
196418 |
1.618033989 |
317811 |
1.618033989 |
514229 |
1.618033989 |
832040 |
1.618033989 |
1346269 |
1.618033989 |
2178309 |
1.618033989 |
3524578 |
1.618033989 |
Next, I chose some arbitrary integers for f(0) and f(1), other than 1. I decided to make f(0)=4 and f(1)=5. Notice that in the first column, the values are very different from the values of the Fibonnaci sequence in the first table. But take notice that the ratio of the adjacent terms in the second column still approach the same limit of l.618, just like the Fibonnaci sequence.
Fibonnaci Seq |
Ratio |
Seq. 2 |
Ratio 2 |
1 |
1 |
4 |
|
1 |
1 |
5 |
1.25 |
2 |
2 |
9 |
1.8 |
3 |
1.5 |
14 |
1.555555556 |
5 |
1.666666667 |
23 |
1.642857143 |
8 |
1.6 |
37 |
1.608695652 |
13 |
1.625 |
60 |
1.621621622 |
21 |
1.615384615 |
97 |
1.616666667 |
34 |
1.619047619 |
157 |
1.618556701 |
55 |
1.617647059 |
254 |
1.617834395 |
89 |
1.618181818 |
411 |
1.618110236 |
144 |
1.617977528 |
665 |
1.618004866 |
233 |
1.618055556 |
1076 |
1.618045113 |
377 |
1.618025751 |
1741 |
1.61802974 |
610 |
1.618037135 |
2817 |
1.618035612 |
987 |
1.618032787 |
4558 |
1.618033369 |
1597 |
1.618034448 |
7375 |
1.618034226 |
2584 |
1.618033813 |
11933 |
1.618033898 |
4181 |
1.618034056 |
19308 |
1.618034023 |
6765 |
1.618033963 |
31241 |
1.618033976 |
10946 |
1.618033999 |
50549 |
1.618033994 |
17711 |
1.618033985 |
81790 |
1.618033987 |
28657 |
1.61803399 |
132339 |
1.618033989 |
46368 |
1.618033988 |
214129 |
1.618033988 |
75025 |
1.618033989 |
346468 |
1.618033989 |
121393 |
1.618033989 |
560597 |
1.618033989 |
196418 |
1.618033989 |
907065 |
1.618033989 |
317811 |
1.618033989 |
1467662 |
1.618033989 |
514229 |
1.618033989 |
2374727 |
1.618033989 |
832040 |
1.618033989 |
3842389 |
1.618033989 |
1346269 |
1.618033989 |
6217116 |
1.618033989 |
2178309 |
1.618033989 |
10059505 |
1.618033989 |
3524578 |
1.618033989 |
16276621 |
1.618033989 |
For
the last part of this assignment, I explored a sequence where f(0)=1 and f(1)=3.
This is called a Lucas Sequence.
Take notice that as n gets larger or approaches infinity, the ratio
reaches the same limit as the Fibonnaci sequence.
Fibonnaci Seq |
Fib. Ratio |
Lucas Seq |
Luc.
Ratio |
1 |
1 |
1 |
|
1 |
1 |
3 |
3 |
2 |
2 |
4 |
1.333333333 |
3 |
1.5 |
7 |
1.75 |
5 |
1.666666667 |
11 |
1.571428571 |
8 |
1.6 |
18 |
1.636363636 |
13 |
1.625 |
29 |
1.611111111 |
21 |
1.615384615 |
47 |
1.620689655 |
34 |
1.619047619 |
76 |
1.617021277 |
55 |
1.617647059 |
123 |
1.618421053 |
89 |
1.618181818 |
199 |
1.617886179 |
144 |
1.617977528 |
322 |
1.618090452 |
233 |
1.618055556 |
521 |
1.618012422 |
377 |
1.618025751 |
843 |
1.618042226 |
610 |
1.618037135 |
1364 |
1.618030842 |
987 |
1.618032787 |
2207 |
1.618035191 |
1597 |
1.618034448 |
3571 |
1.61803353 |
2584 |
1.618033813 |
5778 |
1.618034164 |
4181 |
1.618034056 |
9349 |
1.618033922 |
6765 |
1.618033963 |
15127 |
1.618034014 |
10946 |
1.618033999 |
24476 |
1.618033979 |
17711 |
1.618033985 |
39603 |
1.618033992 |
28657 |
1.61803399 |
64079 |
1.618033987 |
46368 |
1.618033988 |
103682 |
1.618033989 |
75025 |
1.618033989 |
167761 |
1.618033989 |
121393 |
1.618033989 |
271443 |
1.618033989 |
196418 |
1.618033989 |
439204 |
1.618033989 |
317811 |
1.618033989 |
710647 |
1.618033989 |
514229 |
1.618033989 |
1149851 |
1.618033989 |
832040 |
1.618033989 |
1860498 |
1.618033989 |
1346269 |
1.618033989 |
3010349 |
1.618033989 |
2178309 |
1.618033989 |
4870847 |
1.618033989 |
3524578 |
1.618033989 |
7881196 |
1.618033989 |
In
closing, 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, other similar sequences, as n goes to infinity.
Return
to Brooke’s
EMAT6680 Homepage.