Series and Sequences in Spreadsheets

Robert P. Webber, Scott McElfresh, Longwood University
 

Review of spreadsheet basics

 

A spreadsheet is a rectangular grid of cells.  A cell is identified by its column (a letter) and row (a number).  For instance, cell  B3  refers to the cell in column  B  and row  3.

 

 

A

B

C

D

1

 

 

 

 

2

 

 

 

 

3

 

X

 

 

4

 

 

 

 

 

 

 

 

        Cell B3

 

 

You can specify a group of contiguous cells by giving the addresses of the first and last cells, separated by a colon.  For instance,   A1:A4  refers to the first four cells in column  A, and  B2:F2  to the second through the fifth cells in row  2.

 

 

A

B

C

D

E

F

1

X

 

 

 

 

 

2

X

 

 

 

 

 

3

X

 

 

 

 

 

4

X

 

 

 

 

 

5

 

 

 

 

 

 

 

            Cells A1:A4

 

 

 

A

B

C

D

E

F

1

 

 

 

 

 

 

2

 

X

X

X

X

X

3

 

 

 

 

 

 

4

 

 

 

 

 

 

5

 

 

 

 

 

 

 

            Cells B2:F2

 

 

Cells usually contain data, such as text or numbers, or formulas.  If you highlight a cell by clicking on it and begin typing, what you type will be entered as data in the cell.  If you precede the data by typing an equality sign  = , what you type will be entered as a formula and result in a calculation.

 

For instance, suppose you enter some numbers in cells  A1, B1, and  C1, and the formula  =A1+B1+C1  in cell  D1.  The data will be displayed in the first three cells, and sum of the data in cell D1.

 

 

A

B

C

D

E

F

1

4

-6

18

16

 

 

2

 

 

 

 

 

 

3

 

 

 

 

 

 

4

 

 

 

 

 

 

5

 

 

 

 

 

 

 

The numbers  4, -6, and 18 are entered in cells A1, B1, and C1; the formula  =A1+B1+C1  is entered in cell  D1.

 

 

To see the formula that was typed in cell  D1 , move the cursor to cell  D1 and click.  You will see the formula displayed in the formula bar at the top of the spreadsheet.

 

 

Copying formulas

 

Once you have entered a formula in a cell, you can copy it to another cell by copying and pasting it.  You can also copy it by clicking and dragging.  To click and drag, move the cursor to the cell with the formula.  In Excel, put the cursor on the lower right corner of the cell.  You will see the thick cross change to a smaller, black line cross.  Click on the corner of the cell when the black line cross appears and hold the mouse button down as you drag the mouse over the cells into which you wish to copy the formula.

 

When you copy a formula, either by copying and pasting or by clicking and dragging, the cell references of the formula’s operands are automatically adjusted.  For instance, if the formula in cell  C1  is   =A1+B1 , and you copy this formula to cell C2 , the formula in  C2  will be  =A2+B2 .

 

For example, suppose there are values in cell  A1  through  A4, and you put the formula  =A1 / 2  in cell  B1.

 

 

 

A

B

1

2

1

2

-3

 

3

8.6

 

4

91

 

5

 

 

 

Data is in cells  A1  through  A4; the formula  =A1 / 2  in cell B1.

 

If you click and drag the formula down column B, the results of dividing each cell in column  A  by  2  will be displayed.  If you put the cursor in cell  B4 , say, you will see the formula  =A4 / 2  in the formula bar at the top of the screen.

 

 

A

B

1

2

1

2

-3

-1.5

3

8.6

4.3

4

91

45.5

5

 

 

 

The cells in column  B  have formulas.

 

 

Recursive sequences using spreadsheets

 

Automatic updating of addresses in formulas allows us to investigate and solve many problems that involve performing the same calculation over and over with different data.  For instance, suppose the first number in a sequence of integers is  10, and each term after the first is  80%  of the preceding term.  The first several terms are

 

            10

            10 * 0.8 = 8

            8 * 0.8 = 6.4

            6.4 * 0.8 = 5.12

 

and so on.  Such a sequence, when each term after the first is a function of one or more preceding terms, is called a recursive sequence in mathematics, and mathematicians would write it as   a1 = 10;  an = an-1 * 0.8, n > 1    .

 

Suppose we want to list the first  15  terms of this sequence.  Calculating them by hand, or even using a calculator, would be tedious.  A spreadsheet makes it easy.  Just put the first term in a cell.  In the next cell, write the formula

 

            = preceding_cell_address * 0.8

 

where you substitute the address of the first cell for  previous_cell_address.  Then click and drag the formula over the next  14  cells.

 

Here is the result.  For readability, I put the numbers of the terms in column  A, the first value of the sequence in cell  B1, and the formula in  B2.

 

1

10

2

8

3

6.4

4

5.12

5

4.096

6

3.2768

7

2.62144

8

2.097152

9

1.677722

10

1.342177

11

1.073742

12

0.858993

13

0.687195

14

0.549756

15

0.439805

 

 

 

Now let’s see a practical application.  Suppose the annual rate of inflation is  4.1%.  If an item costs  $100  now, what will it cost ten years from now?  In how many years will its cost double?

 

To say “the annual rate of inflation is  4.1%” means that each year, the cost of the item is  4.1%  more than it was in the preceding year.  An item costing  $100  in year  1  costs

 

            100 + 100 * .041 = $104.10

 

in year  2,

 

            104.10 + 104.10 * 0.41 = $108.37

 

rounded to the penny in year 3, and so on.  The cost in any year after the first is the preceding year’s cost plus the preceding year’s cost times  4.1%, which gives the formula

 

            = preceding_year’s_cost + preceding_year’s_cost * 0.041 .

 

You can use a little algebra to factor out the preceding year’s cost to get

 

            = preceding_year’s_cost * 1.041 .

 

In mathematical notation, a1 = 10;  an = an-1 * 1.041, n > 1  .  Now put the year in  A1  and the initial cost in  B1.  In cell  A2, put the formula

 

            =A1 + 1

 

for the next year, and in cell  B2  put the formula

 

            = B1 * 1.041

 

(or the formula  = B1 + B1 * 0.041 , if you didn’t do the algebra!)

 

Move the cursor to cell  A2, click it, and hold the mouse button down as you move to the right to highlight cells  A2 and  B2.  Release the button and move the cursor to the bottom right corner of cell  B2  to get the small black line cross.  Then click and drag the  A2:B2  block vertically down the  A  and  B  columns.  Here are the first ten lines of the result.

 

1

100

2

104.1

3

108.3681

4

112.8112

5

117.4365

6

122.2513

7

127.2637

8

132.4815

9

137.9132

10

143.5676

 

 

In year  10, the item will cost  $143.57, rounded to the penny.

 

Continuing to click and drag, you will see the original amount exceeds  $200  in year  19.

 

11

149.4539

12

155.5815

13

161.9604

14

168.6007

15

175.5134

16

182.7094

17

190.2005

18

197.9987

19

206.1167

20

214.5675

21

223.3647

 

Therefore, the cost doubles in  19  years, when the item will cost  $206.12.

 

 

Exercises

1.         The first term of a sequence is  1, and each term after the first is half of the preceding term.  Find the first ten terms.

 

 

2.         The first term of a sequence is  1, and each term after the first is twice the preceding term.  Find the first ten terms.

 

 

3.         The first term of a sequence is  16, and each term after the first is  ¾  of the preceding term.  Find the first  20  terms.

 

 

4.         The first term of a sequence is  500, and each term after the first is  120%  of the preceding term.  Find the first  16  terms.

 

 

Problems 5 and 6 deal with compound interest.  If interest is compounded at  r%  per period, then each period interest of  r%  times the preceding amount is added to the preceding amount.  For example, if interest is  5%  compounded annually, and  $600  is deposited, the amounts at the start of each year are

 

            Year    Amount at start of year

 

              1        $600 

              2        600 + 600 * .05 = $630

              3        630 + 630 * .05 = $661.50

 

and so on.

 

5.         A bank advertises interest at  5.15%  compounded annually.  If you deposit  $5000, how  much will you have at the start of year 6; that is, after  5  years?

 

 

6.         A  7  year bank certificate of deposit pays  6.2%  interest compounded annually.  If you deposit  $10,000 , how much will you have at the start of the eighth year; that is, after  7  years?

 

 

In problems 7 through 10, in how many years will prices double if the annual rate of inflation is

 

7.         3% ?

 

8.         6% ?

 

9.         9% ?

 

10.       12% ?

 

 

11.       Bacteria in a culture are increasing at the rate of  7%  per hour.  That is, each hour  7%  more bacteria are present than the previous hour.  If  5000  bacteria are initially present, how many will be present in  6  hours?  In how many hours will the number of bacteria double?

 

 

12.       A population of animals is increasing at the rate of  3%  per year.  That is, each year  3%  more animals are present that the year before.  In how many years will the initial population double?  triple?

 

 

13.       A radioactive substance is decaying (losing radioactivity) at the rate of  1%  per decade.  This means that each decade, the amount of radioactivity present is  99%  of the preceding decade’s radioactivity.  If the amount initially present is  0.1  gram, how much will be present after ten decades?  How many years will it take for the radioactivity to decay to half the initial amount?  (A decade is 10 years.)

 

 

14.       A conservation group estimates that forests in a developing region are shrinking at the rate of  10%  per year.  That is, the group estimates that  90%  of the preceding year’s forest acreage remains each year.  If the group is correct, in how many years will the forest coverage be only half the initial amount?

 

 

15.       The Fibonacci sequence is defined as follows.  The first two terms are each  1, and each term after the second is the sum of the two preceding terms.  Find the first  40  terms of the Fibonacci sequence.

 

 

16.       Find the first  30  terms of the sequence defined by  a1 = 4;  an = 1.025an-1, n >1  .

 

 

17.       A sequence is defined by  a1 = 4; a2 = 5; an = 3*an-1 -2*an-2, n>2  .  Find the first  25  terms of this sequence.

Credits and licensing

This article is by Robert P. Webber and Scott McElfresh, licensed under a Creative Commons BY-SA 3.0 license.

Version 2015-Aug-22 18:30