Series and Sequences in Spreadsheets
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.
If you hit enter, it would calculate that cell (and in the current example would display as the value 1):
If you copied cell B1 and pasted it into cell B2, the displayed value would be based on the cell to its left:
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:
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.
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:
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:
Hitting enter on the expression in B1 shows the correct value:
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):
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:
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:
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:
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 * 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
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
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:
Copy that down the column, and each term of the sequence is generated using the value of the previous term:
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
after 1 year,
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
You can use a little algebra to factor out the preceding year's cost to get
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
for the next year, and in cell B3 put the expression
(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.
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.
Therefore, the cost doubles in 18 years, when the item will cost about $206.12.
- 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.
- The first term of a sequence is 1, and each term after the first is twice the preceding term. Find the first ten terms.
- 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.
- The first term of a sequence is 500, and each term
after the first is 120% of the preceding term. Find the first
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
- 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?
- 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
- 3% ?
- 6% ?
- 9% ?
- 12% ?
- 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?
- 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?
- 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.)
- 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?
- 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.
- Find the first 30 terms of the sequence defined by a1 = 4; an = 1.025an−1, n >1 .
- 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