Are you ready to see the power of spreadsheets for mathematical, statistical, and scientific thinking? Let’s follow the path of using data to derive a mathematical model and then, using the model, to build an interactive simulation. A survey of 330 teachers found the No. 1 data analysis tool was  – wait for it – Google Sheets! MS Excel was third.

Spreadsheets are the low-end of the computational food chain and can get your students started in relatively simple computations that can get really jacked-up if you want.

Google Sheets offers the big advantage of being free and allows for online collaborative efforts in the classroom and out-of-class projects.

Data - Large and Small

Collect your own (first-hand, you are performing the measurements)

Collecting experimental data allows the introduction of simple measurement error analysis 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 real-world data sets (second-hand) is the Quantitative Environmental Learning Project (QELP) website. You can download data as an Excel file that can easily be converted to a GSheets file with no problem.

Exploring Error in Measurements

There are two major groups of errors, random and systematic (adds bias), and both can be built into spreadsheet simulations such as “just-add-data” spreadsheets.

So, how do you integrate error into a spreadsheet? Let’s examine an important measurement device, the ruler, using the Rulers and Measurement Error Excelet to explore the types of errors in a variety of ways.

Big data sets with scatter

Many scientific groups make data available for downloading, and some even make it easy. The file format, .csv (comma separated values as a text file), can be read by Sheets and Excel. Data on web pages that are column-separated can be saved as text files and read by spreadsheets. If you are going to have students download data, check it out first to see the results! It can be messy.

Sea Level Change (and other global problems) is a large collection of GSheets spreadsheets in which data from NASA, NOAA, and other sites have been downloaded and are ready for plotting (see the guides below).  A large number of possible questions and tasks are included to aid instructors to modify to suit their classrooms.  Dealing with big data with scatter is an important task that students need to get involved in handling it!

How to get started from scratch

Dealing with Data in Excel 2013/2016 - detailed handout for Excel  (more about building later)

Dealing with Scientific Data in Google Sheets - Video instructions with an accompanying GSheets spreadsheets for hands-on practice

Mathematical Models and a Multivariable Approach

Deriving a mathematical model from data has become commonplace in math classes from middle school through college. These models, and so many science experiments, tend to be bivariate; however, in the real-world, things are multivariate plus measurements contain error.  So our approach needs to deal with variation, which comes in the form of measurement error and the possibility of the variation or change in other variables that we think are constant.  See the real-world function machine below.

Modeling

Do you need to teach linear regression? No, it’s just a line of best-fit. Goodness-of-fit, how good the linear regression fits the data, can be judged by the use of r-square and, as students turn to non-linear systems, the use of residues can be introduced. Novice students don’t see curvature in data especially if the r-square is near one. Converting from math, y = mx + b, to the actual variables, H = tn + e (for stacking cookies), is a task that needs lots of practice.

These experiments contain a variety of interactive simulation and data pooling too:

See What-if Spreadsheets for a wide variety of concepts in mathematics. These are pre-built spreadsheets available in both Excel and Sheets.

Simulations and What-if Questioning

Here is where more science process enters, as we ask the what-if questions that allow students to predict > test > analyze and then explain (yes, use words including math speak!). Students get to investigate how a variable influences the results by numerical experimentation.

Pre-built Interactive Spreadsheets (more than 265)