The spreadsheet is a utility tool that can be adapted to many different explorations, presentations and simulations in mathematics. This write up explores the Fibonacci sequence using Excel.
Click here for the spreadsheet. Here’s the data for the Fibonacci sequence.
Fibonacci Numbers |
Ratio of terms |
Ratio of 2nd terms |
Ratio of 3rd terms |
Ratio of 4th terms |
Ratio of 5th terms |
1 |
|
|
|
|
|
1 |
1 |
|
|
|
|
2 |
2 |
2 |
|
|
|
3 |
1.5 |
3 |
3 |
|
|
5 |
1.666666667 |
2.5 |
5 |
5 |
|
8 |
1.6 |
2.66666667 |
4 |
8 |
8 |
13 |
1.625 |
2.6 |
4.33333333 |
6.5 |
13 |
21 |
1.615384615 |
2.625 |
4.2 |
7 |
10.5 |
34 |
1.619047619 |
2.61538462 |
4.25 |
6.8 |
11.3333333 |
55 |
1.617647059 |
2.61904762 |
4.23076923 |
6.875 |
11 |
89 |
1.618181818 |
2.61764706 |
4.23809524 |
6.84615385 |
11.125 |
144 |
1.617977528 |
2.61818182 |
4.23529412 |
6.85714286 |
11.0769231 |
233 |
1.618055556 |
2.61797753 |
4.23636364 |
6.85294118 |
11.0952381 |
377 |
1.618025751 |
2.61805556 |
4.23595506 |
6.85454545 |
11.0882353 |
610 |
1.618037135 |
2.61802575 |
4.23611111 |
6.85393258 |
11.0909091 |
987 |
1.618032787 |
2.61803714 |
4.2360515 |
6.85416667 |
11.0898876 |
1597 |
1.618034448 |
2.61803279 |
4.23607427 |
6.85407725 |
11.0902778 |
2584 |
1.618033813 |
2.61803445 |
4.23606557 |
6.85411141 |
11.0901288 |
4181 |
1.618034056 |
2.61803381 |
4.2360689 |
6.85409836 |
11.0901857 |
6765 |
1.618033963 |
2.61803406 |
4.23606763 |
6.85410334 |
11.0901639 |
10946 |
1.618033999 |
2.61803396 |
4.23606811 |
6.85410144 |
11.0901722 |
17711 |
1.618033985 |
2.618034 |
4.23606793 |
6.85410217 |
11.0901691 |
28657 |
1.61803399 |
2.61803399 |
4.236068 |
6.85410189 |
11.0901703 |
46368 |
1.618033988 |
2.61803399 |
4.23606797 |
6.854102 |
11.0901698 |
75025 |
1.618033989 |
2.61803399 |
4.23606798 |
6.85410196 |
11.09017 |
121393 |
1.618033989 |
2.61803399 |
4.23606798 |
6.85410197 |
11.0901699 |
196418 |
1.618033989 |
2.61803399 |
4.23606798 |
6.85410196 |
11.09017 |
317811 |
1.618033989 |
2.61803399 |
4.23606798 |
6.85410197 |
11.0901699 |
514229 |
1.618033989 |
2.61803399 |
4.23606798 |
6.85410197 |
11.0901699 |
Here’s a graph of all the ratios:
By graphing these together, you can see the similar shape of the graphs and how quickly each converges to its particular value.
Next, look at the Lucas sequence. This sequence starts with (2), 1 and 3 where the Fibonacci sequence starts with (0), 1 and 1.
Lucas Sequence |
Ratio of terms |
Ratio of 2nd terms |
Ratio of 3rd terms |
Ratio of 4th terms |
Ratio of 5th terms |
1 |
|
|
|
|
|
3 |
3 |
|
|
|
|
4 |
1.333333333 |
4 |
|
|
|
7 |
1.75 |
2.333333 |
7 |
|
|
11 |
1.571428571 |
2.75 |
3.666667 |
11 |
|
18 |
1.636363636 |
2.571429 |
4.5 |
6 |
18 |
29 |
1.611111111 |
2.636364 |
4.142857 |
7.25 |
9.666666667 |
47 |
1.620689655 |
2.611111 |
4.272727 |
6.714285714 |
11.75 |
76 |
1.617021277 |
2.62069 |
4.222222 |
6.909090909 |
10.85714286 |
123 |
1.618421053 |
2.617021 |
4.241379 |
6.833333333 |
11.18181818 |
199 |
1.617886179 |
2.618421 |
4.234043 |
6.862068966 |
11.05555556 |
322 |
1.618090452 |
2.617886 |
4.236842 |
6.85106383 |
11.10344828 |
521 |
1.618012422 |
2.61809 |
4.235772 |
6.855263158 |
11.08510638 |
843 |
1.618042226 |
2.618012 |
4.236181 |
6.853658537 |
11.09210526 |
1364 |
1.618030842 |
2.618042 |
4.236025 |
6.854271357 |
11.08943089 |
2207 |
1.618035191 |
2.618031 |
4.236084 |
6.854037267 |
11.09045226 |
3571 |
1.61803353 |
2.618035 |
4.236062 |
6.854126679 |
11.09006211 |
5778 |
1.618034164 |
2.618034 |
4.23607 |
6.854092527 |
11.09021113 |
9349 |
1.618033922 |
2.618034 |
4.236067 |
6.854105572 |
11.09015421 |
15127 |
1.618034014 |
2.618034 |
4.236068 |
6.854100589 |
11.09017595 |
24476 |
1.618033979 |
2.618034 |
4.236068 |
6.854102492 |
11.09016765 |
39603 |
1.618033992 |
2.618034 |
4.236068 |
6.854101765 |
11.09017082 |
64079 |
1.618033987 |
2.618034 |
4.236068 |
6.854102043 |
11.09016961 |
103682 |
1.618033989 |
2.618034 |
4.236068 |
6.854101937 |
11.09017007 |
167761 |
1.618033989 |
2.618034 |
4.236068 |
6.854101977 |
11.09016989 |
271443 |
1.618033989 |
2.618034 |
4.236068 |
6.854101962 |
11.09016996 |
439204 |
1.618033989 |
2.618034 |
4.236068 |
6.854101968 |
11.09016994 |
710647 |
1.618033989 |
2.618034 |
4.236068 |
6.854101966 |
11.09016995 |
1149851 |
1.618033989 |
2.618034 |
4.236068 |
6.854101966 |
11.09016994 |
This convergence of the ratios is the same as the Fibonacci sequence.
Finally, here’s a random sequence beginning with -1 and -7:…
Random Sequence |
Ratio of terms |
Ratio of 2nd terms |
Ratio of 3rd terms |
Ratio of 4th terms |
Ratio of 5th terms |
-1 |
|
|
|
|
|
-7 |
7 |
|
|
|
|
-8 |
1.142857143 |
8 |
|
|
|
-15 |
1.875 |
2.142857 |
15 |
|
|
-23 |
1.533333333 |
2.875 |
3.285714 |
23 |
|
-38 |
1.652173913 |
2.533333 |
4.75 |
5.428571 |
38 |
-61 |
1.605263158 |
2.652174 |
4.066667 |
7.625 |
8.714286 |
-99 |
1.62295082 |
2.605263 |
4.304348 |
6.6 |
12.375 |
-160 |
1.616161616 |
2.622951 |
4.210526 |
6.956522 |
10.66667 |
-259 |
1.61875 |
2.616162 |
4.245902 |
6.815789 |
11.26087 |
-419 |
1.617760618 |
2.61875 |
4.232323 |
6.868852 |
11.02632 |
-678 |
1.618138425 |
2.617761 |
4.2375 |
6.848485 |
11.11475 |
-1097 |
1.6179941 |
2.618138 |
4.235521 |
6.85625 |
11.08081 |
-1775 |
1.618049225 |
2.617994 |
4.236277 |
6.853282 |
11.09375 |
-2872 |
1.618028169 |
2.618049 |
4.235988 |
6.854415 |
11.0888 |
-4647 |
1.618036212 |
2.618028 |
4.236098 |
6.853982 |
11.09069 |
-7519 |
1.61803314 |
2.618036 |
4.236056 |
6.854148 |
11.08997 |
-12166 |
1.618034313 |
2.618033 |
4.236072 |
6.854085 |
11.09025 |
-19685 |
1.618033865 |
2.618034 |
4.236066 |
6.854109 |
11.09014 |
-31851 |
1.618034036 |
2.618034 |
4.236069 |
6.854099 |
11.09018 |
-51536 |
1.618033971 |
2.618034 |
4.236068 |
6.854103 |
11.09017 |
-83387 |
1.618033996 |
2.618034 |
4.236068 |
6.854102 |
11.09017 |
-134923 |
1.618033986 |
2.618034 |
4.236068 |
6.854102 |
11.09017 |
-218310 |
1.61803399 |
2.618034 |
4.236068 |
6.854102 |
11.09017 |
-353233 |
1.618033988 |
2.618034 |
4.236068 |
6.854102 |
11.09017 |
-571543 |
1.618033989 |
2.618034 |
4.236068 |
6.854102 |
11.09017 |
-924776 |
1.618033989 |
2.618034 |
4.236068 |
6.854102 |
11.09017 |
-1496319 |
1.618033989 |
2.618034 |
4.236068 |
6.854102 |
11.09017 |
-2421095 |
1.618033989 |
2.618034 |
4.236068 |
6.854102 |
11.09017 |
Here’s a comparison of the convergence of the first terms of the three sequences listed here:
After the initial jumping around to account for the differences in the first terms, the terms converge rapidly – by about the 6th number in the sequence there’s no measurable difference in the ratios, at least as far as the graph is concerned.
Last but not least, I wanted to compare the recursive formula – an = an-1 + an – to the closed form of the equation, which is
an = 1/sqrt(5) * (((1 + sqrt(5))/2)n – ((1 - sqrt(5))/2)n). I expected to see some round off error based on the way the spreadsheet would handle the square roots raised to a power. Here are the results:
Fibonacci Numbers |
Closed Formula |
1 |
1 |
1 |
1 |
2 |
2 |
3 |
3 |
5 |
5 |
8 |
8 |
13 |
13 |
21 |
21 |
34 |
34 |
55 |
55 |
89 |
89 |
144 |
144 |
233 |
233 |
377 |
377 |
610 |
610 |
987 |
987 |
1597 |
1597 |
2584 |
2584 |
4181 |
4181 |
6765 |
6765 |
10946 |
10946 |
17711 |
17711 |
28657 |
28657 |
46368 |
46368 |
75025 |
75025 |
121393 |
121393 |
196418 |
196418 |
317811 |
317811 |
514229 |
514229 |
As you can see, there’s not a bit of error! That was a nice surprise, especially since this was the formula I had to key into the spreadsheet:
=1/SQRT(5)*(POWER((1+SQRT(5))/2,B2) - POWER((1-SQRT(5))/ 2, B2))
Note: B2 is a hidden value that represents n in the prior equation.