How Much Water Is In Crater Lake?
Summary
In this Spreadsheet Across the Curriculum activity, students use a 3D grid of rectangular prisms to calculate the volume of water in Crater Lake (Crater Lake National Park, Oregon). The calculation starts with an Excel spreadsheet of data from a bathymetric survey. The students use the square grid of depths (actually elevation of the lake bottom) to calculate the volumes of vertical prisms, which they sum to determine the total volume of water in the lake. The module helps them understand how one can determine the volume of an irregular solid that is too complicated to permit a formula. The Excel spreadsheet contains data from two different sized grids so that students can explore the effect of data resolution on the calculated result. In the end, the students compare the volumes they have calculated using the two different grid sizes to the published value calculated by the U.S. Geological Survey using the full spatial resolution of the data and thus determine which of the grid sizes gives the more accurate estimate of the volume of water in Crater Lake.
This material is based upon work supported by the National Science Foundation under Grant Number NSF DUE0836566. Any opinions, findings, and conclusions or recommendations expressed in this material are those of the author(s) and do not necessarily reflect the views of the National Science Foundation.
Learning Goals
Students will:

Work with actual data from a contour map showing an intriguing topographic surface that is hidden from view because it lies beneath a scenic lake. The map contains some suprises. Wellknown Wizard Island is not the only volcano within the basin. It is just the one that rises above lake level.

Use a spreadsheet to determine the volume of many long, square, rectangular prisms standing on end and side by side that collectively approximate the volume of water in the lake.

Compare total volumes calculated from summing the prism volumes for two different grid sizes and to the published value from the USGS.
In the process the students will:

Gain experience with and see use for topographic maps, in this case a map of an underwater surface.

See how to determine the volume of an irregular geometric figure by dividing it up into small simple units whose volumes one can calculate  a step toward integration.

Better understand the source of errors in numerically calculated values.

Work with actual data to solve a problem about an iconic geologic scene.

Do a calculation that they might think of again when they see the calendar images of Crater Lake and Wizard Island.
Context for Use
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.

PowerPointSSACgnp.BG1601.HL1.1 (PowerPoint 5.3MB Jun1 13)
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 file is the student version of the module. It includes a template for students to use to complete the spreadsheet(s) and answer the endofmodule questions, and then turn in for grading.
An instructor version is available by request. The instructor version includes the completed spreadsheet. Send your request to Len Vacher (vacher@usf.edu) by filling out and submitting the Instructor Module Request Form.
Teaching Notes and Tips
The module is constructed to be a standalone resource. It can be used as a homework assignment, lab activity, or as the basis of an interactive classroom activity. It was used as an outofclass activity in a seniorelective course, Environmental Geology of the National Parks (for geology majors and nonmajors), during development of the module in Spring 2010. The module was used after the students had worked through several other modules. In general, the students considered this module to be one of the more challenging of the collection, but well within their range of expectations for level of difficulty. It has not been implemented yet in the introductorylevel Geology of National Parks course.
Assessment
There is a slide at the end of the presentation that contains endofmodule questions. The endofmodule questions can be used to examine student understanding and learning gains from the module. Pre/post test, pre/post test answer key, and answer key for endofmodule questions are at the end of the instructor version of the module.
References and Resources
Cranson, K. R. 1980. Crater Lake, gem of the Cascades: the geological story of Crater Lake National Park. Lansing, Mich: K.R.Cranson Press.
Nathenson,Manuel, Bacon, C. R.,Ramsey, D. W. 2007. Subaqueous geology and a filling model for Crater Lake, Oregon. Hydrobiologia. 574: 1327.
The National Park Service Web site. Click here
USGS Website: Crater Lake Revealed. Click here
USGS Web site: 2000 multibeam sonar survey of Crater Lake, Oregon; data, GIS, images, and movies. Click here
USGS Web site: Cruise Report R/V Surf Surveyor Cruise S100CL Mapping the Bathymetry of Crater Lake, Oregon.Click here
CVO Web site: Crater Lake, Oregon, Mount Mazama, Oregon. Click here