Dealing with Scientific Data in Google Sheets
Data > Model > Simulation
Here are four GSheets spreadsheets and accompanying YouTube videos to get you into numerical computations, graphing, and developing a mathematical model via regressions. We will introduce using r-square and residuals to judge the goodness of fit of the model to the data. To download the spreadsheet(s): Cursor over and look in the upper right-hand corner of the embedded spreadsheet and click on the gray box that appears to open it! Then go to File > Make a copy... so you can follow (and edit) the steps as you follow the videos.
The first video gets you into computations with the data - using formulas and the functions in Sheets, while the second video introduces graphing plus linear regressions and r-square.
Google Sheets function list - click here (under HELP on any GSheets spreadsheet too!)
GSheets - Computations
GSheets - Graph with Model
This second spreadsheet (given below), which has some interactive features (yellow cell which can be changed), has you examine data to decide if it has curvature (nonlinear). This introduces residuals to aid in the analysis. We will also examine random error or the addition of scatter in the data as well.
This spreadsheet has some practice tabs and has you investigate if random error can mask curvature!
GSheets - Is the data linear?
The third spreadsheet (below) develops an interactive simulation, which allows you to examine the behavior of a mathematical model. This is building a spreadsheet simulation. Here we are going to examine a cubic polynomial where we can adjust the coefficients, a, b, c, and d and visualize behavior. Introduce some advanced items such as the use of the check box as an off-on switch to turn a reference cubic equation on and off the graph.
Why build a spreadsheet simulation? See the over 250 examples that allow numerical experimentation: click here
GSheets - Building a Simulation
How about some more bells and whistles for simulations? The fourth spreadsheet that examines radioactive decay, a classical exponential decay, builds a multivariable simulation that uses checkboxes, data validation to set cell limits and get a dropdown menu, and a vlookup table. It also shows how to add random error using a RANDBETWEEN function and the error from background counting radiation.
GSheets - Building a Simulation II: More Bells & Whistles (in the works!)
For the more or up-and-coming advanced user:
The Google Sheets Checkbox - This is a "how to" interactive Google Sheets with instructions that illustrates further uses of the checkbox.
The Mechanics of Inducing Error in a Spreadsheet - This is a "how to guide" to add random and systematic errors into your Google Sheets spreadsheet simulations. For a good first model experience for students, see Investigating the Height of a Stack of Cookies to investigate the influence of scatter in data and its meaning in this case.
If you need an introduction to the types of errors, see Rulers and Measurement Error, a very animated interactive Excel spreadsheet with a common measuring device.
