Assignment 12: Exploring Mathematics with Spreadsheets

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.

Return