Lesson Plan: Electronic Hydrology Lesson Plan - Revised
Subject: Earth Science/ Hydrology/ Peak Discharge
Classtime: 120 minutes
Grade Level: 8-12

Materials/Technology:

• Computer/Printer
  
• Excel Software
  
• Paper/pencil
  
• Map of Yellowstone River
  

Safety, Handling, Disposal:
Proper operation of computer/printer is expected--no browsing other sites.

Learner Outcomes:
Students should be able to:

• Use Excel spreadsheets
  
• Predict time and height of peak flow on river
  

Problem/Purpose:
To find if time of peak flows for lower reaches of the Yellowstone are predictable.

Background/Inquiry:
Flooding is a reality along the Yellowstone River and has historically resulted in millions of dollars of property damage and lives lost. An early warning by hydrologists could help reduce these loses.

This activity predicts when peak flows will occur for communities at successively lower reaches of the Yellowstone River. Although this activity focuses on the Yellowstone River in Montana, it could be used on any river for which data is available.

Vocabulary List: Here.

Hypothesis: Date of peak discharge at a particular location can be predicted from upstream peaks.

Procedures:
Advanced teacher prep -

1. Examine the stations that are available for data collection lying along the Yellowstone River from Gardiner to the lowest point closest to the confluence of the Missouri and Yellowstone Rivers. Use the USGS website: http://montana.usgs.gov/pub/MTStations.pdf.
   
   
2. Students will only use stations selected by the instructor. Data from these stations should be entered into a data table similar to the one below.
   
   

   Station #	Location	Elevation	Miles from Missouri/Yellowstone Confluence
   06191500	Corwin Springs	5079	550
   06192500	Livingston	4542	501
   06214500	Billings	3081	360
   06295000	Forsyth	2504	238
   06309000	Miles City	2333	184
   06329500	Sidney	1881	29

   
   
3. Retrieve peakflow data from the USGS website for the identified stations:
   1. Go to http://mt.waterdata.usgs.gov/nwis/peak
   2. Under “Data Category,” select “Surface Water”
   3. Under “Geographic Area,” select Montana
   4. Select “Go”
   1. You will then be led to a site titled, “Surface-Water Data for Montana”
   2. Select “Peaks”
   1. You should now be at the site titled, “Peak Streamflow for Montana”
   2. Under “Site Identifier,” select “Site Number” and then “Submit”
   1. Type in your site number
   2. Under “Tab-separated Data,” select “MM/DD/YYYY”
   3. Select “Submit”
   1. Go to the first line of your desired data retrieved, and highlight all lines.
   2. Under Edit, select Copy (to copy the data)

   
   
4. Import these data sets into Excel and parse the data
   a. Open Excel, this will bring up a new spreadsheet.
   b. Under Edit, select Paste. This will paste data copied from website.
   c. Return in Netscape to http://mt.waterdata.usgs.gov/nwis/peak and follow steps 2-11 for the next site (Livingston).
   d. After copying data for each site, go to Excel to Column A and highlight the cell below the last site's data.
   e. Paste that data, then do the same thing for the other 4 sites.
   
   
5. Parsing Data
   a. Click on top left cell having the letter A
   b. Under Data, select Text to Columns to parse (separate the data).
   c. Fixed width button should be highlighted. select Next (which will place vertical separators between data columns).
   d. Click on Finish. Five columns of data should appear.
   
   
6. Eliminate columns not needed:
   a. Click on the smallest cell just above the number 1 of row 1 (to the left of Column A.
   b. Highlight Format at top menu, go to Columns, follow to right and select Autofit Selection, release mouse.
   c. Highlight top cell of column A that contains the A.
   d. Go to Edit at top menu and select Delete.
   e. Go to top cell of new column A and type in the station’s name.
   f. Highlight the next 6 cells below that cell, go to the top menu.
   g. Highlight Edit, go down to Clear, follow the right arrow to All, release.
   h. Go to the top cell in column A for each of the next stations data and type in that stations name.
   i. Do steps 6 and 7 just above for each station
   j. Highlight Column D and under Edit at top menu, select Delete (Keep discharge column as you may wish to use it later in an extension lab).
   
   
7. Inserting Columns:
   a.Highlight the cell at top of column C with the C inside it.
   b.Go to top menu, highlight Insert, select column and release.
   
   
8. Averaging Data:
   a. Prior to finding the average (median) change all the years to 1999 medians can be figured and graphed.
   b. Highlight Column B and go to Format, select Cells go to Date in the list at left and select month/day format and the year will be dropped from view.
   c. Go to the cell in Column C just right of the first Date of Peak flow cell for Corwin Springs
   d. Type in: =median(b1:b7) then push return to find the average Date of Peak flow for Corwin Springs.
   e. Now select cell C1 and under Edit, Copy it, then go to the cell in Column C just right of the first date cell for the next station, under Edit, select Paste and release. Do the same for the other station.
   f. There should be 6 median dates for peak flow in column C.
   
   
9. Graphing Data:
   Prepare the median dates and discharge columns C for graphing. Eliminate the empty cells between cells having data by:
   a. Highlight the empty cells between 2 cells having data in columns C.
   b. Go to menu item Edit and select Delete, select choice- Shift Cells Up. This will bring all median dates together to graph.
   c. Highlight the data in column C, go to Insert and select chart.
   d. Select Line graph, select finish.
   
   
10. Determine the time lapse between peaks between stations and record:
    a. Subtract date of peak flow at the second site from date of peak flow at the first site.
    b.Record in below format.
    Corwin Springs to Livingston- 12 days
    Livingston to Billings- 1 day
    Billings to Forsyth- 7 days
    Forsyth to Miles City- 1 day
    Miles City to Sidney- 4 days
    
    
11. With this data, predict what the dates of peak flow for Corwin Springs were in 1997 and 1998. Use time lapse to predict peak flows at all other stations investigated. Obtain peak flow from Corwin Springs for 1997 and 1998. Compare these results with the actual, determine and record the % error on student data table.
    (% error = Actual value-Experimental value/Actual value *100)
    Do the same for all stations.

Results/Analysis:

Students should turn in completed student generated data sheets (station data, time lapse, student predictions, percentage error), Excel spread sheets, and Excel graphs.

Conclusions:
From your data was your prediction of peak flow accurate? Give reasons for inaccuracy.

Assessments:

1. From the data could you predict what years will have floods? Why or why not?
2. Would extending the number of years studied allow you to be more accurate?
3. Teacher can build a data table and have students predict peak flow arrival times.

Reflections: Share your thoughts on this lesson - email the NTEN team.

Extensions:Find if the time lapses change with elevation.

1. Determine the elevational changes between adjoining stations.
2. Graph the data to visualization any changes found.
3. Determine the time lag/mile for each reach between stations- Divide Hours (converted from days) by Miles between stations-
4. Summarize and discuss your conclusions.
5. Find the peak flow dates from years 1970-90 and repeat the lab. - Does using averages from a larger data base reduce the errors?
6. Obtain the peak discharges and graph it against river miles to see how river size changes with length.
7. Graph lag/mile against elevation (Y-axis) and visualize results. Contact the following people to determine if and what equation they use to predict flow peaks: Yellowstone County Emergency Preparedness Director, 256-2775 USGS, Water Resources Division Hydrologist, Billings, 657-6113

References/Resources:

URLs:

Yellowstone River Web addresses for retrieval of Realtime Data:

http://montana.usgs.gov/rt-cgi/gen_tbl_pg

Historical Data:

Upper Yellowstone

http://waterdata.usgs.gov/nwis-w/MT/search.components/textsearch.cgi?
mode=search&basin=Upper+Yellowstone&state=MT&exact=1

Lower Upper Yellowstone:

http://waterdata.usgs.gov/nwis-w/MT/search.components/textsearch.cgi?
mode=search&basin=Upper+Yellowstone-Lake+Basin&state=MT&exact=1

Middle Yellowstone- Billings to Custer:

http://waterdata.usgs.gov/nwis-w/MT/search.components/textsearch.cgi?
mode=search&basin=Upper+Yellowstone-Pompeys+Pillar&state=MT&exact=1

Lower Yellowstone-Bighorn to Locate:

http://waterdata.usgs.gov/nwis-w/MT/search.components/textsearch.cgi?
mode=search&basin=Lower+Yellowstone-Sunday&state=MT&exact=1

Lower Yellowstone-Terry to Sidney:

http://waterdata.usgs.gov/nwisw/MT/search.components/textsearch.cgi?
mode=search&basin=Lower+Yellowstone&state=MT&exact=1

Credits
Contributing Teacher: Robert Cheeseman
NTEN Course: ESCI 517 Electronic Hydrology
Instructor: Steve Custer
Developing Team:
T.L. Buck Buchanan, Patti Harrison, Don Samuelson, John Usher, Don Wilson
Original HTML Editors: Tyson N. Trebesch, Ryan Huddleston

Copyright © 1998 - 2002, National Teachers Enhancement Network
Comments: pattih@montana.edu
Phone: 1-800-282-6062
Last Updated: 13 December 2002
at the Burns Telecommunications Center
on the campus of Montana State University-Bozeman

Up to Top