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

Tom Juster
University of South Florida, Tampa
Created: April 18, 2012
Author Profile


In this Spreadsheets Across the Curriculum activity, students learn how to create and interpret a Gutenberg-Richter plot of earthquake magnitude vs. log(frequency) for earthquakes along specific fault segments. 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. At the end of the module they are asked 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.

Learning Goals

Students will

  • Extract data from an external earthquake catalog and compute frequencies and recurrence intervals.
  • Create an Excel scatter plot of log(RI) vs. magnitude—the Gutenberg-Richter plot.
  • Make inferences from this plot about the recurrence interval of earthquakes larger than the observed earthquakes.

In the process the students will

  • Learn how geologists use the Gutenberg-Richter plot to make inferences about the frequency of large earthquakes even if such earthquakes haven't occurred.
  • Practice creating and modifying charts in Excel.
  • Work with real data to calculate real numbers that are of great interest to people assessing hazards.
  • Practice creating and modifying charts in Excel.
  • numbers that are of great interest to people assessing hazards.

Context for Use

This module was designed for use in the Hazards of the Earth's Surface service course at USF. It assumes that students are familiar with basic Excel operations, especially the use of relative and absolute cell references and functions. The topic of earthquakes would also be other basic courses in geology.

Description and Teaching Materials

The module is a PowerPoint presentation with embedded spreadsheets. Click on the link below to download a copy of the module.

Optimal results are achieved with Microsoft Office 2007 or later; the module will function in earlier versions with slight cosmetic compromises. If the embedded spreadsheets are not visible, save the PowerPoint file to disk and open it from there.

The above PowerPoint presentation is the student version of the module. The embedded spreadsheet consists of a template for students on which students complete their work and answer the end-of-module questions, and then turn in for grading. Since this module is designed as a stand-alone resource, instructions for extracting and saving the embedded spreadsheet are included in the PowerPoint presentation.

This module is offered in two versions: a traditional SSAC version and a new auto-feedback/graded (AFG) version. The AFG version: (a) provides automatic and immediate feedback to incorrect answers, including formulas; (b) requires students to complete tasks sequentially by not allowing them to advance until they've completed a task perfectly; and (c) automatically computes a grade and encrypts it into a code the students submit to verify successful completion. The files needed for this version can be accessed from Juster's AFG website.

Teaching Notes and Tips

This module is constructed to be a stand-alone resource. It can be used as a homework assignment, lab activity, or as the basis of an interactive classroom activity. It has been used as a mid-course module in Hazards of the Earth's Surface, an online service course at USF designed for non-majors, for the last two years. The module assumes students are familiar with the concept of earthquake magnitude, and are comfortable with logarithms. In addition, the module assumes students can create and modify a semi-log diagram, plot a trend line, and make inferences about data read from the graph. To assure students have these skills, it is recommended that they first complete the "How often do earthquakes occur" module. Because the instructions for creating the graph are written for Excel 2007/2010, students might be confused if they are using an earlier version of Excel.


There is a slide at the end of the presentation that contains end-of-module questions. The end-of-module questions can be used to examine student understanding and learning gains from the module. The answer key for the end-of-module questions is found at the end of the instructor version of the module.

References and Resources

Gomberg, J. and Schweig, 2007. Earthquake Hazard in the Heart of the Homeland—USGS Fact Sheet 2006-3125. Available at:

Pickering White, B.J., Smith, R.B., and Wong, I., 2006. Development of earthquake ground shaking hazard maps for the Yellowstone-Jackson Hole-Star Valley, Wyoming, Final Technical Report. Available at:

Williams, R.A., 2009. Earthquake Hazard in the New Madrid Seismic Zone Remains a Concern—USGS Fact Sheet 2009-3071. Available at: