The Spreadsheet: A Tool for Mathematics
Spreadsheets are an often-overlooked tool that can be very useful for mathematics. This demonstration will show five applications, and the innovative mathematics teacher will find many more. Note: These examples were generated with Micorsoft Excel; other common spreadsheets are available in Microsoft Works, Claris Works, and many others.
The examples represented here are Representing
Data, Algebraic Operations, Equations,
Linear
Regression, and Curve Fitting.
The simplest use of spreadsheets is to represent data in tabular and graphic forms. Students collect data and enter it into a table. They may then select from a variety of different forms of graphs, including picture, bar, column, pie, and line. These representations aid the student in constructing and internal and external representation of the context, and in reasoning with the data and drawing conclusions.
The following is a sample line graph
constructed to compare average monthly temperatures in five cities.
In each cell of the spreadsheet, there are three options for data entry: numbers entered are treated as numbers, an entry preceded by the equal sign is a formula, and an entry preceded by a quotation mark is a label. When entering a formula, the data in other cells may be referenced by name A1, A2, etc. or by clicking in that cell to input it. A cell referenced by A4 is considered relative, and in the next row, it would use B4. To keep a value absolute (not dynamic), use the dollar sign. A$4$ would always use the value of A4. Or A$4 would use the value of A4 or A5, etc. A large number of functions (including math and statistical functions) are built into the program, and may be accessed from Insert-Function.
The following example solves equations with the quadratic formula.
The spreadsheet can also be used in a manner similar to a graphing
calculator to graph equations. One great benefit to the teacher is
that these equations can easily be printed.
Directions for Excel:
1.Enter x and y data
2.Highlight data
3.Choose Chart
4.Select XY(scatter)
5.Finish
6.Chart - Add trendline - (Select Linear or Polynomial, etc)
7.Options - Display equation on chart.
The spreadsheet is also an excellent tool for linear regression.
The easiest method is to graph a set of data points and then ask for a
line of best-fit, called a trendline. Excel will draw the least-squares
regression line and give its equation. (This method works equally
well for nonlinear functions.)
Directions are the same as for graphing linear equation:1.Enter x and y data
2.Highlight data
3.Choose Chart
4.Select XY(scatter)
5.Finish
6.Chart - Add trendline - (Select Linear or Polynomial, etc)
7.Options - Display equation on chart.
This method uses the "Solver" add-in. It gives a clear picture
of the meaning of least-squares. Add-ins are included with the Excel
program, but may have to be installed from the Tools menu.
Directions:1.Enter x and y data
2.Make up a value for m and b
3.Calculate y-predicted = mx+b
4.Calculate the difference in y and y-predicted
5.Square this value
6.Sum these squares
7.Highlight x, y, and y-predicted, and graph the scatterplot (2)
8.Tools - Solver
9.Target Cell - Sum of the differences squared - min
10.By changing m and b values
11.Solve. You will see m and b change, and the plot will change also.
12.Does not have to be linear. Other types of models are possible. (See step 3)
Other Web Resources for Teaching with Spreadsheets:
Leah P. McCoy
Associate Professor of Education
Wake Forest University
