24. 10 Spreadsheets as cognitive tools

24. 10. 1 What Are Spreadsheets?

Spreadsheets are computerized, numerical record-keeping systems that were originally designed to replace paper-based accounting systems. Essentially, a spreadsheet is a grid, table, or matrix of empty cells with columns identified by letters and rows identified by numbers. Each cell may contain values, formulas, or functions. Numerical or textual data can be entered into each cell. Functions consist of mathematical or logical operations that also act on the values of the different cells, such as sum or average. Other functions automatically match values in cells with other cells, look-up values in a table of values, or create an index of values to be compared with other cells.

Spreadsheets have three primary functions: storing, calculating, and presenting information. Information, usually numerical, can be filed by a spreadsheet program into a particular location (the cell). This enables that information to be accessed and retrieved efficiently. Most importantly, spreadsheets support calculation functions. The numerical contents of any combination of cells can be mathematically related in just about any way the user wishes. Cells can be added, multiplied, and factored in any combinations of ways. Most spreadsheets provide mathematical functions such as logarithms and trigonometric functions. Contemporary spreadsheet software such as Microsoft Excel also includes sophisticated tools for generating tables and graphs.

Most spreadsheets support the entering of values with functions such as replication, whereby the program will fill in formulas in cells by replicating a formula in another cell. During spreadsheet construction, the author is not required to copy a similar formula over and over again in different cells. The spreadsheet can change the formula relative to the position of the cell as well. Many spreadsheet programs also allow users to write "macros," i.e., procedures for automating a series of spreadsheet functions by using a single command.

Spreadsheets were originally developed to support business decision-making and accounting operations. They are especially useful for answering "what if" questions; e.g., what if interest rates increased by 1%? Changes need to be made in only one location, and the spreadsheet automatically recalculates all of the affected values. Spreadsheets are powerful problem-solving tools. However, the difficulty in using spreadsheets for problem solving depends on the amount of abstractness and information processing the problem contains (Leon-Argyla, 1988).

24.10.2 How Are Spreadsheets Used as Cognitive Tools?

Spreadsheets may be used as a cognitive tool for amplifying and reorganizing mental functioning. Spreadsheets completely restructured the work of budgeting for managers and business people around the globe, enabling planners to be hypothesis testers (playing "what if?" games) rather than calculators (Pea, 1985). The unique power of spreadsheets is sometimes credited with spurring the remarkable growth of microcomputers, starting with the development of the Visicalc spreadsheet in 1978 (Ditlea, 1984).

In the same way that spreadsheets have qualitatively changed the accounting process, they can change any educational process that involves working with quantitative information. The Working Group for Technology of the National Curriculum Commission (1990) charged with framing the national curriculum in Great Britain has recognized the role of spreadsheets as tools that enable students "to use information technology to explore patterns and relationships and to form and test sample hypotheses."

Spreadsheets are rule-using tools that require that users become rulemakers (Vockell & van Deusen, 1989). Calculating values in a spreadsheet requires that the user identify relationships and patterns among the data that he or she wants to represent in the spreadsheet. Next, those relationships must be modeled mathematically, using rules to describe the relationships in the model. Building spreadsheets requires abstract reasoning by the user, thereby matching one of the important goals of cognitigve tools.

Spreadsheets support problem-solving actinties. Given a problem situation with complex quantitative relationships, spreadsheets can be used to represent those relationships. The "what if?" thinking that is best supported by spreadsheets is essential to decision analysis (Sounderpandian, 1989). Such reasoning requires learners to consider implications of conditions or options, thereby engaging higherorder thinking.

Identifying values and developing formulas to interrelate them in spreadsheets enhance learners' understanding of the algorithms used to compare them and also the mathematical models used to describe content domains. Students understand calculations (both antecedents and consequents) because they are actively involved in identifying the interrelationships between the components of the calculation. Spreadsheet construction and use demonstrate all steps of problem solutions, showing the progression of calculations as they are performed. The spreadsheet process models the mathematical logic that is implied by calculations. Making the underlying logic obvious to learners should improve their understandings of the interrelationships and procedures.

Numerous educators have explored the use of spreadsheets as cognitive tools. Spreadsheets have frequently been used in mathematics classes for such purposes as a calculator to demonstrate multiplicative relationships in elementary mathematics (Edwards & Bitter, 1989); for root finding in precalculus using synthetic division, bisection methods, and Newton's method (Pinter-Lucke, 1992); for helping students to understand the meaning of large numbers (e.g., a million) by comparing quantities to everyday things (Parker & Widmer, 1991); for solving elementary mathematical story problems in math classes (Verderber, 1990); for implementing linear system algorithms for solving advanced mathematical formulas (Watkins & Taylor, 1989); and for implementing Polya's problem-solving plan with arithmetic problems (Sgroi, 1992).

Spreadsheets have often been used to manifest quantitative relationships in various chemistry and physics classes, such as calculating the dimensions of a scale model of the Milky Way to demonstrate its intensity (Whitmer, 1990); solving complex chemistry problems such as wet and dry analysis of flue gases, which may be expanded to include volumetric flow rate, pressure, humidity, dew point, temperature, and combustion temperature in a mass and balances course (Misovich & Biasca, 1990); modeling the stoichiometric relationships in chemical reactions and calculating how many bonds are broken, the energy required to break bonds, and the new masses and densities of the products and reagents in the reactions (Brosnan, 1990); calculating the force needed to lift various weights in various level positions (Schlenker & Yoshida, 1991); solving rate equation chemical kinetics problems in a physical chemistry course (Blickensderfer, 1990); calculating and graphing quantum mechanical functions such as atomic orbitals to simulate rotational and vibrational energy levels of atomic components in a physical chemistry class (Kari, 1990); solving challenging science problems, including incline plane simulations and converting protein into energy (Goodfellow, 1990); solving physics laboratory experiments such as time, displacement, velocity, and their interrelationships using a free-fall apparatus (Krieger & Stith, 1990); and estimating and comparing the relative velocities of different dinosaurs (Karlin, 1988).

Spreadsheets are also useful in supporting social studies instruction, such as representing Keynesian vs. classical macro-economical models including savings-investment and inflation-unemployment relationships (Adams & Kroch, 1989); supporting decision analysis by helping users to find the best use of available information, as well as evaluating any additional information that can be obtained (Sounderpandian, 1989); interrelating demographic variables in population geography using population templates (Rudnicki, 1990); tracking portfolio performance in a stocktrading simulation (Crisci, 1992); and creating and manipulating economic models (e.g., balance of payments, investment appraisal, elasticity, and cost benefit analysis) in an economics course (Cashian, 1990).

Spreadsheets have been used in other disciplines as well. They have supported ecology education in the analysis of field data about tree species (Sigismondi & Calise, 1990) and the analysis of lunchroom trash to help students make projections of annual waste accumulation for an Earth Day project (Ramondetta, 1992). Spreadsheets have even been used to facilitate student grading of peer speech performances, providing a high level of motivation for students (Dribin, 1985).

It is sometimes useful to provide guided activities and problems to structure the use of spreadsheets. For example, to support higher-level thinking skills such as collecting, describing, and interpreting data, Niess (1992) provided students with a spreadsheet with wind data from various towns. Wind directions (NE, SW, WSW) described rows of data, with the percentage of days for each month of the year representing columns. She then asked students to use the spreadsheet to answer queries, such as:

• Are the winds more predominant from one direction during certain months? Why do you think this is the case?
  
• In which months is the wind the calmest?
• Which wind direction is the most stable during the year?

24.10.3 What Research Supports the Use of Spreadsheets as Cognitive Tools?

As with databases, there has been very little empirical study of the effects of using spreadsheets as cognitive tools. A few studies have examined the effects of different instructional treatments on learning to use spreadsheets (Charney, Reder & Kusbit, 1990; Kerr & Payne, 1994; Tiemann & Markle, 1990). These studies were not investigating the cognitive requirements or effects of using spreadsheets. Rather they were interested in the effects of different computer-based tutorial treatments, and spreadsheets happened to be the content or skill being learned. Baxter and Oatley (1991) compared the effectiveness of two different spreadsheet packages. Not surprisingly, the users' prior experience levels with spreadsheets was far more important to learning than the usability of the software package. These studies provide few insights about the effectiveness of spreadsheets as cognitive tools.

In one of the rare studies investigating spreadsheets as cognitive tools, Sutherland and Rojano (1993) were interested in how prealgebra students could use spreadsheets to represent and solve algebra problems. This study was conducted simultaneously in Britain and Mexico and took place over a 5-month period. During that time, students moved from a strict cause-effect local numerical notion of algebraic relationships to general rule-governed relationships that could be symbolized both in the spreadsheet and in algebraic notation. Another study used spreadsheets in community college math classes to help students solve linear and nonlinear equations problems (Hulse, 1992). Nonsignificant increases in mathematics achievement and decreases in numerical computation anxiety were reported; however, this study was so methodologically flawed by short treatment times and the use of inappropriate measures of achievement that it would be diffficult to generalize the results.

All of the literature that we found provides accounts of how to use spreadsheets in various curricular application, along with some occasional anecdotal support for their use. For instance, Kari (1990) reported from several years of student use that students can learn both spreadsheet construction as well as physical chemistry concepts when provided with partially completed spreadsheets or spreadsheet templates. So the use of spreadsheets as cognitive tools remains speculative. Research on the cognitive outcomes from using spreadsheets is needed before we can conclude that they can function as generalizable cognitive tools.