Series and Sequences in Spreadsheets

Robert P. Webber, Scott McElfresh, Don Blaheta, Longwood University

Copying expressions

Why are we working on this? Data analysis often involves working with a sequence of data where each element needs to be processed in the same way; simulations and what-if analyses may need to generate a sequence of numbers, each predictably based on the previous one. Spreadsheet expressions have the power to do both of these things. Skills in this section: Express repeated computation and sequences — Distinguish relative cell references from absolute cell references — Interpret results of sequential computation Concepts: Algorithm representation, Interpretation of computed results

Once you have entered an expression in a cell, you can copy it to another cell by copying and pasting it. Normally, when you do this, the expression that's pasted is adjusted to account for its new location; this is because spreadsheets by default use relative positioning: if cell D3 contains an expression that refers to C3, for instance, that reference really means "the cell immediately to the left of this one". If it were copied and pasted elsewhere, the row-column label would be replaced with that of the cell immediately to the left of the new location.

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

A B C D
1 2 =A1/2
2 −3
3 8.6
4 91
5

If you hit enter, it would calculate that cell (and in the current example would display as the value 1):

A B C D
1 2 1
2 −3
3 8.6
4 91
5

If you copied cell B1 and pasted it into cell B2, the displayed value would be based on the cell to its left:

A B C D
1 2 1
2 −3−1.5
3 8.6
4 91
5

And if you investigated the expression now stored for cell B2, you would find that it is =B1/2.

When you have the same expression to use for an entire row or column, you don't need to separately paste each cell. One way to quickly handle it is to copy the original, then highlight all the cells that expression is to be used in, then paste. Another is to "click and drag": put the cursor on the lower right corner of the cell. You will see the thick cross cursor change to a smaller, black line cross. (The exact details of the cursor may vary in different spreadsheets and different versions, but there will generally be some visual change.) 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 expression. This is equivalent to highlighting the cells and then pasting, but may be more convenient.

If you do this on our example spreadsheet, the expression will be copied down (with adjustments for relative positioning) and the whole column will display half the value of the column to the left:

A B C D
1 2 1
2 −3−1.5
3 8.64.3
4 9145.5
5

Since all the cells in column B are expressions, changes to the numbers in column A will cause the corresponding cells in column B to recalculate and display the new value. Note, though, that once the expression has been copied and pasted, changes to the original expression in B1 will not be automatically propagated to the other cells in column B.

Absolute positioning

So, what if you wanted to try a bunch of different divisors? If you changed B1 to divide by 4 instead of 2, only that cell would change:

A B C D
1 2 0.5
2 −3−1.5
3 8.64.3
4 9145.5
5

You could paste or drag the new expression each time you adjusted the divisor, but that quickly gets tedious (and is error-prone). Instead, it's reasonable to specify the divisor as a parameter, a value that is given a label and can be changed separately without rewriting an expression. A first attempt at this strategy might look like this:

A B C D
1 2 =A1/D2 Divisor
2 −3 4
3 8.6
4 91
5

Hitting enter on the expression in B1 shows the correct value:

A B C D
1 2 0.5 Divisor
2 −3 4
3 8.6
4 91
5

But if we paste this expression down the column, we get a bunch of error messages (the exact message may vary depending on your software):

A B C D
1 2 0.5 Divisor
2 −3#DIV/0! 4
3 8.6#DIV/0!
4 91#DIV/0!
5

The problem is that the relative positioning adjustment is causing the parameter reference, to cell D2, to get rewritten as a reference to cell D3, then D4, and so on: the expression stored for B4 at this point is =A4/D5. (The references to blank cells, when used in an arithmetic expression, get interpreted as zeroes, hence the specific complaint here is a division-by-zero error.)

When we are writing expressions like this that refer to a specific parameter, we require absolute positioning. In this example, when we refer to cell D2, we want to always refer to D2, no matter what row we're in. To mark a reference as absolute, we precede its row and column with dollar signs:

A B C D
1 2 =A1/$D$2 Divisor
2 −3 4
3 8.6
4 91
5

Then, when I hit enter and copy it down the column, the relative reference (A1) is adjusted, and the absolute reference ($D$2) is not:

A B C D
1 2 0.5 Divisor
2 −3−0.75 4
3 8.62.15
4 9122.75
5

The expression stored for B4 is now =A4/$D$2. Changes to the divisor value stored in cell D2 will now update all the computed values:

A B C D
1 2 −1 Divisor
2 −31.5 −2
3 8.6−4.3
4 91−45.5
5

Recursive sequences using spreadsheets

Automatic updating of addresses in expressions 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 numbers 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 expression

= preceding_cell_address * 0.8

where you substitute the address of the first cell for previous_cell_address. Then click and drag the expression over as many cells as you want to generate.

In this case, I can put the first term in A1, then write an expression in A2 that refers to A1:

A B
1 10
2 =A1*0.8
3

Copy that down the column, and each term of the sequence is generated using the value of the previous term:

A B
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 initially (when zero years have passed) costs

100 + 100 * .041 = $104.10

after 1 year,

104.10 + 104.10 * 0.41 = $108.37

rounded to the penny after 2 years, 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 expression

= 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, a0 = 100; an = an−1 * 1.041, n > 0 . Now put the number of years passed in A2 (0, to begin with) and the initial cost in B2, and labels in A1 and B1. In cell A3, put the expression

=A2 + 1

for the next year, and in cell B3 put the expression

=B2 * 1.041

(or the expression =B2+B2*0.041, if you didn't do the algebra!)

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

A B
1 Yrs Cost
2 0 100
3 1 104.1
4 2 108.3681
5 3 112.8112
6 4 117.4365
7 5 122.2513
8 6 127.2637
9 7 132.4815
10 8 137.9132
11 9 143.5676

After 9 years, the item will cost $143.57, rounded to the penny.

Continuing to click and drag, you will see the original amount exceeds $200 in row 20, where the first column indicates that 18 years will have passed.

12 10 149.4539
13 11 155.5815
14 12 161.9604
15 13 168.6007
16 14 175.5134
17 15 182.7094
18 16 190.2005
19 17 197.9987
20 18 206.1167
21 19 214.5675
22 20 223.3647

Therefore, the cost doubles in 18 years, when the item will cost about $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 3/4 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, Scott McElfresh, and Don Blaheta, licensed under a Creative Commons BY-SA 3.0 license.

Version 2016-Feb-1 03:00