W. Gary Martin home page > CTSE 7970 > Lab 1

CTSE 7970, Summer 2001
Lab 1 – Spreadsheets

We begin our focus on spreadsheets. These exercises are designed with Microsoft Excel, although they will work in most spreadsheet programs with only minor modifications.

1.      Sequences. Let’s create the most simple sequence in the world: 1, 2, 3, …

a.      Type “1” in cell A1 (that is, column A and row 1). In cell A2 (right below A1), type “=A1+1”, meaning add one to the value in cell A1. Now highlight the cells from A2 to A20 or so. Then under the Edit menu, choose “Fill Down”. Magic!

b.      OK, now let’s start the sequence at 100. Click in cell A1 and type 100. Presto!

c.      Now let’s change the difference to 2. Edit cell A2 to be “=A1+2” and fill down.

d.      Change the values so that positive even numbers are generated.

e.      Generate a sequence that starts at 30 and goes back by 2’s until it reaches 0.

f.       Generate a sequence that starts at 1 and so that each term is double the previous term.

g.      Now let’s try something fancy… The Fibonacci sequence:  1,1,2,3,5,8,… (Note that you will have to enter 1 for both cells A1 and A2. A3 is then computed, and you can fill down from there.)

2.      Defining constants. Let’s set up an arithmetic sequence so that you can easily change the common difference. Open a new window.

a.      First, put the common difference (D) in a cell at the top of the sheet. Put “D” in A1 and put 3 (for now) in cell B1. Let’s start the sequence in A3, say 5. Now in cell A4, enter “=A3+B1” to get the next term, then fill down. WHAT WENT WRONG?

• The problem is that Excel (and most spreadsheet programs) copy formulas relative to the current cell. For example, click on cell A5 and look at its formula at the top of the sheet. Instead of “=A4+B1”, which is what we were hoping for, it says “=A4+B2”, since it took the cell in the same relative position to A5 as B2 was to A4.
• The solution? Simple. We need an absolute reference, denoted using a dollar sign before the row and column. In A5, type “=A4+$B$1”. Now fill down and you are set!
• NOTE: One of the dollar signs can be omitted. Which one is definitely needed?

b.      Now change the value in B1 to 7 and watch the rest of the values change!

c.      Now set up a general geometric series. You can either open a new sheet or add this to the current sheet.

3.      Naming constants. To make things a little clearer, we can give a constant a name.

a.      Using the spreadsheet from #2, place the cursor on cell B1 and under Insert, choose “Name… Define”. It will suggest that you name it D, since that is the label in cell A1. Change the formula in cell A4 to “=A3+D”. Fill down to be sure it works.

b.      Redo your geometric sequence as well, using “Ratio” as the name of the constant.

c.      If you haven’t done so, save your sheet(s) from parts 1 and 2.

d.      Start a new. Define constants a, b, and c in their own cells. Then set up a sequence that begins at a, and whose terms are found by multiplying the previous term by b and adding c. What kind of sequence is generated?

4.      Series. Let’s now do a series, whose nth term is the sum of the first n series of a sequence, using the sheet you started in #2.

a.      We’ll add a column beside our arithmetic sequence. The first term of the series will be in B3 and will be equal to A3; enter the appropriate formula. Add a formula for cell B4 so that it is the sum of the first two terms, and fill down.

b.      Pick values for the first term and common difference so that the values for the series are perfect squares (e.g., the nth term is n²). So that nth term is 2n². So the nth term is n²+1.

c.      Now set up a geometric series using your sequence from #2. What fun!

5.      Functions. Here’s another way to generate a series.

a.      Click on cell C9. Type “=sum(A3:A9)”. What happens?

b.      Try filling it down and explain what occurs. Fix your formula so that it works properly. (HINT: The first cell in the range should be an absolute reference.)

c.      There are other built-in functions, such as AVERAGE that can be useful. Find the average of the first n terms of your geometric sequence.

6.      A practical example. Set up a simple spreadsheet to compute grades.

a.      Student names go in column A, beginning in row 3. Enter four students into your class.

b.      Names of assignments go in row 1, beginning in column B. Possible points go in row 2. Enter five assignments, with titles and point values.

c.      Now give each student a grade for each assignment.

d.      Make the seventh column the total points column, computing the total points possible and the points each student has earned.

e.      Compute percents in the eighth column. Choose Cells from the Format menu, and set the Number type to be percent.

f.       You may want to use the toolbar to center, bold, or add borders to enhance readability.

g.      Now in the eighth row, calculate the average score for each assignment. And in the ninth row, compute the average percent.

Here is a sample:

 

h.      Something to Remember: Try to insert a sixth assignment (homework 3). What went wrong? By always having an empty last column (and row) just before the totals, you can make it much easier to insert a new assignment (or student). That is, don’t ever fill in that last column of row!