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 yellow2, or^{nd}-STORCL.

## 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), STARTandSTEP) 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

C2andC4. CellA2, then, receives a simple formula which directs it to look into cellC2for its value.

## A more complicated formula is required for cell

A3. This directs the sheet to look into the cell above (in this caseA2) and then add the value found in cellC4. The$signs make this valueabsolute: this formula will always look into cellC4, no matter where it is copied on the sheet. However, the value forA2isrelative: it simply says, “Look in the cell above” and draw the value from that.

This difference between

absoluteandrelativecell values lies at the heart of using spreadsheets most effectively, and will become more clear in the next section, where wecopythe formula from cellA3andpasteit into a range of cells below.

## The

CUT, COPYandPASTEcommands are activated using the calculatorfunction keys– the blue “graphing keys” across the top of the keypad. Look closely aboveY=and you will see (in green) the labelF1. Similarly, each of the five blue keys is labeled fromF1toF5.F3(ZOOM) is the “copy” key. PressingF3copies the formula from the active cell (in this caseA3), 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
F3to copy the required formula.- Press
F1to 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 cellA12).## Press

F4toPASTEthe formula into the selected range of cells.

## To jump back to the top of the sheet, one screen at a time, use the green

ALPHAkey with the up-arrow.

Now, enter a function formula into column

B, cellB2, such as the one shown. This takes the value of cellA2, 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, fromB2toB12. (Remember:F3to copy,F1to select the range, andF4to 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!

F5activates theMENU, 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™orCBR™). 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

AIMTo further extend students’ skills in the use of a spreadsheet and their knowledge and understanding of simple and compound interest.

METHODSet up an automatic interest calculator using CellSheet™.

PROCESSCreate a new spreadsheet, calledINTEREST. 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 using2(or^{nd}-STO->RCL) and indicates that this formula will always look precisely at the cellB3, no matter how or where it is copied to. This will become clearer after copying the formula inA6into the cells fromA6down toA15.(Remember:F3 -> F1 (Scroll down) -> F4). As you arrow back up fromA15, you will notice that the first cell reference changes at each cell (thus, the originalA5becomesA6,A7and so on. This is called arelative cell reference.

As the formula is copied down the column, the instruction of such a reference is to always look in the same

relativeposition – in this case, look in the cell directly above for the value). The dollar signs indicate anabsolutecell reference: this instructs the cell to always look for its value in the same place: in this case, cellB3.

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) andCompound Interest Total(C.TOT).

For

Simple Interest, we enter into cellB5(as shown), the formula=$B$1*$B$2*A5. Can you see why? The simple interest formula isPRN, the product of thePrincipal(orPresent Value, PV), the interestRATEand theTERM(orN, number of interest periods). In this case, bothPVandRATEareabsolute referenceswhileNis arelative cell reference.COPYandPASTEthis formula into the cells fromB5toB14.

For

Simple Interest Totalwe need only to add the interest found in columnBto the original investment amount inB1. Hence, the formula will be=$B$1 + B5.

Next move to column

E: we will complete theCompound Interest Totalfirst, since the compound interest formula produces this amount. This formula is given byP(1 + R), and so we enter:^{N}=$B$1*(1 + $B$2)^A5.

Finally, go back to cell

D5and 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, STEPor the firstNvalue) 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.Notealso, to enter 4.5% simply enter it as 4.5/100 and let the spreadsheet do the calculation!).

What about

monthlyinterest calculations,weeklyor evendailycalculations? First, enter the interest rate over1200instead of100. Thus, for5%pa monthly, enter5/1200. Next, adjust thestarting valueof the term to whatever year you desire multiplied by 12. Thus, to find the monthly interest after 40 months (3 years and 4 months) enter3 x 12instead of0inA5, and then read off the required values!

Finally, the spreadsheet also allows graphical comparisons. Press

F5 (MENU, GRAPH)and choose option4: Charts…We will choose3:Line Chartfor this information, completing the entries as shown below. We use the number of years (or interest terms) as ourXrange(A5:A14) and the actual interest amounts (rather than the total amounts) for our comparison (columnsBandD). ChoosingDrawFitallows the calculator to automatically choose a scale and axes which will display the data. This may be manually controlled from theChartsscreen, option4: Line Window.

Press

CLEARto return to the spreadsheet, and change the step from1to5. In this way, we compare the differences, not over 10 years but over 50! All the previous settings are preserved, so simply return toCharts, chooseLineand go down toDrawFitto observe a more dramatic comparison!

For comments & suggestions, please e-mail Steve Arnold.