Getting Started with Spreadsheets
Stephen Arnold
Spreadsheets have long been a standard mathematical software tool, “borrowed” from the world of business and put to good use for the teaching and learning of mathematics. They bring both advantages (being able to see “behind the scenes”, exposing the “skeleton” of many complicated mathematical processes) and disadvantages (the syntax and formatting of spreadsheets can place an additional barrier between students and the mathematics they are attempting to learn).
Nonetheless, spreadsheets are here to stay and, used carefully, can add much to student learning. Like other valuable mathematical software tools, however, spreadsheets have made far less impact upon mathematics classrooms than might have been expected over the past fifteen years or so, largely due to the fact that they have been on computers, and so not readily available in most school situations. The release of the CellSheet™ App by Texas Instruments for both the popular TI-83 Plus series, as well as the powerful CAS calculators (TI-89 and Voyage™ 200) promises much. At last, students have the power and flexibility of the spreadsheet on hand at school or home, and teachers may readily refer to these tools as well as display their applications at any time, using ViewScreen™ and overhead projector.
The following activities provide a simple starting point for teachers and students wishing to take advantage of these exciting tools for mathematics teaching and learning.
Learning Activity 1: Introducing CellSheet™
At first sight, the CellSheet™ App can appear a little daunting. The “help” screen, which appears on start-up, provides much information in a very small space. However, the basic principles are simple, consistent in most respects with standard spreadsheet formatting on a PC.
As with a computer-based spreadsheet, three types of objects may be entered into a cell:
1. numbers (which may be entered just as they occur),
- text, which must be preceded by inverted commas (ALPHA-+), and
- formulas, which must be preceded by an equals sign. Since there is no equals sign on the 83 Plus keypad, the STO-> key has been reserved for this purpose.
The only other special character required by a spreadsheet is the $ (used for “absolute cell references – explained later). For this symbol, use the yellow 2nd-STO, or RCL.
A nice “getting started” activity involves setting up a sheet for evaluating a function (as shown), which may then be graphed, or examined numerically (noting that the graphing and table facilities of the graphing calculator actually make such a task much easier and, for practical purposes, more trouble than it is worth!).
From the sample shown, the headings (X, F(X), START and STEP) are all entered after inverted commas (ALPHA-+). Note that finishing inverted commas are NOT required.
This sheet will be “automated”: the starting value and step-size will be controlled from the two cell values in column C: changing these will cause the rest of the sheet to update automatically. The actual values, 0 and 1, are simply entered directly into cells C2 and C4. Cell A2, then, receives a simple formula which directs it to look into cell C2 for its value.
A more complicated formula is required for cell A3. This directs the sheet to look into the cell above (in this case A2) and then add the value found in cell C4. The $ signs make this value absolute: this formula will always look into cell C4, no matter where it is copied on the sheet. However, the value for A2 is relative: it simply says, “Look in the cell above” and draw the value from that.
This difference between absolute and relative cell values lies at the heart of using spreadsheets most effectively, and will become more clear in the next section, where we copy the formula from cell A3 and paste it into a range of cells below.
The CUT, COPY and PASTE commands are activated using the calculator function keys – the blue “graphing keys” across the top of the keypad. Look closely above Y= and you will see (in green) the label F1. Similarly, each of the five blue keys is labeled from F1 to F5. F3 (ZOOM) is the “copy” key. Pressing F3 copies the formula from the active cell (in this case A3), and activates menu commands for the remaining steps in the process, as shown.
Copying a formula into a range of cells is an easy three-step process.
- Press F3 to copy the required formula.
- Press F1 to mark the beginning of the range of cells to receive the formula, then scroll down with the blue “down arrow” to the end of this range (in this case, to around 10 value, to cell A12).
Press F4 to PASTE the formula into the selected range of cells.
To jump back to the top of the sheet, one screen at a time, use the green ALPHA key with the up-arrow.
Now, enter a function formula into column B, cell B2, such as the one shown. This takes the value of cell A2, multiplies it by 2 and subtracts 1.
Now, try the three-step process once again to copy this formula into the cells in column B, from B2 to B12. (Remember: F3 to copy, F1 to select the range, and F4 to paste!)
If we wish now to change the starting value or the step size, we need only change a single cell value, and all else updates automatically!
F5 activates the MENU, giving access to file management functions, as well as useful additional options, which include a variety of graphing options, and useful facilities for importing from and exporting to lists. This feature can be most useful when collecting data, using a data logger (CBL2™ or CBR™). The data may be imported from lists into the spreadsheet, and then transferred to computer as a single spreadsheet file, which may be easily imported into Microsoft Excel®.
Learning Activity 2: Staying Interested
AIM To further extend students’ skills in the use of a spreadsheet and their knowledge and understanding of simple and compound interest.
METHOD Set up an automatic interest calculator using CellSheet™.
PROCESS Create a new spreadsheet, called INTEREST. We will use this to compare the differing effects of simple and compound interest. We will also create a spreadsheet which is completely controllable for all its values.
Set up the new sheet as shown. The only formula is the one displayed in A6. Notice that this formula is =A5 + $B$3. The $ sign is accessed using 2nd-STO-> (or RCL) and indicates that this formula will always look precisely at the cell B3, no matter how or where it is copied to. This will become clearer after copying the formula in A6 into the cells from A6 down to A15. (Remember: F3 -> F1 (Scroll down) -> F4). As you arrow back up from A15, you will notice that the first cell reference changes at each cell (thus, the original A5 becomes A6, A7 and so on. This is called a relative cell reference.
As the formula is copied down the column, the instruction of such a reference is to always look in the same relative position – in this case, look in the cell directly above for the value). The dollar signs indicate an absolute cell reference: this instructs the cell to always look for its value in the same place: in this case, cell B3.
Return to the top of the sheet. We will now create columns for Simple Interest (S.INT), Simple Interest Total (S.TOT, the sum of the principal and the interest earned), Compound Interest (C.INT) and Compound Interest Total (C.TOT).
For Simple Interest, we enter into cell B5 (as shown), the formula =$B$1*$B$2*A5. Can you see why? The simple interest formula is PRN, the product of the Principal (or Present Value, PV), the interest RATE and the TERM (or N, number of interest periods). In this case, both PV and RATE are absolute references while N is a relative cell reference. COPY and PASTE this formula into the cells from B5 to B14.
For Simple Interest Total we need only to add the interest found in column B to the original investment amount in B1. Hence, the formula will be =$B$1 + B5.
Next move to column E: we will complete the Compound Interest Total first, since the compound interest formula produces this amount. This formula is given by P(1 + R)N, and so we enter: =$B$1*(1 + $B$2)^A5.
Finally, go back to cell D5 and enter =E5 - $B$1, since the actual interest earned by the compound interest method will be the total less the original amount.
You have now created a fully automatic interest calculator. Changing any of the control values (PV, RATE, STEP or the first N value) and all the values in the sheet will update. Use the sheet to find the difference in interests for an investment of $20 000 at 4.5%pa after 8 years. (NOTE: If you wished, you could now easily create a new column for this difference. Note also, to enter 4.5% simply enter it as 4.5/100 and let the spreadsheet do the calculation!).
What about monthly interest calculations, weekly or even daily calculations? First, enter the interest rate over 1200 instead of 100. Thus, for 5%pa monthly, enter 5/1200. Next, adjust the starting value of the term to whatever year you desire multiplied by 12. Thus, to find the monthly interest after 40 months (3 years and 4 months) enter 3 x 12 instead of 0 in A5, and then read off the required values!
Finally, the spreadsheet also allows graphical comparisons. Press F5 (MENU, GRAPH) and choose option 4: Charts… We will choose 3: Line Chart for this information, completing the entries as shown below. We use the number of years (or interest terms) as our Xrange (A5:A14) and the actual interest amounts (rather than the total amounts) for our comparison (columns B and D). Choosing DrawFit allows the calculator to automatically choose a scale and axes which will display the data. This may be manually controlled from the Charts screen, option 4: Line Window.
Press CLEAR to return to the spreadsheet, and change the step from 1 to 5. In this way, we compare the differences, not over 10 years but over 50! All the previous settings are preserved, so simply return to Charts, choose Line and go down to DrawFit to observe a more dramatic comparison!
