Exploring Spreadsheets in Mathematics

By

Brooke Norman

 

---

 

Program such as Excel are excellent spreadsheet programs that can be used to generate tables of numbers.

 

In this assignment, we are going to use Excel to generate a Fibonnaci sequence.

 

---

 

I used Microsoft Excel to generate a Fibonnaci sequence from n=1 to n=34.  You can see the sequence in the table below.  How did I do this?  First, I listed a number 1 in block A2.  In block A3, I entered a 1 also.  In block A4, I entered the formula =A2+A3.  This gives block A4 a value of 2.   I then highlighted the blocks in column A from A4 down to A34. I then used the “fill down” command to copy the formula into each of these blocks.

 

---

 

 I can check this to make sure it is correct.  Let’s choose block A23 for example.  We will add block A21+A22 and see if it gives us A23.  [6765+10946=17711].  If we look at the number in A23, the value is 17711.  The values are correct!   

 

---

 

I then found the ratio of each pair of adjacent terms in the Fibonnaci sequence.  This was done by entering a 1 in block B2.  In block B3, I entered the equation of =A3/A2.  I used the same technique of highlighting block B3 and dragging the highlight all the way down to B34.  Take a look at the different ratios.  It appears that one increases and the next decreases and continues in that pattern until they begin to reach common number.  This is called a limit. They seem to approach a limit of 1.618033989 as n becomes larger or approaches infinity. 

 

Fibonnaci Seq

Ratio

1

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

514229

1.618033989

832040

1.618033989

1346269

1.618033989

2178309

1.618033989

3524578

1.618033989

 

---

Next, I chose some arbitrary integers for f(0) and f(1), other than 1. I decided to make f(0)=4 and f(1)=5. Notice that in the first column, the values are very different from the values of the Fibonnaci sequence in the first table. But take notice that the ratio of the adjacent terms in the second column still approach the same limit of l.618, just like the Fibonnaci sequence.

 

Fibonnaci Seq

Ratio

Seq. 2

Ratio 2

1

1

4

 

1

1

5

1.25

2

2

9

1.8

3

1.5

14

1.555555556

5

1.666666667

23

1.642857143

8

1.6

37

1.608695652

13

1.625

60

1.621621622

21

1.615384615

97

1.616666667

34

1.619047619

157

1.618556701

55

1.617647059

254

1.617834395

89

1.618181818

411

1.618110236

144

1.617977528

665

1.618004866

233

1.618055556

1076

1.618045113

377

1.618025751

1741

1.61802974

610

1.618037135

2817

1.618035612

987

1.618032787

4558

1.618033369

1597

1.618034448

7375

1.618034226

2584

1.618033813

11933

1.618033898

4181

1.618034056

19308

1.618034023

6765

1.618033963

31241

1.618033976

10946

1.618033999

50549

1.618033994

17711

1.618033985

81790

1.618033987

28657

1.61803399

132339

1.618033989

46368

1.618033988

214129

1.618033988

75025

1.618033989

346468

1.618033989

121393

1.618033989

560597

1.618033989

196418

1.618033989

907065

1.618033989

317811

1.618033989

1467662

1.618033989

514229

1.618033989

2374727

1.618033989

832040

1.618033989

3842389

1.618033989

1346269

1.618033989

6217116

1.618033989

2178309

1.618033989

10059505

1.618033989

        3524578

1.618033989

16276621

1.618033989

                               

---

 

For the last part of this assignment, I explored a sequence where f(0)=1 and f(1)=3.  This is called a Lucas Sequence.  Take notice that as n gets larger or approaches infinity, the ratio reaches the same limit as the Fibonnaci sequence.

     

Fibonnaci Seq

Fib. Ratio

Lucas Seq

Luc. Ratio

1

1

1

 

1

1

3

3

2

2

4

1.333333333

3

1.5

7

1.75

5

1.666666667

11

1.571428571

8

1.6

18

1.636363636

13

1.625

29

1.611111111

21

1.615384615

47

1.620689655

34

1.619047619

76

1.617021277

55

1.617647059

123

1.618421053

89

1.618181818

199

1.617886179

144

1.617977528

322

1.618090452

233

1.618055556

521

1.618012422

377

1.618025751

843

1.618042226

610

1.618037135

1364

1.618030842

987

1.618032787

2207

1.618035191

1597

1.618034448

3571

1.61803353

2584

1.618033813

5778

1.618034164

4181

1.618034056

9349

1.618033922

6765

1.618033963

15127

1.618034014

10946

1.618033999

24476

1.618033979

17711

1.618033985

39603

1.618033992

28657

1.61803399

64079

1.618033987

46368

1.618033988

103682

1.618033989

75025

1.618033989

167761

1.618033989

121393

1.618033989

271443

1.618033989

196418

1.618033989

439204

1.618033989

317811

1.618033989

710647

1.618033989

514229

1.618033989

1149851

1.618033989

832040

1.618033989

1860498

1.618033989

1346269

1.618033989

3010349

1.618033989

2178309

1.618033989

4870847

1.618033989

3524578

1.618033989

7881196

1.618033989

---

 

In closing, we can say that the limit of the ratio for the adjacent terms of the Fibonnaci sequence and the Lucas sequence both approach 1.618, as well as, other similar sequences, as n goes to infinity.

---

 

Return to Brooke’s EMAT6680 Homepage.