Printer Friendly

Spreadsheets in mathematics: accessibility, creativity, and fun.

Abstract: A spreadsheet, such as Microsoft Excel, provides educators with a creative tool for the study and teaching of mathematics, mathematical modeling, and mathematical visualization. It enables students to gain mathematical insights into a diverse range of interesting and significant applications in an engaging setting while they simultaneously acquire practical skills in using the principal mathematical tool of the workplace. This paper illustrates novel ways to use this powerful and accessible tool and its outstanding graphic features creatively in teaching a surprising number of mathematically oriented topics. Illustrations come from such disciplines as the physical and social sciences, statistics, mathematics, computer science, and the arts. The paper describes how Excel's graphics can create eye-catching animated graphic displays and inject more fun into the study of mathematics.

1. Introduction

The computer spreadsheet is nearly 30 years old. During its lifetime, applications of this creative software have diversified and spread widely from the original uses in financial fields into all areas of human endeavor. Today it is the principal mathematical tool of the workplace. It is readily available on virtually every computer, with the most common version being Microsoft Excel. It also increasingly appears in the teaching and communication of mathematics at all levels. In this paper, we provide some visual glimpses into a broad range of applications of spreadsheets in mathematics education. Our examples use standard features and techniques of Excel in novel ways, thereby extending the range of mathematical topics that we can study in surprising and creative ways.

The spreadsheet offers many advantages for learning mathematical concepts. It is an easily accessible and creative tool whose basic operations are familiar to most students. Educators can use spreadsheets so that the actual process of creating a spreadsheet model itself teaches and reinforces mathematical concepts. In addition, the spreadsheet design often allows students to study successfully numerous topics ordinarily considered as being too difficult for them. Its use also provides students with valuable practical experience in working with a tool that they will use in their future jobs. Finally, the spreadsheet helps teachers and students to find the study of mathematics to be a fun experience.

Our approach to the use of spreadsheets contains an emphasis on designing effective graphics to promote the development of visualization skills. To do this, we present illustrative examples that describe creative ways to use spreadsheets to produce interactive animated graphics. Our examples come from a wide range of applications and disciplines. Each of these examples incorporates the use of mathematics. We have designed this paper to provide a visual overview of our approach. We create our examples using Microsoft Excel 2003. In addition, we give brief descriptions of the use of such visual tools as sliders and simple macros for animated graphics. Because of the limited space available in this paper, we provide extensive annotated Excel 2003 files for most of the examples of the paper on the journal Web site. We provide hyperlinks at the end of the paper.

Besides being working models of our examples, these files contain step-by-step descriptions of the methods that we use to create the models and visual features. These will assist readers in acquiring skills for creating their own animated models. We show some of the basic steps in creating these models in Section 11. Those who are interested in additional details of spreadsheet operation, such as the use of these and other animation tools and ways of using the Solver and Data Table, can also consult [8] and its attached CD, or download a paper and Excel application files from [2].

While we create our models using Excel 2003, these also will run on quite well on Excel 2007. In addition, we can create these examples directly in the newer program, which contains most of the features of the earlier version, although some of these may be more difficult to find (for example, sliders are on the Developer ribbon which may require an extra step to display, and graphing is on the Insert ribbon). Unfortunately, a few of the features of Excel 2003, such as the drag-and-drop techniques for graphing, have disappeared, although we can accomplish the same things with alternate tools and techniques. In Section 12, we look briefly at some of the aspects of Excel 2007 that relate to our approach.

A spreadsheet lends itself to a variety of educational uses. First, it provides educators with a natural way to implement mathematical algorithms and models and to create interactive graphs for use in student assignments and activities. In the latter case, it provides a way for students to work in groups on more substantial projects. Second, it enables teachers to prepare original and effective classroom demonstrations to illustrate mathematical ideas. In addition, it allows teachers to create visual models for most textbook topics, including those in algebra, calculus, statistics, numerical analysis, and linear algebra. Third, it can provide an avenue for the professional development of educators, opening opportunities for them to give professional presentations of new approaches to teaching and research. This applies not only to mathematics, but also to virtually any other discipline. Finally, it is an excellent avenue for teaching continuing education courses, and for communicating with the public and with colleagues from other disciplines. The author has used spreadsheets successfully in presenting mathematics to wide variety of adult learners in both developing and technologically advanced nations. Additional discussions of teaching uses of spreadsheets may be found in [1], [3] and on the Web [9], [10].

While this paper is about the creative use of a spreadsheet, we also realize that each of the topics can also be studied profitably using other mathematical software, each of which has its own strengths. However, often specialized software requires a more substantial time investment in becoming proficient in its use, and students are less likely to use it in future employment. Perhaps most importantly, using spreadsheets changes the message that we too often send to the public--that mathematics is different, difficult, and requires special tools. In fact, we can do very useful, creative, and fun things in mathematics using the widely used and popular spreadsheet.

2. Mathematical Modeling

Originally, the business community was the principal user of spreadsheets, employing them to create interactive financial mathematical models that they interrogated in a "What if ...?" manner. Over the ensuing years, many additional mathematical applications for spreadsheets arose, enabling their usage to expand into the design of mathematical models from diverse fields. As a result, today we use spreadsheets in a broad range of classrooms to study a wide and complex range of interesting mathematical models. Many topics that ordinarily we might regard as too advanced are now accessible to our students. The spreadsheet provides students with a tool that encourages creativity, and enables them to have fun while investigating significant mathematical ideas.

As our first example, in Figure 2.1 we present an ecological model that involves the study of the interaction of two competing species of animals. We base it on a more traditional presentation found in pages 41-45 of [7]. Here we consider two species of an animal, say gray and red, whose populations grow exponentially if there were no competition between them. However, since there is a species interaction effect that reduces population growth, this also is included in the model. For our spreadsheet descriptions, we use the arrow notation of [8]. In this notation, arrows point from the referenced cells to their locations within a formula. This frees us from the need to use physical cell locations to create or describe our models.

In Figures 2.2-2.3, we first enter the population sizes and growth rates, and then form a table that determines the sizes of the populations in successive time periods. After reproducing the initial populations in the top row of our table, we compute the number of new grey species as the product of their population and the growth rate. We indicate that the growth rate is an absolute reference by placing a pin in the source cell. We next compute the amount of grey population reduction that occurs due to competition between the species by assuming that it is proportional to the number of possible interactions between individuals (the product of the populations). We assume that studies have approximated the value of the parameter for the reduction rate. We then find the next period's population of the grey species in the following row as the previous population plus the new growth minus the species interaction reduction. We treat the red species similarly. We then simply copy the formulas. We supply additional details in an example file.

[FIGURE 2.2 OMITTED]

[FIGURE 2.3 OMITTED]

To produce an image to help us in visualizing the results, we create the xy-graph in Figure 2.4. It shows the population sizes over a sequence of periods. We see that with our assumptions, despite the fact that we start with a larger population of the grey species, the red species eventually drives out the grey one. Students can also discover that there are initial population values that will result in equilibrium, but one that is extremely unstable. The slightest changes cause the eventual extinction of one of the species. The graph in Figure 2.5 gives us a different way to visualize the results. Randomly placed markers represent the numbers of each species in period n. As the value of n increases, we see that the mixture of red and grey colors gradually change completely to red.

[FIGURE 2.4 OMITTED]

[FIGURE 2.5 OMITTED]

We display our output using xy-(or scatter) charts (Excel uses the term chart for its graphs). This type of graph plots points by (x,y) coordinates, with the x-values coming from the first column of data (here the values of n), and uses the other columns for y-values of functions. We use this chart type to create our mathematical graphs, as it can connect successive points by lines segments to create curves that appear as being continuous. To create Figure 2.4, we first use the mouse to select, the columns of data (Figure 2.6). We then click on the Chart Wizard button. In the resulting dialog box of Figure 2.7, we select the xy-chart type, and the subtype that plots only markers at the points. After completing the graph, we can reformat its features to obtain Figure 2.4.

[FIGURE 2.6 OMITTED]

[FIGURE 2.7 OMITTED]

To enhance our graphs, in Cell G2 we insert a parameter, n, for the current period number. We then use a table lookup function, =VLOOKUP($G$2,$A$7:$C$36,2), to get the coordinates of that period. The function looks for the value of G2 (or n) in the first column of the block A7:C36, and returns the value from column 2 of the block (i.e. the grey population), corresponding to the value of n. We replace the 2 by 3 to obtain the size of the red species. To complete Figure 2.4, we drag these coordinates into the graph as additional series to produce the heavier markers.

We further enhance our model by inserting a scroll bar (or slider), and use it to vary the value of the parameter n to visualize the changes in the populations over several periods. To create the slider, we enter the commands: View, Toolbars, Control Toolbox to generate the toolbar of Figure 2.8. Excel contains two scroll bars. We prefer this one, as graphic images update more smoothly with it as we move the slider.

[FIGURE 2.8 OMITTED]

We then click on the left button to enter the design mode and then click on the Scroll bar button. Next, we move the mouse pointer to position the slider in the sheet, hold down on left button, and drag out a rectangle as in Figure 2.9. We right-click in the resulting image and chose the Properties option, as in Figure 2.10. In the resulting dialog box, we type in G2 as the linked cell, and set the maximum value, Max, to 30, the number of data points. We then click on the left button to exit the design mode. This links the scroll bar to Cell G2, which contains the value of the period, n. As we move the slider in the scroll bar, n takes on non-negative integer values ranging from 0 to 30 in increments of size 1. We use the slider to vary n and observe the nature of the change in our animated graphs that result. We provide additional discussions of scroll bars in the example files.

[FIGURE 2.9 OMITTED]

[FIGURE 2.10 OMITTED]

Similar models and visualization techniques appear in [8], with investigations of such topics as population growth, resource harvesting, epidemics, predator-prey interactions, genetics, medicine dosage, financial analyses, projectiles, heat flow, apportionment, and planetary motion. Other possible topics include pollution, cooling, drug testing, and many more. These topics provide interesting group projects, allowing students to design creative implementations and graphics.

3. Graphing Functions

Spreadsheets are effective tools for creating animated graphs of functions. Here we create the graph of the polar equation cos(t / c). In Figures 3.1-3.2, we set the parameter, c, to 2. The left column counts degrees. We convert these to radians using Excel's RADIANS function in the second column, enter the formula for r in the third, and compute the values of x and y in the next two by x = r cos t, y = r sin t. We discuss the steps in more depth in Section 11, and in the Section 3 Excel file.

[FIGURE 3.2 OMITTED]

We use the 4th and 5th columns to create the complete xy-graph in Figure 3.3. However, it is easy to animate the tracing of the curve to appear much as we would draw it manually in class. We generate only the first N points of the curve by employing the IF ... THEN ... ELSE structure of Figure 3.2 in the two rightmost columns. These expressions reproduce the (x,y) values when n [less than or equal to] N, and otherwise copy the value from the cell above. We then link a scroll bar to N. As we move it, we see the curve being traced out. Many additional graphing applications appear in [4] and [8]. Students can use mathematical concepts to have fun in enhancing the model. In Figure 3.4, we use mathematics to create a simple image of a butterfly, and then scale, translate, and rotate it so that it moves at the trace point and remains tangent to the path. Using a few more mathematical ideas and spreadsheet techniques, students can color in the wings, cause them to flap periodically, draw parametric curves in the shape of flowers along the curve, and create a simple macro to continually update N, thereby creating a movie of the butterfly moving from flower to flower.

[FIGURE 3.3 OMITTED]

[FIGURE 3.4 OMITTED]

4. Numerical Algorithms

We can also implement the algorithms of numerical analysis effectively in a spreadsheet, including those for finding zeroes, numerical integration, solving differential equations, fitting curves, solving linear systems, and computing eigenvalues. We can also create animated versions of virtually every static diagram that appears in a text. These creations provide students with new ways to visualize the effects of changes in parameters, and to explore other aspects of numerical algorithms.

Teachers and students can also create animated demonstrations of algorithms. In Figures 4.1-4.3, we implement Newton's Method in a table format to find a zero of f(x) [x.sup.2] - 2. We first enter 0.4 as an initial estimate. We enter formulas for f(x) and f'(x) in the cells to the right. In the next row, we obtain the next approximation by x - f(x)/ f'(x). We then copy these expressions down their columns. From the resulting displays, we can observe the rate of convergence, experiment with the initial estimate and change functions. By designing other animated graphics as in [8], we can better illustrate functions in which the algorithm does not converge or is sensitive to the initial estimate. Such graphics generally attract more student attention than do traditional techniques.

[FIGURE 4.2 OMITTED]

[FIGURE 4.3 OMITTED]

Teachers also can design demonstrations to lead students through an algorithm in a step-by-step manner. Two steps in an animated construction updated by button clicks are in Figures 4.4-4.5.

[FIGURE 4.4 OMITTED]

[FIGURE 4.5 OMITTED]

5. Linear Algebra and Vectors

Excel provides built-in matrix functions for multiplication, inversion, and determinates (see [8], [2]). We can use these and other standard features of Excel to create interactive animated displays for a great range of ideas from the linear algebra of 2 , including such topics as the grids for various bases, eigenvalues, linear programming, pivoting, and the visualizations of linear transformations. We employed some of these techniques in creating the butterfly graph above and the reflection transformation shown in Figure 5.1.

[FIGURE 5.1 OMITTED]

In Figure 5.2, we use the ideas of vectors in illustrating pursuit problems, enhanced by forming xy-images of airplanes (see Figure 5.3), and then using linear algebra and vectors to scale, rotate, and translate the images. We first parameterize the path of one airplane, and then use vectors and a rotation matrix to cause another airplane to pursue the first as it moves directly toward the first in small discrete increments. We set the speeds of the planes as parameters, and use a scroll bar to vary time, causing the planes to trace out curves. We observe the resulting path of the pursuit plane and when, or if, it overtakes the first. Students can also discover and investigate other pursuit strategies.

[FIGURE 5.2 OMITTED]

[FIGURE 5.3 OMITTED]

6. Computer Science

We can also study a broad range of computer science concepts naturally on a spreadsheet. In addition to using the spreadsheet's built-in database features, students can discover creative ways to study such data structures topics as algorithms for sorting, searching, and creating stacks, queues, and trees with this widely used computing tool. The act of creating an implementation often enhances a student's understanding of the concepts. We also can create different ways to visualize traditional computer science topics.

In Figures 6.1-6.2, we show two steps in the iterative solution of the classical Towers of Hanoi problem. In this problem, we start with a number of disks of different sizes. We need to create an algorithm for moving the disks among the poles, one at a time, so that they end up on a different pole, and at each step no larger disk ever sits on top of a smaller one. Students in computer science are challenged to discover and illustrate both iterative and recursive algorithm to solve the problem. Our example provides an animated spreadsheet solution using an iterative algorithm. A version of this solution appeared in an early computer science journal [5] using the first spreadsheet, Visicalc, which did not contain graphics. Instead, at that time we used cell formatting and circular references to display the moving disks.

[FIGURE 6.1 OMITTED]

[FIGURE 6.2 OMITTED]

Figures 6.3-6.5 illustrate the continuous morphing of a butterfly into a rabbit. The concept of continuously modifying one curve into another using this technique is an important concept in the field of computer graphics. Both advanced and novice students can investigate this and other topics from the field of computer graphics using a spreadsheet.

[FIGURE 6.3 OMITTED]

[FIGURE 6.4 OMITTED]

[FIGURE 6.5 OMITTED]

7. Art and Culture

Another fascinating source for interest, fun, and enjoyment comes from examining the arts and cultures of different societies. For example, symmetry, proportion, and further aspects of geometry, as well as other mathematical ideas often appear in the design of national flags and traditional art. A few spreadsheet illustrations appear in Figures 7.1-7.4. A challenge to students is to animate the flags to simulate waving in the breeze. Doing this requires both inventiveness and mathematical insights. Other sources of cultural projects come from traditional arts and crafts, such as the design of quilts, needlepoint, string-and-nail art, bilums (net bags) of Papua New Guinea, and kilims (rugs) of Turkey. Using a spreadsheet to design these cultural images provides many students who otherwise may not be attracted to mathematics with an attractive pathway to encounter the subject through its geometrical aspects and the creative application of a popular and accessible technological tool. In addition, professional educators can create local cultural designs in their illustrations to enhance their international presentations. We provide a number of techniques and illustrations of how to add this coloring to xy-graphs in the example files.

[FIGURE 7.1 OMITTED]

[FIGURE 7.2 OMITTED]

[FIGURE 7.3 OMITTED]

[FIGURE 7.4 OMITTED]

8. Statistics

Despite current shortcomings in some of its statistical features, Excel is a valuable tool for learning the fundamental concepts of statistics. It invariably supplies us with several levels at which to approach a topic. Thus, we can pursue a concept such as the standard deviation either directly from definitions or by using built-in functions. In either case, we can supplement our models with effective original graphic visualizations.

One inventive way to display data in a map, as in Figure 8.1, is by using a bubble graph, where the geographical border is composed of tiny circles, and the circles for cities that are proportional to their populations. This also is an excellent way for students to create attractive scatter diagrams for topics of particular interest to them. One of the author's earliest statistical memories is of a magazine's graphic that showed the relative strengths of U.S. university football teams via scaled footballs in a map. We observe that we can create xy-charts of map boundaries in the same way.

Although we entered the coordinates of this map manually, some Web sites, such as the United States Census Bureau (http://www.census.gov/geo/www/cob/co2000.html), provide data sets that supply (x,y) coordinates of many cartographic boundaries, and freeware such as PlotDigitizer (http://plotdigitizer.sourceforge.net/) give us a more convenient way to generate more easily coordinates to digitize a wide variety of images.

[FIGURE 8.1 OMITTED]

Another versatile spreadsheet tool is the Solver. In Figures 8.2-8.4 we first employ the RANDOM function to generate 100 (x,y) points over a chosen range, and use the CORRELATION function to observe that the correlation coefficient, r, is nearly 0. We then convert the data values into constants and use the Solver to set the correlation coefficient to a chosen value (r = 0.8) by having the Solver change the x- and y-values. The spreadsheet makes the adjustments and produces a graph of a distribution giving the desired value of r. If we use 1 or -1 for r, then we will obtain a straight line.

[FIGURE 8.4 OMITTED]

9. Historical Topics

During the author's university studies and his early years of teaching, the principal computational tool was the slide rule. Most students today are completely unfamiliar with its operation and its use of logarithms. This and other historical ideas provide wonderful projects for making classes interesting and fun. In the display below, we use scroll bars to move the center rule and the crosshair. The display of Figure 9.1 also provides the underlying numbers to better show the ideas involved. Other historical topics for student projects include the abacus, Galileo's sector compass, Napier's bones, and the Galton board of statistics.

[FIGURE 9.1 OMITTED]

10. Creative Fun

Finally, students are generally quite adept at finding interesting and fun things to use in illustrating mathematical concepts. We show two examples in Figures 10.1-10.2. One of these is a carnival ride, called a Ferris wheel in the U.S. We create it using our earlier graphic techniques. We then use a rotation matrix to rotate the wheel through k degrees, and build a macro containing a loop to iterate the process many times, causing the wheel to rotate. The larger the value of k, the faster the wheel moves. Using a negative value for k reverses the direction of rotation.

A macro is a small user designed program. Excel uses the program Visual Basic. However, rather than needing to write programs in this language, to create our animation macros we employ Excel's macro recording feature that turns our spreadsheet operations into a macro. Then we make a minor modification to create a loop. Sources [2], [8], and the example file for Section 3 discuss our techniques for creating and using these simple macros.

Another interesting project is creating a traditional analog clock by using mathematics to insure that the second, minute, and hour hands advance appropriately. We then link the time to the computer's clock. Students can also draw a building, in which to display the clock, or modify the design for a counterclockwise moving clock, such as the historical "backwards" clock of Prague.

[FIGURE 10.1 OMITTED]

[FIGURE 10.2 OMITTED]

Another creative application comes from psychology, where optical illusions help in the analysis of visual perception. Students create designs using a spreadsheet, and manipulate them through sliders, spinners, and buttons. In Figure 10.3, the left and right red segments appear not to be in line. However, when we click a button to expose the connecting line segment in Figure 10.4, we see that, in fact, they do line in the same line. In Figure 10.5, we use a scroll bar to move the center arrow to try to divide the line into two equal parts. We seldom will be correct. Once again, we can see the correct location after pushing a button to display the correct location. In many illusions, color plays a vital role, and the spreadsheet provides us with a good tool for investigating this aspect. Consult [5] to see a vast array of examples of optical illusions to implement.

[FIGURE 10.3 OMITTED]

[FIGURE 10.4 OMITTED]

[FIGURE 10.5 OMITTED]

11. A Further Discussion of Fundamental Excel Operations

In this section, we present a few more of our basic techniques for entering formulas and creating graphs (see [2], [8]). For our illustrations, we expand upon the example of Section 3. In this example, we generate the graph of the polar equation r = cos(t / n). We begin by entering the value of the parameter c in Cell B1 (here, c = 2), and the number of degrees, step, between angles in our list, in Cell B2 (here, step = 1).

We generate degrees in Column A. We first enter 0 in Cell A4. Then, in Cell A5, we add the step size to the previous value. Here we describe our preferred way to do this, using the "gesturing" technique of [8]. We place the cursor in Cell A5, and type = to begin an equation. Next, rather than typing in a cell location, we use the mouse to click on the step size to get the expression =B2 (Figure 11.1). Because we want this to be an absolute reference that will not change in copying, we press the F4 key to obtain =$B$2. We then type + and click on the previous value of n in the cell above to obtain =$B$2+A4 (Figure 11.2). We note that there is no need for us to focus on the names of cell coordinates, because Excel takes care of that for us. We now press Enter to complete the expression. Excel then displays, the result, 1, on the screen.

To copy this expression down Column A, we can click on the fill handle at the lower right corner of Cell A5, hold down on the right mouse button, and drag the expression down as far as we desire (here we go until n = 720). If we look at the resulting formulas, we will see that one part remains fixed as an absolute reference, while the other part is relative, always referencing the cell above. Next, in Cell B4, we convert degrees to radians using the built-in Excel function RADIANS. We type =radians (and click on the cell to the left (Figure 11.3). Then we type) followed by Enter.

[FIGURE 11.1 OMITTED]

[FIGURE 11.2 OMITTED]

[FIGURE 11.3 OMITTED]

We now go to Cell C4 to enter the desired polar function. As we see in Figure 11.4, we first type =cos(and then click on the current value of t in the cell to the left to obtain =cos(B4 . Next, we type / and click on the cell containing the parameter c, pressing the F4 key to make the reference absolute, obtaining =cos(B4/$B$1 as in Figure 11.5. We then type) and press Enter to complete the formula.

[FIGURE 11.4 OMITTED]

[FIGURE 11.5 OMITTED]

We next convert our polar expression into rectangular coordinates. In Cell D4, we use our usual technique to create x as x = r cos(t), as shown in Figures 11.6-11.7, while in Cell E4 we compute y similarly as y = r sin(t).

[FIGURE 11.6 OMITTED]

[FIGURE 11.7 OMITTED]

Finally, we now need to copy these expressions down their respective columns. To do this we can highlight the block B4:E4 as in Figure 11.8, and drag down on the fill handle. Alternatively, we can double-click on the fill handle, and Excel copies the expressions down through the last row containing an entry in the adjacent Column A.

[FIGURE 11.8 OMITTED]

[FIGURE 11.9 OMITTED]

Now, we briefly describe a standard way to create mathematical graphs. The (x,y) coordinates for the graph lie in Columns D:E. We use the mouse to highlight these as in Figure 11.9, and click on the Chart Wizard button. Alternatively, we can choose the commands: Insert, Chart. In the ensuing steps of the Chart Wizard, we choose the graph type and subtype. To produce mathematical graphs, we use the xy-chart type. This plots points using the (x,y)-coordinates. We choose the chart subtype that plots markers at the points and connects consecutive points by line segments. We can later delete the markers or lines if desired. After we complete the graph, we can make a wide variety of modifications in its formatting.

Afterwards we may want to insert another series of (x,y) points into our existing graph. In this case, we want to create a second curve using Columns F:G. To do this using Excel 2003 (see Section 12 for a difference with Excel 2007), we use the mouse to highlight these columns, place the mouse pointer on an edge of the highlighted area, hold down on the left mouse button, drag it into the graph, and release the button (Figure 11.10).

[FIGURE 11.10 OMITTED]

[FIGURE 11.11 OMITTED]

We obtain the graph of Figure 11.11, where we see pink markers for the first N = 10 points. As N changes the number of points displayed changes. We can now reformat the second curve, and delete the first one if we desire. As in Section 3, the use of a scroll bar gives us a way to use the second curve in animating the development of the graph.

12. Excel 2003 vs. Excel 2007

One of the frustrating things that we often experience in using software is the need to adjust constantly to the appearance of new versions. Those of us who feel at home with Excel 2003 will find many changes in Excel 2007. We will notice that while there are some new helpful features, a few of the old useful features are missing. In addition, perhaps the largest adjustment will lie in adjusting to the new layout. In this section, we look at a few features of Excel 2007 that relate to the examples in this paper.

First, we see that Excel 2007's screen layout has a new approach. Rather than the familiar toolbars at the top of the screen, we see a display of ribbons. As we click on each one, we will see a new display of icons and commands. Figure 12.1 shows part of the Home ribbon. The entire ribbon is too wide for us to show here. In this display, the small icons at the top left are those put there by the author. A very useful button in the upper right corner (not shown) is help button, a circle with question mark on it. If we click on this, we see many helpful things, especially the option "What's New?" The material there will help long term Excel users adapt to the new version. We will find many other discussions on the Web.

[FIGURE 12.1 OMITTED]

As we use Excel 2007, at first some of the usual commands and icons, such as Save and Open, seem to be missing. We get to them by clicking on the large Office Button in upper left corner. If we click on this, we see in Figure 12.2 some common commands and our most recently accessed files. At the bottom, we see the Excel Options button. We click on that to find many things that we will need.

[FIGURE 12.2 OMITTED]

Here we show only a few of the most useful options. In Figure 12.3, the Popular option allows us to display the Developer tab if it did not appear in the original installation. This ribbon is vital for us, because it is where we find scroll bars and other control devices.

[FIGURE 12.3 OMITTED]

Another important category is the Add-In. Here we can choose to display various mathematics and statistics tools, such as the Analysis Toolpak and the Solver (Figure 12.4).

[FIGURE 12.4 OMITTED]

We now look at two more of the principal ribbons. On the Insert ribbon of Figure 12.5, we find the tools to create charts (i.e. graphs).

[FIGURE 12.5 OMITTED]

If we have displayed the Developer ribbon and click on it, then if we click on Insert in the Controls category, we find the scroll bar and other controls (Figure 12.6). There are two versions of the some of the tools. We use the ActiveX version for the scroll slider. If provides the smoother way to animate our models, although it is not as smooth as the Scroll Bar on the Control Toolbox in Excel 2003.

[FIGURE 12.6 OMITTED]

We close this section by listing some of the difference that we will encounter in creating examples like ours in Excel 2007.

* Excel 2007 allows us to create vastly larger files for our models. In addition, there are several new extensions for our files. The usual Excel 2007 creations have an extension of *.xlsx, while those including a macro will be saved as *.xlsm.

* In creating graphs, instead of clicking on a Chart Wizard button, we choose the Insert ribbon, and click on the xy-chart and select the desired subtype. Instead of selecting some formatting options at this stage, as in Excel 2003, Excel 2007 creates the chart immediately, after which we can modify the format. The new version provides us with a greatly expanded range of colors, lines, and other feature with which we can enhance our graphs.

* On the other hand, Excel 2007 eliminates the ability to add new series via the natural drag-and-drop technique. Instead, we can highlight the series, right-click on it and choose copy, click on the chart. In the Home ribbon, we then choose the option Insert, Paste Special.

* If we need to change the order of the series that make up our graph, we now right-click in the chart, choose the Select Data option, and reorder the series orders using the arrow keys.

* Although we do not discuss these features, Excel 2007 has removed the ability to manipulate date through points in a graph or to rotate 3-dimensional curves directly in graphs.

* We find scroll bars are under the options Controls, Insert on the Developer ribbon.

Links

Below we provide hyperlinks to Excel 2003 files for most of the models in this paper. These annotated files include step-by-step instructions for creating the models and their graphs, as well as the scroll bar and macros.

Section 2, Section 3, Section 4, Section 5, Section 6, Section 7, Section 8, Section 10

References

[1] Arganbright, Deane (2006). "Using Spreadsheets with Mathematically Gifted Students". Journal of the Korea Society of Mathematical Education, Series D: Research in Mathematical Education, Vol. 10, No. 1, March, 33-47.

[2] Arganbright, Deane (2005). "Enhancing Mathematical Graphical Displays in Excel through Animation". Spreadsheets in Education, Vol. 2, No. 1, November, 125-147.

[3] Arganbright, Deane (2005). "Developing Mathematics Creativity with Spreadsheets", Journal of the Korea Society of Mathematical Education, Series D: Research in Mathematical Education, Vol.9, No. 3, September, 187-201.

[4] Arganbright, Deane (1993). Practical Handbook of Spreadsheet Curves and Geometric Constructions. Boca Raton, FL: CRC Press.

[5] Arganbright, Deane (1986). "Spreadsheet Solutions for Mathematical Modeling Problems", Abacus, Vol. 4, No. 1, 24-27.

[6] Bach, Michael. 72 Optical Illusions, http://www.michaelbach.de/ot/.

[7] Giordamo, Frank, Maurice Weir, and William Fox (2003). A First Course in Mathematical Modeling, 3rd ed. Pacific Grove, CA: Thompson/Brooks-Cole.

[8] Neuwirth, Erich and Deane Arganbright (2004). The Active Modeler: Mathematical Modeling with Microsoft Excel. Belmont, CA: Thompson/Brooks-Cole.

[9] Neuwirth, Erich. Spreadsheets in Education, http://sunsite.univie.ac.at/Spreadsite/.

[10] Sugden, S, ed. Spreadsheets in Education, online journal, http://www.sie.bond.edu.au/.

Deane Arganbright

dearganbright@yahoo.com

Martin, Tennessee U.S.A.
Figure 2.1. Species Competition Output

         A            B            C            D

 1                  Initial          New     Interact
 2                Population      Growth       Effect
 3        Grey           145         0.24        0.003
 4         Red            79          0.3        0.002
 5
 6    period n     Grey Pop      Red Pop     New Grey
 7            1        145.0         79.0         34.8
 8            2        145.4         79.8         34.9
 9            3        145.5         80.5         34.9

         E            F            G

 1                        N       Period
 2                     Grey            20
 3                      Red          41.3
 4                                  204.9
 5
 6   Grey Loss      New Red     Red Loss
 7         34.4         23.7         22.9
 8         34.8         23.9         23.2
 9         35.2         24.2         23.4

Figure 3.1. Graphing Output

   c         2                N         4

step         1
    n        t        r        x        y        X        Y
    0        0        1        1        0        1        0
    1    0.017        1        1    0.017        1    0.017
    2    0.035        1    0.999    0.035    0.999    0.035
    3    0.052        1    0.998    0.052    0.998    0.052
    4     0.07    0.999    0.997    0.070    0.997     0.07
    5    0.087    0.999    0.995    0.087    0.997     0.07

Figure 4.1 Newton

Newton's Method

n          x          y         y'

 0        0.4      -1.84        0.8
 1        2.7       5.29        5.4
 2      1.720      0.960      3.441
 3      1.441      0.078      2.883
 4      1.414      7E-04      2.829
 5      1.414      7E-08      2.828
 6      1.414          0      2.828

Figure 8.2. Random Data

r       0.019

n          x          y
 1      -0.06      0.185
 2      0.468      -0.68
 3      -0.52      -0.05
 4      -0.11      0.872

Figure 8.3. Correlation Data

 r        0.8

 n          n          y
 1      0.032       0.16
 2      0.129      -0.43
 3      -0.53      -0.29
 4      0.316      0.805
COPYRIGHT 2007 Mathematics and Technology, LLC
No portion of this article can be reproduced without the express written permission from the copyright holder.
Copyright 2007 Gale, Cengage Learning. All rights reserved.

Article Details
Printer friendly Cite/link Email Feedback
Author:Arganbright, Deane
Publication:Electronic Journal of Mathematics and Technology
Article Type:Report
Geographic Code:1USA
Date:Oct 1, 2007
Words:6608
Previous Article:From string art to caustic curves: envelopes in symbolic geometry.
Next Article:Modeling intake and clearance of alcohol in humans.
Topics:

Terms of use | Privacy policy | Copyright © 2021 Farlex, Inc. | Feedback | For webmasters |