The golden rule in Google Sheets: to get an interactive version that you can edit, go to the File menu and select “Make a copy.” This saves the file to your Drive account.
Are you ready to see the power of spreadsheets for mathematical, statistical and scientific thinking? Let’s go down the path of using data to derive a mathematical model and then, using the model, to build an interactive simulation. tech-2022-Rosenberg-Big-data-big-changes-The-technologies-and-sources-of-data-used-in-science.pdf” target=”_blank”>A survey of 330 teachers I discovered that the number one data analysis tool was (wait for it) Google Sheets! MS Excel came in third place.
Spreadsheets are the low end of the computational food chain and can help your students get started with relatively simple calculations that can get really complicated if you want.
Google Sheets offers the great advantage of being free and Enables online collaborative efforts in the classroom and out-of-class projects.
Data: big and small
Pick yours up (first hand, you’re doing the measurements)
The collection of experimental data allows the introduction of simple measurement error analyzes using centimeter rulers such as Investigating the height of a stack of cookies (good first modeling activity with a “just add data” Excelet link). A good source of small (second-hand) real-world data sets is Quantitative Environmental Learning Project (QELP) website. You can download data as an Excel file which can be easily converted to a GSheets file without any issues.
Exploring measurement error
There are two main groups of errors, random and systematic (adds bias), and both can be incorporated into spreadsheet simulations, such as “just add data” spreadsheets.
So how do you integrate the error into a spreadsheet? Let’s examine an important measuring device, the ruler, using the Rules and measurement error Excelet to explore error types in various ways.
Large data sets with scatter
Many scientific groups make data available for download and some even make it easy. The file format, .csv (comma separated values like a text file), can be read in Sheets and Excel. Web page data that is separated by columns can be saved as text files and read in spreadsheets. If you are going to ask students to download data, Check it out first to see the results! It can be complicated.
Sea level change (and Other Global Issues) is a large collection of GSheets spreadsheets into which data from NASA, NOAA, and other sites has been downloaded and is ready to plot (see guides below). A large number of possible questions and tasks are included to help instructors modify them to fit their classrooms. Handling big data with scatter is an important task that students must engage in to handle!
How to start from scratch
Data management in Excel 2013/2016 – detailed booklet for Excel (more on construction later)
Managing scientific data in Google Sheets – Video instructions accompanied by GSheets spreadsheets for hands-on practice
Mathematical models and a multivariable approach
Deriving a mathematical model from data has become common in mathematics classes from high school to college. These models, and many scientific experiments, tend to be bivariate; However, in the real world, things are multivariate and measurements contain errors. Therefore, our approach must address variation, which comes in the form of measurement error and the possibility of variation or change in other variables that we believe are constant. See the real-world feature machine below.
Modeling
Do you need to teach linear regression? No, it’s just a line of best fit. Goodness of fit, that is, how well the linear regression fits the data, can be judged by using r-squared and, as students turn to non-linear systems, the use of residuals can be introduced. . Novice students do not see curvature in the data, especially if the r-square is close to one. Converting from mathematics, y = mx + b, to the real variables, H = tn + e (for stacking cookies), is a task that takes a lot of practice.
Add-data-only spreadsheets (pre-built without writing spreadsheet formulas!!!)
These experiments also contain a variety of interactive simulation and data pooling:
See Hypothetical spreadsheets for a wide variety of concepts in mathematics. These are pre-designed spreadsheets available in both Excel and Sheets.
Simulations and hypothetical questions
This is where more of the scientific process comes in, as we ask hypothetical questions that allow students to predict > test > analyze and then explain (yes, use words that include math language!). Students can investigate how a variable influences results through numerical experimentation.
Pre-designed interactive spreadsheets (265+)
Create your own interactive spreadsheets
Online collaboration
See our website, Cloud Dataset, for examples and instructions for setting things up in GSheets. The simplest thing is to group data for tasks. Data can be collected from groups or even individuals by simply sharing a spreadsheet with editing rights for students to enter the data or collect it through a Google Form. Class statistics can now be obtained and a class discussion can follow. Discussions can be whole class with grouped data projected or set up groups and use the chat feature or comments placed on the spreadsheet in class, and yes, it works! If you want to play with one, try this: Collaborative adaptation of the eyeball curve through crowdsourcing and, if there is someone else, open the chat.
Final thoughts
Interactive spreadsheets bring engaging pedagogy to the classroom that allows for numerical experimentation. These cover the entire path from data to mathematical models and simulations and introduce error analysis, helping students to “think”! It is not difficult to get students dedicated to building simulations.
Scott Sinex is professor emeritus at Prince George’s Community College in Maryland, where he taught chemistry for decades and served as chair of the Department of Physical Sciences and Engineering from 1995 to 2016. Learn more about his work and publications at https://sites.google.com/view/ssinex.
