Tom Juster's AFG Spreadsheet Modules for Geologic Hazards

What are AFG spreadsheet modules?

AFG spreadsheet modules are versions of Spreadsheets Across the Curriculum (SSAC) spreadsheet modules that have been modified to provide some advantages:

1.    Students get automatic and immediate feedback for incorrect answers, and can correct them to ensure they get everything right. The feedback considers not only whether an answer is correct or not, but if it was obtained in the correct way; i.e., students can't simply do calculations on their hand calculator and enter the results into the spreadsheet; they have to actually perform the calculations using spreadsheet tools. Learning Excel is one of the objectives of these modules.

2.   Students must work through the spreadsheet sequentially, from easy to difficult. Each module is divided into between 2 and 6 preliminary tasks and one final capstone task. Each preliminary task must be completed perfectly before the student is allowed to advance to the next. Students may elect to quit any time during the final task, but in practice almost all continue to refine their answers until it too is done perfectly--thus obtaining a perfect 100% score!

3.   Spreadsheets are automatically graded, which saves the instructor time. Upon completion of the module the student is presented with a unique encrypted alphanumeric code that the instructor can decrypt to confirm the student did the work.

All of the AFG modules are also available in traditional versions through the SSAC website. This site also has a great introduction to the concept of SSAC, as well as links to a large library of completed SSAC modules on various subjects, including geology.

 

What files do I need to use an AFG spreadsheet module?

Each AFG module consists of two parts: (a) a PowerPoint file 2007/2010 (.PTSX) designed to be run in slide-show mode that explains the relevant science and math, and demonstrates the tasks the student is expected to learn in the module; and (b) a macro-enabled Excel 2007/2010 spreadsheet (.XLSM) that the student actually completes. The Excel spreadsheets have been secured with a third-party Add-in program to prevent students from prying into the details of the feedback algorithms. The two files are designed to be used in tandem: the PowerPoint explains exactly what is to be done in the Excel spreadsheet. Students will not be able to complete the module with just the spreadsheet file. In order to unlock and read the student code instructors will need the decryption program; this can be requested by email.

 

Do the AFG spreadsheet modules require any specific hardware or software?

Yes. At the present time the only version of Excel that reliably supports macro-enabled spreadsheets using VBA programming is version 2007/2010 for the PC. These modules will not work on a Mac or iPad (though the newest version of Excel 2012 for the Mac promises VBA macro support; preliminary testing has not been positive). They will not work on a PC running Excel 2003 or OpenOffice. Note that the traditional versions of the modules have much looser hardware restrictions.

 

How can the AFG spreadsheet modules be used?

These modules can be used just like any SSAC module--as a standalone activity. For details of implementation, see the SSAC website. The modules below were created specifically for Hazards of the Earth's Surface, an online service course at the University of South Florida. Students were required to complete five modules during the semester selected from the full menu of eleven. Upon completion, students took a short online quiz where they entered their code and then answered some critical thinking questions about what they'd learned.

 

How were the AFG spreadsheet modules created?

The AFG spreadsheet modules were created using the Visual Basic for Applications (VBA) programming language in Excel 2010 for the PC. They were then encrypted using the Add-in program XLSafe. If you're interested in the nuts and bolts see this reference.

 

Description of the modules:

Module

Description

Risk assessment for Benton County, Oregon, Part 1: Using Excel for the first time

Students are introduced to the spreadsheet program Excel while preparing a risk assessment for Benton County, Oregon, based on the threat of a catastrophic earthquake along the Cascade subduction zone. This is the first in a sequence of two modules on this topic, and emphasizes basic Excel skills that will be needed in all subsequent activities.  The module is divided into three tasks. Using a summary table of structures in Benton County (which is needed to compute the monetary exposure to risk), the first task introduces the concept of Excel formulas, the second task explains about built-in functions such as SUM, COUNT, and MAX, and the third task requires the students to reprise these skills on a table of buildings classified by construction. Some slides in this module originally appeared in the module "Spreadsheet Warm Up for SSAC Geology of National Parks Modules" by Dorien McGee, Meghan Lindsey, and Len Vacher.

Risk assessment for Benton County, Oregon, Part 2: Risk of a catastrophic earthquake

Students continue to learn Excel while computing the risk posed to Benton County, Oregon, caused by a catastrophic earthquake along the Cascadia subduction zone. This is the second of two modules that compute the risk. The twin objectives are to teach students about the quantification of risk and to reinforce their newly learned Excel skills. The module is divided into four tasks. Students are first introduced to the concept of the Risk Equation and its three components: probability of hazard, exposure, and vulnerability. The first three tasks are then devoted to computing the monetary and mortality risk to Benton County posed by a large Cascadia earthquake. By deconstructing the risk equation into its components students see how each is computed and how each affects the calculation of risk. The final task requires the students to reprise the Excel skills they've learned to compute the probabilities of occurrence of a large earthquake along three fault segments.  Some slides in this module originally appeared in the module "Spreadsheet Warm Up for SSAC Geology of National Parks Modules" by Dorien McGee, Meghan Lindsey, and Len Vacher.

Oceanic lithosphere: sink or swim?

Students are introduced to the concept of the weighted average through calculation of the density of the layered lithosphere. There are five tasks. The module starts by demonstrating how the weighted average is calculated and interpreted from a table of home prices (by size) and a table of university tuition (by residency). These same principles are then applied to the density of the oceanic lithosphere based on its two components: crust and mantle. Since the thickness of the mantle component increases as the oceanic lithosphere ages, students can then calculate how the average density of the oceanic lithosphere changes and compare this value to the density of the underlying asthenosphere. The difference in density determines whether the oceanic lithosphere can descend into the mantle (i.e., be subducted; "sink") or remain on top ("float"). The final tasks of the module ask the students to apply these same principles to the density of oceanic lithosphere that has undergone the basalt -> eclogite phase transition, and then to the density of the continental lithosphere.

Waiting for the Tsunami

Students use the shallow-water wave equation to compute the time it takes for a tsunami to travel across the ocean. The module is divided into four tasks. The first tasks introduce students to the idea of shallow-water waves, and shows how they are defined and how their velocity is computed. The third task demonstrates how travel times are computed from the velocity and distance. The final task applies these concepts to a hypothetical tsunami originating off the west coast of Portugal (where a giant earthquake occurred in 1755) by dividing the travel paths into segments, each with a characteristic depth and therefore velocity.

How often do earthquakes occur?

Students learn how to create and interpret a plot of earthquake magnitude vs. frequency for all earthquakes worldwide. The module is divided into six tasks. Students first extract data from the Advanced National Science System (ANSS) website, then calculate frequencies, learn about logarithms, and finally create a plot using several advanced Excel function: logarithmic axes and extrapolated trend lines. The final task requires them to reprise these skills by inferring the frequency of the smallest (M < 3) earthquakes, which are too numerous to catalog.

Assessing earthquake risk:
How often does the "Big One" occur?

Students learn how to create and interpret a Gutenberg-Richter plot of earthquake magnitude vs. log(frequency) for earthquakes along specific fault segments. The module is divided into five tasks. Students first create a Gutenberg-Richter diagram for the New Madrid fault zone in Missouri by extracting data from an external catalog and plotting it up. They then use the plot to infer the recurrence interval of very large earthquakes on this fault. The final two tasks ask the students to reprise these skills by creating Richter-Gutenberg plots of earthquakes from Grand Teton and Yellowstone National Parks, and infer the recurrence interval of M=7.5 earthquakes in these areas.

Yellowstone: Hazards at the world's largest volcano

This module is modified from the module "Yellowstone! A National Park on a Hotspot" Judy McIlrath.  Students use Excel to perform calculations relevant to the origin of volcanism at the world's largest 'supervolcano'--Yellowstone. The module is divided into four tasks. Students use the ages and locations of volcanoes in the Hawaiian chain to calculate the rate at which the Pacific Plate is moving. They then put the size of volcanic eruptions into context by expressing the eruptive volume in terms of "numbers of Olympus swimming pools of erupted material". Finally, they explore the relationship between frequency and recurrence interval for large cataclysmic eruptions, post-caldera eruptions, and steam explosions.

A percentage stroll through Norris Geyser Basin, Yellowstone National Park

Students use Excel to perform a variety of calculations involving percentage, all applied to observations they make when taking a virtual stroll through Norris Geyser Basin in Yellowstone National Park. The module is divided into seven tasks, one for each stop along the route. Students use percentages to describe the height of Steamboat Geyser (the world's largest), the acidity of Echinus Geyser (the world's largest acidic geyser), the degree of human impact at Minute Geyser, the temperature of Black Growler steam vent (one of the hottest in the park), the subsurface temperatures at a deep exploratory borehole, and the nature of hydrothermal discharge at Tantalus Creek, which drains Norris Basin.  The final task requires them to reprise these skills by using percentages to describe the variation of temperature and pH in the Back Basin area of Norris.

A Tale of Two Cities (and two hurricanes): Part 1, Miami

Students use Excel to perform a analyze the risk posed by a category 4 hurricane to central Miami, and compare it to the actual damage produced by Hurricane Andrew, which struck south of the city. Using a large table of data that summarizes area, population, and housing statistics of the great Miami area by zip code, students calculate the property vulnerability of both downtown Miami and the area actually struck by Hurricane Andrew. They then extract data from the NOAA Historical Hurricane Tracks webpage to compute the probability of such a storm and then use the risk equation to compute the risk. The final tasks require then to consider the risk to other cities.

Flood days and good canoeing days at Congaree National Park

This module is adapted from a module by the same name by Mark Rains, David Shelly, and Len Vacher.  Students work with U.S. Geological Survey hydrograph data to determine the probability that the high water level of Cedar Creek, which flows through Congaree NP, will be within a particular range on a given random day. The module is divided into five tasks. The data are daily stage heights over the ten-year period October 1, 1998 through September 30, 2008. The method of solution is to count the number of days (out of the 3,653 days of record) that the water-level of Cedar Creek was above 8 ft (when some trails are flooded) and the number of days when it was in the range of 2-6 ft (when Cedar Creek was not safely navigable by typical visitors to the Park). The students use the COUNTIF function to count the number of days when the water level is within the stipulated range, the COUNT function to count the total number of days, and the ratio of the two to determine the probability. The intent of the module is to introduce students to the frequency concept of probability.

A Tale of Two Cities (and two hurricanes): Part 2, New Orleans

Students explore the origins of subsidence in the city of New Orleans. The module is divided into five tasks. Students first learn about the history and geology of New Orleans, and the three main reasons it is presently subsiding: (1) compaction of deltaic sediments; (2) downward movement along a normal fault; and (3) compaction and oxidation of organic soils that have been exposed by drainage. They then create a spreadsheet that calculates the cumulative subsidence of the city since 1720 incorporating all three sources of subsidence. This estimate is then adjusted to account for sea level rise. The final task requires them to adjust the subsidence rates within limits to see how the same net subsidence can be produced by various contributions among the three processes.

 

Links to the Modules:

Topic

PPTX File

XLSM File

Risk assessment for Benton County, Oregon, Part 1: Using Excel for the first time

Haz-Risk-1

Haz-Risk-1

Risk assessment for Benton County, Oregon, Part 2: Risk of a catastrophic earthquake

Haz-Risk-2

Haz-Risk-2

Oceanic lithosphere: sink or swim?

LithoDens

LithoDens

Waiting for the Tsunami

Tsunami

Tsunami

How often do earthquakes occur?

EQ-Freq

EQ-Freq

Assessing earthquake risk: How often does the "Big One" occur?

EQ-GR

EQ-GR

Yellowstone: Hazards at the world's largest volcano

Volcs-YS-1

Volcs-YS-1

A percentage stroll through Norris Geyser Basin, Yellowstone National Park

Volcs-Norris

Volcs-Norris

A Tale of Two Cities (and two hurricanes): Part 1, Miami

2Cities-Miami

2Cities-Miami

Flood days and good canoeing days at Congaree National Park

Flooding-Cong

Flooding-Cong

A Tale of Two Cities (and two hurricanes): Part 2, New Orleans

2Cities-NO

2Cities-NO