The Spreadsheet Lab Manual

Click here to edit subtitle

Goal:  Provide teachers with classroom ready spreadsheet modeling activities, "Spreadsheet Labs," that unite Science and Math to model real world phenomena using Excel or Google spreadsheets.  "The Spreadsheet Lab Manual" laboratory suite consists of 25 independent spreadsheet based simulations in high school Physics, Chemistry, Biology and Math.

SPREADSHEET LAB CONTENT AREAS

"Creating mathematical models using spreadsheets can help students learn at deep levels in both science and mathematics, and give teachers an excellent opportunity to integrate these two disciplines." (Horton, Leonard Mathematical Modeling in Science: Using Spreadsheets to Create Mathematical Models and Address Scientific Inquiry.  Science Teacher, v72 n5 p40 Jul 2005.)  

Why Spreadsheets?                                         Activity Video Clips

STEM MAJORS NEED SPREADSHEETS all the time, the spreadsheet is the go-to program for data collection and analysis task.  Proficiency on a spreadsheet will help bridge the skill gap that separates students who are successful STEM majors and students who leave STEM majors.  The Spreadsheet Lab Manual simultaneously give students command of math and teaches them to reason quantitatively on a platform (the spreadsheet) that is a standardized part of both higher education and industry (Excel, Google Sheets, Apple are all compatible)

Scroll Down to View Graphs and Activity Summaries: The following graphs are produced and manipulated by students completing the activities in The Spreadsheet Lab Manual*. The data is generated from mathematical models written and assembled by the students by following the procedures outlined in the activities.

Activity: "Comparing Trajectories" (Physics)  click to view: Video Demonstration

Description: This graph compares the trajectories of projectiles with and without air resistance on a spreadsheet. Students can investigate this motion by adjusting cross sectional area, mass, initial velocity, launch angle or drag coefficient and immediately observing the effect of the change.

Content Areas: Kinematics, Forces and Newton's Laws

Objectives: Students will create a spreadsheet from scratch that quantitatively calculates the trajectory of a projectile and then answer questions that will allow them to model a wide variety of projectiles experiencing drag force.

__________________________________________________________________________________________________________________

Activity: Taken from "Titration Simulation" (Chemistry) click to view: Video Demonstration

Description: This graph is produced by students simulating titration of an acid with a base of known concentration. The students can adjust the volume increment and add 3000 volume increments of known/unknown acid/base to an unknown/known base/acid.

Content Areas: Acid-Base Chemistry, Analytical Methods, Logarithms

Objectives: The students will simulate titration, perform titration calculations by solving problems with spreadsheet functions (goal seek), observing the pH change quickly close to neutralization even with very small (0.001 ml) volume increments.

 

 

Activity: Taken from "Population Growth 2*" (Predator Prey Simulation) (Biology)

Description: This is a graph of the linked populations of predator (dingoes) and prey (rabbits) approaching equilibrium in a given ecosystem.  Their numbers cycle based on their interdependence. Content Area: Ecology, Population Dynamics

Objectives: The students will observe the dynamics of a predator and prey species and conditions that lead to extinction by constructing a mathematical model of their populations and graphing them verses time on spreadsheet. The students will study variables including: initial population(s), predator benefit, prey fatality probability, logistic competition factors, minimum viable populations. 

   * Serves as a follow-up to "Population Growth 1" which compares Ideal and Logistic Models.

 

 

Activity: "The Superposition Principle" (Physics)**    Video Demonstration

Description: Superimposing wave functions with equal amplitudes and nearly equal frequencies to produce beats on a spreadsheet.

Content Area: Wave Mechanics

Objectives: The students investigate how the superposition principle causes wave amplitudes to combine for waves that are in phase and out of phase.  Beats are produced at different frequency combinations as students study the effect of varying frequency and amplitude to infer a model for the phenomena of beats and confirm their model with research.

 

 **DEMO:  Get two identical tuning forks and place two equal size pieces of scotch tape (approx 2 inches) on the ends of one of them.  Strike each tuning fork and hold them both an equal distance from your ear.  

Background:

View Demonstration Video for High School teachers and administrators*

The Spreadsheet Lab Manual is a collection of Science and Math activities each of which begins by opening a blank spreadsheet and following the instructions to construct a spreadsheet that performs a relevant task. The tasks range from simulations of physical behavior (such as the above examples) that can be modeled mathematically to solving a multiple step mathematical problem. Activity levels range from basic to advanced and serve to enrich the curriculum with 21st Century Skills such as spreadsheet manipulations, calculations, graphs and programming among others. They do this while boosting students' understanding of the course content with a level of interaction with the material that would not be possible without the calculating power and immediate feedback that a spreadsheet can provide. Through their own preparation and student observation during classroom use of the activities, teachers will garner and reinforce their own skills with spreadsheets while creating a dynamic of students and teachers sharing their skills.

Comprehensive NJCCCS alignment

 

Content area and subject area of each lab activity:

For a more detailed description of each activity please refer to the following Activity Descriptions and Activity Planner

  • Vector Addition of multiple 2 dimensional vectors.(Physics: Vectors)
  • Terminal velocity (Physics: motion and forces)
  • Projectile motion with air resistance (Physics: motion and forces)
  • Graphing instantaneous velocity and position verses time of a rocket with changing mass due to burning fuel and air resistance acting on it (Physics: motion and forces)
  • Shifting gears in a car and accelerating it through a range of engine speeds (Physics: rotational motion and forces)
  • Inflating a balloon and plotting buoyant force and weight vs. diameter (Physics: buoyancy
  • Superimposing Wave functions to observe interference and beats (Physics: waves)
  • Performing electrostatic field, force, potential and PE calculations (Physics: electrostatics)
  • Analyzing mathematical relationships in circuits (Physics: current electricity)
  • Generating heating/cooling curves for objects using Newton's Law of Cooling (Chemistry: heat)
  • Predicting limiting reactants and percent excess for chemical reactions (Chemistry: stoichiometry)
  • Simulating diffusion limited reactions and effusion (ChemistryGraham's Law)
  • Investigating Boyle's, Charles', Gay-Lussac's and Avogadro's Law (Chemistrygas laws)
  • Simulating titration and solving related problems (Chemistry: acid-base reactions)
  • Comparing population growth in ideal and logistic models (Biology/Environmental: populations)
  • Modeling predator prey interactions in an ecosystem (Biology/Environmental: populations)
  • Simulating Genetic Crosses (Biology/Environmental: Genetics)
  • Solving and graphing quadratic and nonlinear equations (Math)
  • Modeling investments, compounding interest and amortizations (Math)
  • Simulating casino games to analyze the probability of winning or losing over the long run (Math)
  • Optimization of can design using cylinder geometry (Math)
  • Analyzing functions over varying intervals and graphing (Math)