Spreadsheets in the classroom; using Supercalc to teach algorithms in mathematics. Samuel W. Spero.

Countless businessmen and -women have proved that the spreadsheet program on a personal computer is one of the quintessential problem solving tools in today's office. What many teachers of mathematics have yet to discover is that the same spreadsheet can be used as a problem solving tool in the classroom.

Students of mathematics have long struggled to learn the algorithms that govern numerical methods. Working out the problems by hand, even with a calculator, is often too time-consuming for all but the most trivial examples. Programming the algorithms in Basic, Fortran, or Pascal requires a knowledge of computer programming that many students lack. Using "canned" programs which require the student simply to enter data and equations does not help him learn the algorithm.

The electronic spreadsheet, however, offers an ideal compromise. It is a legitimate computer language which the student can use to articulate an algorithm. It eliminates tedious calculation. Yet it is as easy to use as paper and pencil.

The example I use to illustrate the use of the spreadsheet in the classroom in this article relates specifically to Supercalc. The concepts presented, however, can be applied to virtually any spreadsheet program with only minor modifications.

Getting Started

The spreadsheet may be thought of as a grid (see Figure 1). Columns are identified by letters, while rows are identified by numbers. The intersection of a row and a column, designated by the column letter and the row number (B24, for example, is the intersection of column B and row 24), is called a cell. All the action in a spreadsheet takes place in the individual cells.

The cursor, which can be moved about the spreadsheet with the arrow keys, marks the active cell. When something is typed on the keyboard, it appears in the active cell.

Whatever is typed on the keyboard in Supercalc appears on the third of three lines located just beneath the spreadsheet on the computer screen--the edit line. Special commands allow you to edit entries on this line both before and after the information has been entered into the active cell.

The first of the three lines is called the status line and shows the contents of the active cell. The amount of memory available is also indicated on this line.

The second of the three lines is the prompt line. Here you find prompts that identify the type of information that can be entered. For example, when using the FORMAT, LOAD/SAVE, or REPLICATE command, the various options available under each appear on the prompt line.

Solving the Equations

To enter alphabetic information, such as the title of your problem, you type a quotation mark followed by the information. To enter the information into the active cell, you press RETURN. Let's get started by giving a title to our problem: Solving a Set of 3 Equations in 3 Unknowns. See Figure 2.

The three equations are entered in the same manner. Make A6 the active cell and type "The Equations. Then type "3X+21Y+3Z=-30 into A8, "X+2Y-2Z=7 into A10, and "2X+8Y-Z=-2 into A12. Of course, you would press RETURN after each item to enter it, but I will not bother to mention that from now on.

Numbers can be entered directly without quotes. Enter the coefficients of the unknowns and values from the right-hand sides of the equations in matrix form as shown in Figure 3.

In addition to text and numbers, formulas and transformations can also be entered. The variables in the formula are identified by the cell coordinates (e.g., A17) of the values to be substituted into the formula.

The method we will use to solve the equations in this example is the Gauss-Jordan elimination technique. In this method, all but one of the unknowns are successively eliminated from each of the equations until what remains is a series of equations, each in a single unknown. Elementary row operations are used to reduce the matrix of coefficients to an identity matrix. The effect of this series of transformations on the augmented matrix is to transform the augmented column vector into the solution vector.

To facilitate the elementary row transformations for computer solutions, the diagonal element in a particular row is reduced to 1. This row is then used to reduce the other elements in the same column to 0. The procedure is repeated until the matrix of coefficients has been reduced to an identity matrix and the augmented column vector reduced to the solution vector.

Starting with row 1, the entire row is divided by the value of the a(1,1) element, which in our example is 1, as in row 24 of the spreadsheet in Figure 5. The simple formulas used to generate the values in Figure 5 appear in Figure 4.

The other elements in column 1 are then set equal to 0 by an elementary row transformation. The elements in the first row are multiplied by the additive inverse of the first element in the row being transformed, which in the example is -1. These elements in the first row are added to the elements in the row being transformed, the effect of which is to place a 0 in the a(i,1) position (where i is the number of the row). This procedure, which yields the formulas in rows 31 and 32 of the spreadsheet in Figure 6, is repeated for every element in the first column as in Figure 7.

In transforming the a(2,1) element, for example, we multiply the a(1,1) element by a(2,1), subtract, and get a difference of 0. We then repeat the transformation on every element in the second row. This transformation can be written as:

a(2,j)-a(2,1).sup.*.a(1,j) where j is the number of the column.

This formula must be replicated for all elements in the second row, i.e., for j = 1 to 3. We do this in Supercalc using the REPLICATE command. We need type only the formula for the first column; the formulas for the other columns can then be replicated with those factors that are to be treated relatively being treated relatively, and those factors that are to be treated absolutely not being changed at all. Larger nxn matrices are only slightly more time-consuming to calculate than our simple 3x3 example.

The other columns and rows in the matrix are dealt with in a similar fashion. Figure 8 shows the applicable formulas, and Figure 9 shows the results of the manipulations.

More Applications

The lesson to be learned from the example presented here is that virtually no knowledge of traditional computer languages is necessary to program the algorithm. Because the goals of mathematics instruction do not include programming as an instructional objective but do include practice in using algorithms to solve problems, the electronic spreadsheet can be a very useful tool for students.

Specific areas in which spreadsheets can be used in college algebra include the study of linear, quadratic, and general polynomial functions; matrices, and simultaneous equations. In calculus classes, spreadsheets can be used in the study of Newton's Method for solving non-linear equations, applications of the Trapezoidal Rule, Simpson's Rule, and other numerical integration algorithms; and infinite series. Techniques for solving differential equations and for determining inverses and determinants of matrices can also be explored using spreadsheets.