Center Home -> Content Areas Home -> Math Home -> Project Activities -> Excel Activities ->

Fibonacci-Like Sequences and the Golden Ratio

Activity Description Activity Guide


Part 1: An Exploration of a Male Bees' Genealogy
  • Discuss the following scenario in small groups.

Scenario: "A drone comes from an unfertilized egg; it has a mother but no father. A female bee has both a mother and father" (NCTM. January 1996 NCTM Student Math Notes). Trace back 10 generations of bees.  Note how many family members are in each generation.

  • Discuss the results in pairs. How is the family of bees "growing" in each successive generation? What patterns are emerging? How are the patterns related to the fact that a male bee has only 1 female parent, but a female bee has 2 parents?

  • Predict how many bees there would have been if you go back one more generation. Develop a rule for predicting how many bees there would have been in successive earlier generations.  Express this rule in symbolic form.

Part 2: An historical look at Fibonacci and the sequence

The pattern of numbers (1, 1, 2, 3, 5, 8, …) that emerged in the genealogy of a male drone bee has some historical roots. Leonardo of Pisa (or Fibonacci "son of Bonaccio") first wrote about this pattern of numbers after working on a fictitious problem about constraints on rabbit reproduction.

  •  Use a spreadsheet to record the reproduction of rabbits for an entire year.  Use the following headings to help you organize the information.

  A B C D
1 Month Old Pairs of Rabbits New Pairs of Rabbits Total Number of Pairs of Rabbits
2 0 1 0 1
3 1 1 0 1
4 2 1 1 2
5        

What patterns do you notice?  How many pairs of rabbits were there after 1 year?  Write a general rule to continue this pattern.  Estimate how many rabbits you think there would be after 2 years.  Justify your reasoning.

  • Create a formula to use in each column in the spreadsheet to continue modeling the rabbit reproduction.  Use the Fill command to generate the values in each column through 24 months. Compare this value with your estimation.  Is the sequence of numbers in column D (Total Number of Pairs of Rabbits) an arithmetic sequence? A geometric sequence? What are the defining characteristics of arithmetic and geometric sequences?

  • Select column D (Total Number of Pairs of Rabbits) and create a line graph. The sequence appears to grow exponentially. Recall the general form of an exponential function y = a(b)x . How does this compare with the form of a geometric sequence? Use Excel to calculate an exponential regression with the y-intercept equal to 1 (see tip box on next page). Interpret the equation and the r2 value (y = e0.432x and r2= 0.9869). Is Fibonacci’s sequence perfectly exponential? Why or why not?  Notice that Excel uses a form (y = c(e)dx ) different from the standard form for an exponential function. Note the equation from the exponential regression. We will revisit this in a subsequent part to this activity.

To add an exponential trendline to a graph:
  • Click on the graph. Under the Chart menu, select Add Trendline…. In the pop-up dialog box, click on the Exponential icon.
  • Click on the Options tab. Set the Intercept equal to 1 (since the initial number of rabbits, c, equals 1). Also check the options to Display equation and Display R-squared.

  • Click on Ok. The graph of the regression line, the equation, and the r-squared value should appear on your graph.

Part 3: Phee-phi-fo-fum … growing ratios sure are fun!

  • In a new worksheet, enter the first two values of the Fibonacci sequence (1, 1) in cells B1 and B2. Create a formula in cell B3 that will generate the next term in the sequence. Use the Fill feature to recursively copy this formula through B24.

  • In Part 2 of this activity, we established that the sequence is only approximately exponential, and thus not quite a geometric sequence. Recall that a geometric sequence must have a common ratio between terms. Compute ratios between several successive terms in the Fibonacci sequence. In column C, create a formula to calculate the ratio of the current term to the previous term. Fill this formula down through C24. Is the ratio constant?

  • What happens to the ratio of successive terms as the sequence grows? Create a line graph of the successive ratios in column C to illustrate how the sequence of ratios grows. What does the numerical and graphical pattern suggest?

  • What do you expect to happen if you calculate the ratios in reverse (i.e., previous term to current term)? Discuss briefly in pairs. Then create this ratio formula in column D, Fill Down through D24, and add this sequence of ratios to your current line graph.

To add data to an existing graph:
  • Click on the graph. Under the Chart menu, choose Add Data…. In the pop-up dialog box, select the cells in column D containing the new ratios.
  • The following pop-up dialog may appear. If so, click OK to verify that the new data will be plotted as a new series.


  • What do you notice? How are the two sequences of ratios related? The limit of the first sequence of successive ratios is called the Golden Ratio and is noted with the mathematical symbol Φ (Phi). Phi is an irrational number. This ratio has many interesting properties and appears in many numeric and geometric contexts. One of its interesting numerical properties is that 1/ Φ = Φ – 1.  The limit of the second sequence of ratios is 1/ Φ, which is also known as phi (lowercase Φ , sometimes pronounced phee) and symbolized as φ. Thus, Φ and φ differ by 1 and are also reciprocals of each other. Pretty amazing for two irrational numbers that are nonrepeating and nonterminating!

  • Although the original Fibonacci sequence does not have a constant ratio with small n, as n approaches infinity, the ratio tends towards Φ. Thus, the exponential regression equation was a pretty good fit for the sequence (recall the strong r2 value).  Assume that the ratio was a constant 1.618. Then using the form y = a(b)x, the formula to generate the Fibonacci sequence would be approximately y = (1.618)x . Verify (numerically, graphically, and algebraically) that the equations y = (1.618)x and y = e0.432x are approximately equal.

Part 4: Fibonacci-like sequences “a-la-spreadsheet”

  • The French mathematician, Edouard Lucas (1842-1891), officially named the sequence 1, 1, 2, 3, 5, 8, 13, .. the Fibonacci Numbers. He also found another similar series: 2, 1, 3, 4, 7, 11, 18, ... . The rule of adding the previous two terms to get the current term is kept, but the sequence begins with 2 and 1 (in that order). Conjecture what will happen to the successive ratios when the first two terms of the sequence are 2 and 1. Then change the values of B1 and B2 in your spreadsheet to 2 and 1 respectively. Note what happens to the sequence, and to both sequences of successive ratios. Explain what is happening.

  • What happens to the sequence and the sequences of successive ratios if we try other values for the first two terms? For this part of our exploration, it will be helpful to create two scrollbars that will change the values of the first two terms to integer values between –10 and 10. (Recall that a scrollbar can only be set to integer values greater than or equal to 0.) Create two scrollbars that will control the values in cells A1 and A2, respectively.

Hint: For help on how to insert scrollbars, see the scrollbar tip in Part 4 of the Rational Number Relationships Excel activity.
  • Set the value of the scrollbars to range from 0 to 20.
  • Then set B1 = A1-10 and B2=A2-10. Now, changing the scrollbar should allow you to observe numerical and graphical changes in the sequences. 
  • Try these values for the first two terms (in order) and discuss your observations. As you progress through the list, predict what will happen before you actually change the value of the first two terms.

  • 1 and 2, then 2 and 1

  • 2 and 3, then 3 and 2

  • 0 and 1, then 1 and 0

  • -1 and 1, then 1 and –1

  • -3 and 2, then 2 and -3

  • -10 and 10, then 10 and -10

  • -5 and 5, then 5 and -5

  • What happens to the sequence and the successive ratios if you use rational numbers (e.g., 0.5, 1/3, 6.24) rather than integers? What happens to the sequence and the successive ratios if you use irrational numbers (e.g., π, e, or square root of 2) as the first two terms? What does this imply about the ratio of successive terms in any sequence generated with the following rule?

Wn­­+1 = Wn + Wn-1

  • What does this imply?  Prove that any sequence generated according to the rule, Wn­­+1 = Wn + Wn-1, will have sequences of successive ratios converging to Φ and φ.

Part 5: Suggestions for Further Exploration

  • Complete Part 3 of the Exploring Mathematics with Recursion activity in the Graphing Calculator section. What are the pedagogical benefits and drawbacks of using either the graphing calculator or a spreadsheet to investigate properties of the Fibonacci sequence and its successive ratios?

  • Explore the geometrical and art connections of the golden ratio by completing the Exploring the Golden Rectangle activity in The Geometer's Sketchpad section.

Extensions:

1. Read the following article:  Bradley, S. (2000). Generalized Fibonacci sequences. Mathematics Teacher, 93(7), 604-606.

·        Create an interactive spreadsheet to explore a more generalized version of the Fibonacci sequence that uses an arbitrary integer multiplier k.

The interactive spreadsheet should contain interactive cells for defining w0, w1 and the multiplier k. Design the spreadsheet so that a user can change the values of w0 and w1 by typing in a cell and can change k by using a scrollbar. Set the values of k to be integers in the range from -10 to 10.

  • Create a sequence of the successive ratios wn/wn-1  from the generalized sequence and make a line graph of these ratios.

  • Use your spreadsheet to investigate many real number values for w0, w1 and integer values of k between -10 and 10. What do you notice?

  • Use your graphing calculator or a spreadsheet to create a graph of the function for the nearly golden ratios   (Bradley, 2000, p. 606). Use a domain of -10 to 10. Also create a graph of the reciprocal of rk . How are these graphs related? Use the graphs and/or a table to confirm that  .

  • In a 1-2 page reflection, discuss the ideas presented in the article and how the spreadsheet template and the graphs you created could be used with students to investigate the generalized Fibonacci sequences and nearly golden ratios.

2. For those of you that are fascinated with Fibonacci numbers, check out the following resources on the internet.

http://www.mcs.surrey.ac.uk/Personal/R.Knott/Fibonacci/fib.html
This site has TONS of information on connections and applications of the Fibonacci sequence. This includes some investigations into connections between the Fibonacci numbers and the Lucas numbers!

http://www.sciencenews.org/sn_arc99/6_12_99/bob1.htm
A recent article about a mathematician who used technology to add an element of randomness to the Fibonacci sequence and the astonishing mathematical ideas that emerged! Try his ideas out with a spreadsheet and see what happens.




Back to Project Activities | Back to Math Homepage

Send questions or comments here.
Last modified on July 27, 2001.