Spreadsheets
Assignment 12
Problem #4
By Erin Cain
In this problem we are asked
to do the following:
Generate a Fibonnaci sequence in the first column
using f(0) = 1, f(1) = 1,
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.
To begin this problem we must first generate
a Fibonnaci sequence in the first column of the spreadsheet. In order to do this, we must first enter the
first 2 entries in the Fibonnaci sequence in A2 and A3 (1 and 1). Then, in A4, we must enter the formula for
the Fibonnaci numbers which is = A2 + A3.
You then highlight the A4 cell, copy it, and then drag down and
highlight the number of cells you want to fill and paste the formula. The Fibonnaci sequence should then be visible
in column A.
|
Fibonnaci
Sequence |
|
1 |
|
1 |
|
2 |
|
3 |
|
5 |
|
8 |
|
13 |
|
21 |
|
34 |
|
55 |
|
89 |
|
144 |
|
233 |
|
377 |
|
610 |
|
987 |
|
1597 |
|
2584 |
|
4181 |
|
6765 |
|
10946 |
|
17711 |
|
28657 |
|
46368 |
|
75025 |
|
121393 |
|
196418 |
|
317811 |
|
514229 |
|
832040 |
|
1346269 |
|
2178309 |
Now we want to look at the ratios between each
pair of adjacent terms in the Fibonnaci sequence. In order to do this, we must move to column B
in our spreadsheet. In cell B3, we want
to type in the formula = A3/A2. Next we
fill in the rest of the column in the same exact way we did before for just the
sequence. We get the following:
|
Fibonnaci
Sequence |
Ratios |
|
1 |
|
|
1 |
1 |
|
2 |
1 |
|
3 |
2 |
|
5 |
1.5 |
|
8 |
1.666667 |
|
13 |
1.6 |
|
21 |
1.625 |
|
34 |
1.615385 |
|
55 |
1.619048 |
|
89 |
1.617647 |
|
144 |
1.618182 |
|
233 |
1.617978 |
|
377 |
1.618056 |
|
610 |
1.618026 |
|
987 |
1.618037 |
|
1597 |
1.618033 |
|
2584 |
1.618034 |
|
4181 |
1.618034 |
|
6765 |
1.618034 |
|
10946 |
1.618034 |
|
17711 |
1.618034 |
|
28657 |
1.618034 |
|
46368 |
1.618034 |
|
75025 |
1.618034 |
|
121393 |
1.618034 |
|
196418 |
1.618034 |
|
317811 |
1.618034 |
|
514229 |
1.618034 |
|
832040 |
1.618034 |
|
1346269 |
1.618034 |
|
2178309 |
1.618034 |
Let’s look at what happens to the ratios as
the Fibonnaci sequence increases in value.
As you can see from the table, the ratios start at 1, jump up to 2, then
drop down to 1.5, and then slightly jump up to approximately 1.6. The ratios then end up staying at or close to
the value of 1.618034. One way we can
summarize this is that as n approaches infinity, the ratios of two adjacent
numbers in the Fibonnaci sequence approaches 1.618034.
Now what will happen if we choose two
arbitrary numbers to begin our sequence besides one. Lets take 3 and 5 for example; i.e. f(0) = 3 and
f(1) = 5.
|
Fibonnaci
Sequence |
Ratios |
|
f(0) = 3
and f(1) = 5 |
ratios |
|
1 |
|
|
3 |
|
|
1 |
1 |
|
5 |
1.666667 |
|
2 |
1 |
|
8 |
1.666667 |
|
3 |
2 |
|
13 |
1.6 |
|
5 |
1.5 |
|
21 |
1.625 |
|
8 |
1.666667 |
|
34 |
1.615385 |
|
13 |
1.6 |
|
55 |
1.619048 |
|
21 |
1.625 |
|
89 |
1.617647 |
|
34 |
1.615385 |
|
144 |
1.618182 |
|
55 |
1.619048 |
|
233 |
1.617978 |
|
89 |
1.617647 |
|
377 |
1.618056 |
|
144 |
1.618182 |
|
610 |
1.618026 |
|
233 |
1.617978 |
|
987 |
1.618037 |
|
377 |
1.618056 |
|
1597 |
1.618033 |
|
610 |
1.618026 |
|
2584 |
1.618034 |
|
987 |
1.618037 |
|
4181 |
1.618034 |
|
1597 |
1.618033 |
|
6765 |
1.618034 |
|
2584 |
1.618034 |
|
10946 |
1.618034 |
|
4181 |
1.618034 |
|
17711 |
1.618034 |
|
6765 |
1.618034 |
|
28657 |
1.618034 |
|
10946 |
1.618034 |
|
46368 |
1.618034 |
|
17711 |
1.618034 |
|
75025 |
1.618034 |
|
28657 |
1.618034 |
|
121393 |
1.618034 |
|
46368 |
1.618034 |
|
196418 |
1.618034 |
|
75025 |
1.618034 |
|
317811 |
1.618034 |
|
121393 |
1.618034 |
|
514229 |
1.618034 |
|
196418 |
1.618034 |
|
832040 |
1.618034 |
|
317811 |
1.618034 |
|
1346269 |
1.618034 |
|
514229 |
1.618034 |
|
2178309 |
1.618034 |
|
832040 |
1.618034 |
|
3524578 |
1.618034 |
|
1346269 |
1.618034 |
|
5702887 |
1.618034 |
|
2178309 |
1.618034 |
|
9227465 |
1.618034 |
First let’s note how different the sequence
columns are. Our new sequence, all
though the entries are formed by the same formula, end up being very different numbers. However, the ratios still seem to approach
1.618034 as n increases, i.e. as n approaches infinity. In actuality, the 2nd
sequence reaches 1.618034 three numbers earlier than the Fibonnaci sequence.
Finally, as mentioned in the description of
the problem at the top of the page, a Lucas Sequence is one in which f(0) = 1
and f(1) = 3. Let us take a look at this
case as well.
|
Fibonnaci
Sequence |
Ratios |
|
f(0) = 3
and f(1) = 5 |
ratios |
|
Lucas
Sequence |
ratios |
|
1 |
|
|
3 |
|
|
1 |
|
|
1 |
1 |
|
5 |
1.666667 |
|
3 |
3 |
|
2 |
1 |
|
8 |
1.666667 |
|
4 |
1.333333 |
|
3 |
2 |
|
13 |
1.6 |
|
7 |
1.75 |
|
5 |
1.5 |
|
21 |
1.625 |
|
11 |
1.571429 |
|
8 |
1.666667 |
|
34 |
1.615385 |
|
18 |
1.636364 |
|
13 |
1.6 |
|
55 |
1.619048 |
|
29 |
1.611111 |
|
21 |
1.625 |
|
89 |
1.617647 |
|
47 |
1.62069 |
|
34 |
1.615385 |
|
144 |
1.618182 |
|
76 |
1.617021 |
|
55 |
1.619048 |
|
233 |
1.617978 |
|
123 |
1.618421 |
|
89 |
1.617647 |
|
377 |
1.618056 |
|
199 |
1.617886 |
|
144 |
1.618182 |
|
610 |
1.618026 |
|
322 |
1.61809 |
|
233 |
1.617978 |
|
987 |
1.618037 |
|
521 |
1.618012 |
|
377 |
1.618056 |
|
1597 |
1.618033 |
|
843 |
1.618042 |
|
610 |
1.618026 |
|
2584 |
1.618034 |
|
1364 |
1.618031 |
|
987 |
1.618037 |
|
4181 |
1.618034 |
|
2207 |
1.618035 |
|
1597 |
1.618033 |
|
6765 |
1.618034 |
|
3571 |
1.618034 |
|
2584 |
1.618034 |
|
10946 |
1.618034 |
|
5778 |
1.618034 |
|
4181 |
1.618034 |
|
17711 |
1.618034 |
|
9349 |
1.618034 |
|
6765 |
1.618034 |
|
28657 |
1.618034 |
|
15127 |
1.618034 |
|
10946 |
1.618034 |
|
46368 |
1.618034 |
|
24476 |
1.618034 |
|
17711 |
1.618034 |
|
75025 |
1.618034 |
|
39603 |
1.618034 |
|
28657 |
1.618034 |
|
121393 |
1.618034 |
|
64079 |
1.618034 |
|
46368 |
1.618034 |
|
196418 |
1.618034 |
|
103682 |
1.618034 |
|
75025 |
1.618034 |
|
317811 |
1.618034 |
|
167761 |
1.618034 |
|
121393 |
1.618034 |
|
514229 |
1.618034 |
|
271443 |
1.618034 |
|
196418 |
1.618034 |
|
832040 |
1.618034 |
|
439204 |
1.618034 |
|
317811 |
1.618034 |
|
1346269 |
1.618034 |
|
710647 |
1.618034 |
|
514229 |
1.618034 |
|
2178309 |
1.618034 |
|
1149851 |
1.618034 |
|
832040 |
1.618034 |
|
3524578 |
1.618034 |
|
1860498 |
1.618034 |
|
1346269 |
1.618034 |
|
5702887 |
1.618034 |
|
3010349 |
1.618034 |
|
2178309 |
1.618034 |
|
9227465 |
1.618034 |
|
4870847 |
1.618034 |
Once again, be sure to note that as n
approaches infinity, the ratios approach 1.618034. When looking at the Lucas sequence, it
obtains a ratio of 1.618034 one number before the Fibonnaci sequence and two
after the second sequence I made.