from Educators' eZine
If you have numerical data of any variety, Microsoft Excel is a great way to plot a graph – be it a Bar Graph, Pie Chart, or Scatter Plot! Excel's next capability is to develop a mathematical model from the data and run simulations. How easy is it to do this in Excel? Well, if you can plot a graph, you can draw a regression line or trendline as it is called in Excel. And, with a little work, you can animate the mathematical relationship to create a simulation of the model. This article will introduce you to all these aspects and link you to the resources to build interactive Excel spreadsheets or Excelets. All of this can be done without any programming skills, just computation (formulas) in the spreadsheet. Excelets run the gamut from the elementary classroom through college level topics and allow you to create an engaging pedagogy for students that supports national standards in mathematics, science, and technology.
In Excel we create a manipulable variable to modify data as part of a formula. The graph of this data, as well as a regression line or any calculated parameters will then respond. We can manipulate variables by typing in cells or using a number of features available in Excel. The "Fraction Spreadsheet," whose screenshot appears here illustrates this. Using spinners to click up or down, we can change the fraction's numerator or denominator and get a graphical response. Other topics include making change with coins, exploring the relationship between perimeter and area for squares and rectangles, and comparing temperature scales. Using Excelets as a learning tool does not require students to have major experience with Excel, just an introduction on how to use the various interactive features and how to explore variables. Reinforcing the use of mathematical language with linear graphs may be required too, as it always is even with college students!
Another approach is the "just add data" spreadsheet where a series of cells have been set up for data to be added. Some simple examples include counting the various colors of M&M's and generating a pie chart or looking at the calorie content of meals from the protein, carbohydrate, and fat content of labels. The downloadable .pdf "Investigating the Height of a Stack of Cookies" illustrates this using experimentally collected data which your students can model on the first day of class. A graph and associated analysis will automatically occur on entry. This allows quick building of a model with further understanding developed later. Two simulations are also included to check student understanding of the meaning of slope, and to see how variation causes scatter in the data. The table to the right provides Excelets to get your students into mathematical modeling. The screenshot to the right shows a tracer point moving along a line (by clicking on a scroll bar) to help students distinguish between interpolation and extrapolation. Exploring extrapolation and its limitations are crucial as many models breakdown at higher levels of the x-variable.
Topic
Excelet
Activity
Your first model from data
Learning how linear regression works in 5 minutes
—
Difference between interpolation and extrapolation
—
Scatter in data and outliers
Explore types of error in data
Building simulations of mathematical models allows exploration through numerical experimentation and developing "what if" scenarios. Students can derive ideal models from them and then explore how random and systematic errors influence the model to get a real-world treatment. This is scientifically backwards, but allows novice learners to discover correct models, especially where experimentation is not easy. For an interactive Excel spreadsheet investigation of radioactive decay and associated measurement error, see "Exploring Radioactive Decay in Excel: An Interactive Visual Thinking Tool". Many dynamic topics in chemistry are reduced to static presentations in textbooks and many lectures as well; however, they can be brought alive for exploration via predict, test, and observe, as can be seen in "Chemical Excelets".
The wherewithal for producing Excelets can be found at the "Developer's Guide to Excelets", which includes a tutorial, illustrated instructions, and many more examples.
Take the "Interactive Features in Excel" tour to see what you can do in Excel. The forms toolbar provides a variety of features (spinners, scroll bars, checkboxes, etc.) that are easy to use; and when combined with logical functions, lookup tables, conditional formatting, and a number of simple tricks provides a wealth of interactivity and dynamic display. All of this is done using computations (a.k.a. - formulas and available functions) in the cells. The use of comment boxes adds explanation, hints, and answers for students as well. Always look under the graphs, as many of the tricks, such as turning lines on graphs on and off or tracer points are explained there. If you are just getting into Excel, see the downloadable .pdf file "Using Excel for Handling, Graphing, and Analyzing Scientific Data" to gain experience and as a resource for your students.
Excel is a powerful off-the-shelf tool for visualization and to engage your students in "click-and-think" activities. Higher-order thinking processes, algebraic thinking, and science processes are all addressed via numerical experimentation and "what if" scenarios. Additionally, students can develop Excelets as part of projects, providing great dynamic presentations in the classroom. Excelets are great tools for instructional design and addressing multiple intelligences too! Let your classroom evolve from "chalk-and-talk" to "click-and-think" with the use of Excelets.
Email:Scott Sinex
