Excel Project
|
Chapter 9: Spiral Growth in Nature: Fibonacci Numbers and the Golden Ratio Excel Project |
|
Download all Excel spreadsheets for this chapter.
Spreadsheets are wonderful tools for listing many terms of a sequence of numbers that are defined either recursively or explicitly. We illustrate this concept by generating the first 12 Fibonacci numbers using a spreadsheet and their recursive definition given by F1=1, F2=1, and FN=FN-1+FN-2 for N > 2.
Figure 9.1 shows the first 12 values of the Fibonacci sequence generated by a spreadsheet. Column A contains the value of the subscript N; column B is used to represent the Nth Fibonacci number.
Column C is used to calculate the value of the ratio FN/FN-1. Notice that as N increases, the numerical approximation given by the spreadsheet appears to be approaching the golden ratio 
.
To generate Figure 9.1, in column A we start by putting a '1' in cell A3. We then proceed to type =A3+1 in cell A4. By copying this formula to all the cells below A4 we generate the numbers from 1 to 12 in column A. To generate column B, we start by placing a '1' in cell B3 and a '1' in cell B4. We then use the formula =B3+B4 in cell B5 to recursively compute the Fibonacci numbers. Copying this formula to the cells below it in column B produces the output in Figure 9.1. To produce the values in column C, we simply type the formula =B5/B4 in cell C5 and copy this formula throughout column C. A list of formulas used to generate Figure 9.1 is shown in Figure 9.2.
One process that mathematicians continually use is that of forming a hypothesis by spotting a pattern, testing that hypothesis by experimentation on several examples, and then, after understanding these examples, proving the original hypothesis to be true. In the previous example, we used the spreadsheet to spot the pattern that FN/FN-1 appears to approach the golden ratio as N gets large. Consider the following example of this process.
Joe notices that the sum of the first N Fibonacci numbers plus one, (F1+F2+F3+...+FN) + 1, seems to be the same as the (N+2)nd Fibonacci number. That is, he forms the hypothesis that
(F1+F2+...+FN)+1=FN+2
for all N > 1. Joe decides to use a spreadsheet to test his hypothesis. In cell C3, the command =SUM($B$3:B3)+1 is entered into the spreadsheet and copied to each entry in the column below it. In cell D3, Joe enters =B5 to represent the quantity FN+2. He also copies this formula to each entry in the column below. In this way Joe is easily able to check his hypothesis in the cases when N=2, N=3, N=4, and so on. Figure 9.3 shows how Joe used the spreadsheet to his advantage.
Walking
Exercise 9.1 Generate a table of values with a spreadsheet to guess the value that FN-1/FN approaches as N gets large.
Exercise 9.2 The purpose of this exercise is to form a hypothesis about a pattern -- an activity in which scientists are always engaged.
(a) Generate a table of values with a spreadsheet to guess the value that FN/FN-2 approaches as N gets large.
(b) Generate a table of values with a spreadsheet to guess the value that FN/FN-3 approaches as Ngets large.
(c) Guess the value that FN/FN-4 approaches as N gets large.
(d) Use the information from the above problems to hypothesize the value that FN/FN-k approaches as N gets large for k=1,2,3,...
Exercise 9.3 Create a spreadsheet which uses Binet's formula to generate the first 25 Fibonacci numbers explicitly. For which Fibonacci number, if any, do you notice the first discrepency between the way the spreadsheet computes the Fibonacci number explicitly and the way it computes the number recursively?
Technical notes: To compute the value of a number such as 
on a spreadsheet, use the formula
=(1+SQRT(3)) ^ 7.
You will also probably want to FORMAT the cell widths so as to output very large numbers.
Jogging
Exercise 9.4 If is the golden ratio, use a spreedsheet to compute the values of
.
for N=2, 3, 4,..., 20. Make a conjecture based on these values.
Exercise 9.5 The Lucas numbers, denoted LN, are defined recursively by L1=1, L2=3, andLN=LN-1+LN-2
for N > 3.
(a) Use a spreadsheet to generate the first 25 Lucas numbers.
(b) Generate a table of values with a spreadsheet to guess the value that LN/LN-1 approaches as N gets large.
(c) Use a spreadsheet to help make a hypothesis as to what value LN/LN-k approaches as N gets large for k=1,2,3,....
Exercise 9.6 Use a spreadsheet to make a hypothesis as to what value LN/FN approaches as N gets large. Then, make a hypothesis as to what value FN/LN approaches as N gets large. To try to figure out the exact value that this ratio seems to be approaching, try inputing a few (16) digits into the Inverse Symbolic Calculator.
Exercise 9.7 The Padovan numbers are defined recursively by P1=0, P2=1, and P3=0 and
PN=PN-3+PN-2
for N > 4.
(a) Use a spreadsheet to generate the first 25 Padovan numbers.
(b) Generate a table of values with a spreadsheet to approximate the value that PN/PN-1 approaches as N gets large. Because of its similarities with the golden ratio, the actual value that this ratio approaches has been dubbed the silver number by Midhat Gazale.
Exercise 9.8 [Excursions, Chapter 9, Exercise 9] By generating a spreadsheet with one column containing a list of the first 20 Fibonacci numbers FN and another column representing the first 20 squares N2 (starting, of course, at N=0), guess how many values of M and N there are that satisfy the equation
FN=M2.
How is it that you are so confident that there are no others? How would you test your hypothesis with a spreadsheet?
Exercise 9.9 [Excursions, Chapter 9, Exercise 14] With a spreadsheet, verify the hypothesis that
for N=1,2,...,8.
Exercise 9.10 [Excursions, Chapter 9, Exercise 15] With a spreadsheet, verify the hypothesis that for any four consecutive Fibonacci numbers FN,FN+1,FN+2, and FN+3, it follows that
2FN+2-FN+3=FN
for N=1,2,...,8.Exercise 9.11 [Excursions, Chapter 9, Exercise 16] With a spreadsheet, verify the hypothesis that for any four consecutive Fibonacci numbers FN,FN+1,FN+2, and FN+3, it follows that
for N=1,2,...,8.
Exercise 9.12 [Excursions, Chapter 9, Exercise 64] With a spreadsheet, verify the hypothesis that
F1+F3+F5+...+F2N+1=F2N+2
for N=3,4,...,10. Note: We are adding the Fibonacci numbers with the odd subscripts up to 2N+1.
Exercise 9.13 [Excursions, Chapter 9, Exercise 66] With a spreadsheet, verify the hypothesis that for any four consecutive Fibonacci numbers FN,FN+1,FN+2, and FN+3, it follows that
for N=3,4,...,10.
Exercise 9.14 [Excursions, Chapter 9, Exercise 19]
With a spreadsheet, generate approximate values (rounded to the nearest integer using the ROUND command) of 
for N=2,3,...,15. Can you form a hypothesis based on your output? Technical note: To round a number such as 3.14 to the nearest integer, use the formula =ROUND(3.14,0).
Exercise 9.15 Design a spreadsheet that allows the user to input values a, b, and c into three cells and will output to two other cells the approximate roots of the equation ax2+bx+c=0. Technical note: To compute a value such as 
, use the command =SQRT(7). Test your design using the values
(a) a=1, b=2.01, and c=1.
(b) a=1, b=2, and c=1.
(c) a=1, b=1.99, and c=1.
Why do you think you got the output you did from the third set of coefficients? Can you draw any conclusions about how sensitive solutions to the quadratic equation are to changes in the values of the coefficients?
Exercise 9.16 [Excursions, Chapter 9, Exercise 68] Choose your two favorite positive integers c and d. Define a new "Fibonacci-type" sequence by letting T1=c, T2=d and
TN=TN-2+TN-1
for N > 3. Use a spreadsheet to generate the values of TN for N=3,4,...,15 and use this information to guess the value that TN+1/TN approaches as N gets large.
Running
Exercise 9.16 Consider the recursively defined sequence given by G0=0, G1=1 and
GN=GN-1+GN-2+k
for N > 2 where k is a positive integer.
(a) For k=1, generate the first 25 terms for GN using a spreadsheet.Exercise 9.18 If
(b) For k=1, use the spreadsheet to compute GN/GN-1 for N > 2. Put these values in column C of the spreadsheet. Can you use the output of the spreadsheet to guess the value of GN/GN-1 as N gets large?
(c) For k=2, generate the first 25 terms of this sequence and guess the value of GN/GN-1 as N gets large.
(d) For k=3, generate the first 25 terms of this sequence and guess the value of GN/GN-1 as N gets large.
(e) For general k, form a hypothesis about what value GN/GN-1 approaches as N gets large. Test your hypothesis using the spreadsheet.
is the golden ratio, use a spreedsheet to compute the values of
is the silver number, use a spreedsheet to compute the values of
for N=1,2,3,...,20. Make a conjecture about how the silver number p relates to the Padovan sequence (See Exercise 9.7) based on these values.
A Pearson Company Legal Notice |