Assignment 12: Exploring Mathematics with Spreadsheets
By Rebecca L. Adcock
The Fibonacci sequence was identified way back in 1202 by a fellow named Leonardo Fibonacci. Although it is ŌjustÕ a sequence of numbers, as you will see, it also occurs in nature. First, letÕs look at the sequence É
You typically start the sequence with 1,1. We could sit here and generate these numbers using pencil and paper but itÕs a whole lot easier to plug this into a spreadsheet. Here
Õs the first 20 numbers in the sequence as created in an Excel spreadsheet:
|
Fibonnaci Sequence |
|
|
|
|
|
1 |
|
|
1 |
|
|
2 |
|
|
3 |
|
|
5 |
|
|
8 |
|
|
13 |
|
|
21 |
|
|
34 |
|
|
55 |
|
|
89 |
|
|
144 |
|
|
233 |
|
|
377 |
|
|
610 |
|
|
987 |
|
|
1597 |
|
|
2584 |
|
|
|
|
I entered 1 and 1 in the first cells of my column and entered an equation of Ō=A3+A4Õ in the next cell. (Placing the cursor in the lower right corner of cell A5 and dragging downwards copies the formula.)
So far there is nothing remarkable about this simple set of numbers. But look what happens when you calculate the ratio between adjacent elements.
|
Fibonnaci Sequence |
|
|
|
|
|
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 |
As the number of entries in the sequence, the ratio tends toward the same value, something in the neighborhood of 1.618033989. So what? This number has been around since the ancient Greeks and it is called the Golden Ratio (capitalized!). HereÕs how the story goes:
The Greeks considered a line segment to be divided into the Golden Ratio if the ratio of the shorter part to the longer part was the same as the ratio of the longer part to the whole line segment.
I created this sketch to show you what that meansÉ..
The Golden Ratio here has been rounded to 1.62.
LetÕs see what else we can discover about the Fibonacci sequence. Is there anything significant about the ratio between every other entry? (See the third column.)
|
Fibonnaci Sequence |
|
|
|
|
|
|
|
1 |
|
|
|
1 |
1 |
|
|
2 |
2 |
2 |
|
3 |
1.5 |
3 |
|
5 |
1.666666667 |
2.5 |
|
8 |
1.6 |
2.66666667 |
|
13 |
1.625 |
2.6 |
|
21 |
1.615384615 |
2.625 |
|
34 |
1.619047619 |
2.61538462 |
|
55 |
1.617647059 |
2.61904762 |
|
89 |
1.618181818 |
2.61764706 |
|
144 |
1.617977528 |
2.61818182 |
|
233 |
1.618055556 |
2.61797753 |
|
377 |
1.618025751 |
2.61805556 |
|
610 |
1.618037135 |
2.61802575 |
|
987 |
1.618032787 |
2.61803714 |
|
1597 |
1.618034448 |
2.61803279 |
|
2584 |
1.618033813 |
2.61803445 |
|
4181 |
1.618034056 |
2.61803381 |
|
6765 |
1.618033963 |
2.61803406 |
|
10946 |
1.618033999 |
2.61803396 |
|
17711 |
1.618033985 |
2.618034 |
|
28657 |
1.61803399 |
2.61803399 |
|
46368 |
1.618033988 |
2.61803399 |
|
75025 |
1.618033989 |
2.61803399 |
|
121393 |
1.618033989 |
2.61803399 |
|
196418 |
1.618033989 |
2.61803399 |
|
317811 |
1.618033989 |
2.61803399 |
Apparently soÉ. The ratio here is tending towards 2.618033989 which is the Golden Ratio +1. I donÕt have a fancy store about this finding; IÕm sure itÕs not a revelation to anyone but you and me and 1700 years from now, it will not be called AdcockÕs sequence.
LetÕs see what else we can find. What if we start our sequence with 1,3?
|
Lucas Sequence |
|
|
|
|
|
|
|
1 |
|
|
|
3 |
3 |
|
|
4 |
1.333333333 |
4 |
|
7 |
1.75 |
2.33333333 |
|
11 |
1.571428571 |
2.75 |
|
18 |
1.636363636 |
2.57142857 |
|
29 |
1.611111111 |
2.63636364 |
|
47 |
1.620689655 |
2.61111111 |
|
76 |
1.617021277 |
2.62068966 |
|
123 |
1.618421053 |
2.61702128 |
|
199 |
1.617886179 |
2.61842105 |
|
322 |
1.618090452 |
2.61788618 |
|
521 |
1.618012422 |
2.61809045 |
|
843 |
1.618042226 |
2.61801242 |
|
1364 |
1.618030842 |
2.61804223 |
|
2207 |
1.618035191 |
2.61803084 |
|
3571 |
1.61803353 |
2.61803519 |
|
5778 |
1.618034164 |
2.61803353 |
|
9349 |
1.618033922 |
2.61803416 |
|
15127 |
1.618034014 |
2.61803392 |
|
24476 |
1.618033979 |
2.61803401 |
|
39603 |
1.618033992 |
2.61803398 |
|
64079 |
1.618033987 |
2.61803399 |
|
103682 |
1.618033989 |
2.61803399 |
|
167761 |
1.618033989 |
2.61803399 |
|
271443 |
1.618033989 |
2.61803399 |
|
439204 |
1.618033989 |
2.61803399 |
|
710647 |
1.618033989 |
2.61803399 |
We see the same results of the Golden Ratio and the Golden Ratio +1. This is actually called the Lucas sequence. (I looked up Mr. Lucas and although I found some information on his sequence, I never discovered his identity.)
HereÕs something else that Mr. Lucas identifiedÉ
|
Lucas Sequence |
|
|
|
|
|
|
|
2 |
|
|
|
4 |
2 |
|
|
14 |
3.5 |
7 |
|
52 |
3.71428571 |
13 |
|
194 |
3.73076923 |
13.8571429 |
|
724 |
3.73195876 |
13.9230769 |
|
2702 |
3.7320442 |
13.9278351 |
|
10084 |
3.73205033 |
13.9281768 |
|
37634 |
3.73205077 |
13.9282013 |
|
140452 |
3.73205081 |
13.9282031 |
|
524174 |
3.73205081 |
13.9282032 |
|
1956244 |
3.73205081 |
13.9282032 |
|
7300802 |
3.73205081 |
13.9282032 |
|
27246964 |
3.73205081 |
13.9282032 |
|
101687054 |
3.73205081 |
13.9282032 |
|
379501252 |
3.73205081 |
13.9282032 |
|
1416317954 |
3.73205081 |
13.9282032 |
|
5285770564 |
3.73205081 |
13.9282032 |
|
19726764302 |
3.73205081 |
13.9282032 |
|
73621286644 |
3.73205081 |
13.9282032 |
|
2.74758E+11 |
3.73205081 |
13.9282032 |
|
1.02541E+12 |
3.73205081 |
13.9282032 |
|
3.82689E+12 |
3.73205081 |
13.9282032 |
|
1.42822E+13 |
3.73205081 |
13.9282032 |
|
5.33017E+13 |
3.73205081 |
13.9282032 |
|
1.98925E+14 |
3.73205081 |
13.9282032 |
|
7.42397E+14 |
3.73205081 |
13.9282032 |
|
2.77066E+15 |
3.73205081 |
13.9282032 |
Column 1 is based on the beginning numbers 2,4 and each subsequent number is the sum of (4 times the previous number) minus (the number previous to that). In short, A4= 4*A3-A2.
The above result leads me to believe there may be a Golden Something quality to 3.73205081 and 13.9282032 but I donÕt know what it is. LetÕs see what happens if we divide the third column by the second column.
|
Lucas Sequence |
|
|
|
|
|
|
|
|
|
2 |
|
|
|
|
4 |
2 |
|
|
|
14 |
3.5 |
7 |
2 |
|
52 |
3.71428571 |
13 |
3.5 |
|
194 |
3.73076923 |
13.8571429 |
3.71428571 |
|
724 |
3.73195876 |
13.9230769 |
3.73076923 |
|
2702 |
3.7320442 |
13.9278351 |
3.73195876 |
|
10084 |
3.73205033 |
13.9281768 |
3.7320442 |
|
37634 |
3.73205077 |
13.9282013 |
3.73205033 |
|
140452 |
3.73205081 |
13.9282031 |
3.73205077 |
|
524174 |
3.73205081 |
13.9282032 |
3.73205081 |
|
1956244 |
3.73205081 |
13.9282032 |
3.73205081 |
|
7300802 |
3.73205081 |
13.9282032 |
3.73205081 |
|
27246964 |
3.73205081 |
13.9282032 |
3.73205081 |
|
101687054 |
3.73205081 |
13.9282032 |
3.73205081 |
|
379501252 |
3.73205081 |
13.9282032 |
3.73205081 |
|
1416317954 |
3.73205081 |
13.9282032 |
3.73205081 |
|
5285770564 |
3.73205081 |
13.9282032 |
3.73205081 |
|
19726764302 |
3.73205081 |
13.9282032 |
3.73205081 |
|
73621286644 |
3.73205081 |
13.9282032 |
3.73205081 |
|
2.74758E+11 |
3.73205081 |
13.9282032 |
3.73205081 |
|
1.02541E+12 |
3.73205081 |
13.9282032 |
3.73205081 |
|
3.82689E+12 |
3.73205081 |
13.9282032 |
3.73205081 |
|
1.42822E+13 |
3.73205081 |
13.9282032 |
3.73205081 |
|
5.33017E+13 |
3.73205081 |
13.9282032 |
3.73205081 |
|
1.98925E+14 |
3.73205081 |
13.9282032 |
3.73205081 |
|
7.42397E+14 |
3.73205081 |
13.9282032 |
3.73205081 |
|
2.77066E+15 |
3.73205081 |
13.9282032 |
3.73205081 |
What we have discovered is that 13.9282032 (ratio of every other number in sequence) is the square of 3.73205081 (ratio of sequential numbers in the sequence).
LetÕs see what happens if we divide an element in the sequence by one 3 rows previous instead É
|
Lucas Sequence |
|
|
|
|
|
|
|
|
|
|
|
2 |
|
|
|
|
|
4 |
2 |
|
|
|
|
14 |
3.5 |
7 |
|
|
|
52 |
3.71428571 |
13 |
26 |
|
|
194 |
3.73076923 |
13.8571429 |
48.5 |
13 |
|
724 |
3.73195876 |
13.9230769 |
51.7142857 |
13.8571429 |
|
2702 |
3.7320442 |
13.9278351 |
51.9615385 |
13.9230769 |
|
10084 |
3.73205033 |
13.9281768 |
51.9793814 |
13.9278351 |
|
37634 |
3.73205077 |
13.9282013 |
51.980663 |
13.9281768 |
|
140452 |
3.73205081 |
13.9282031 |
51.980755 |
13.9282013 |
|
524174 |
3.73205081 |
13.9282032 |
51.9807616 |
13.9282031 |
|
1956244 |
3.73205081 |
13.9282032 |
51.9807621 |
13.9282032 |
|
7300802 |
3.73205081 |
13.9282032 |
51.9807621 |
13.9282032 |
|
27246964 |
3.73205081 |
13.9282032 |
51.9807621 |
13.9282032 |
|
101687054 |
3.73205081 |
13.9282032 |
51.9807621 |
13.9282032 |
|
379501252 |
3.73205081 |
13.9282032 |
51.9807621 |
13.9282032 |
|
1416317954 |
3.73205081 |
13.9282032 |
51.9807621 |
13.9282032 |
|
5285770564 |
3.73205081 |
13.9282032 |
51.9807621 |
13.9282032 |
|
19726764302 |
3.73205081 |
13.9282032 |
51.9807621 |
13.9282032 |
|
73621286644 |
3.73205081 |
13.9282032 |
51.9807621 |
13.9282032 |
|
2.74758E+11 |
3.73205081 |
13.9282032 |
51.9807621 |
13.9282032 |
|
1.02541E+12 |
3.73205081 |
13.9282032 |
51.9807621 |
13.9282032 |
|
3.82689E+12 |
3.73205081 |
13.9282032 |
51.9807621 |
13.9282032 |
|
1.42822E+13 |
3.73205081 |
13.9282032 |
51.9807621 |
13.9282032 |
|
5.33017E+13 |
3.73205081 |
13.9282032 |
51.9807621 |
13.9282032 |
|
1.98925E+14 |
3.73205081 |
13.9282032 |
51.9807621 |
13.9282032 |
|
7.42397E+14 |
3.73205081 |
13.9282032 |
51.9807621 |
13.9282032 |
|
2.77066E+15 |
3.73205081 |
13.9282032 |
51.9807621 |
13.9282032 |
Now we have entries approaching 51.9807621É.
In this exercise, weÕve looked at sequences of numbers, some famous and some not, that were defined by mathematicians centuries ago, when all the calculations were manual. Think of trying to produce with pencil and paper the numbers we have listed here. By spreadsheet, they were produced in a matter of minutes and the formulas used were simple addition, multiplication and division.