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: |
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 |
||
Risk assessment for
Benton County, Oregon, Part 2: Risk of a catastrophic earthquake |
||
Oceanic
lithosphere: sink or swim? |
||
Waiting for the
Tsunami |
||
How often do
earthquakes occur? |
||
Assessing
earthquake risk: How often does the "Big One" occur? |
||
Yellowstone:
Hazards at the world's largest volcano |
||
A percentage stroll
through Norris Geyser Basin, Yellowstone National Park |
||
A Tale of Two
Cities (and two hurricanes): Part 1, Miami |
||
Flood days and good
canoeing days at Congaree National Park |
||
A Tale of Two
Cities (and two hurricanes): Part 2, New Orleans |