ENV* K245: Water Resources Engineering Rainfall and Riverflow

Exercise on Runoff and Precipitation

In this lab we will compare rainfall and streamflow using data from the Internet to practice (a) downloading data and (b) creating worksheets and charts and to visualize the relationship between precipitation input and runoff output in the water balance. We will start with daily values. We will generate a graph of streamflow and rainfall for the month of April 2004. Then, we will convert the daily values to a monthly total expressed as inches over a watershed.

1. Start a spreadsheet to record and process the data.
1. Open a blank Excel worksheet. In cell A1 type the word Date.
2. In cell A2, type in 01-Apr-2004 and hit enter.
3. Using the autofill handle, drag the date down to cell A31. The first column should now have the dates for the month of April 2004.
4. Save your workbook as XXXstreamflow.xls, where XXX is your initials.
2. Streamflow data for Connecticut is available at no charge from the USGS.
1. In cell B1 of your worksheet, type "Q (ft3/s)" (without the quotation marks).
2. In Internet Explorer, go to the CT USGS page: http://ct.water.usgs.gov/.
3. Click the Data button on the upper left.
4. Click on the second Annual Reports link (that mentions 2004).
5. Scroll down and choose "Water Resources Data-Connecticut Water Year 2004-PDF Format."
6. Scroll down and choose Download the Report (PDF, 10.7 MB)
7. Go to the page for the Willimantic River near Coventry (50 of 398 in the page box at the bottom of Acrobat). Note the area of the drainage basin. Copy the data from the April column of the average daily discharge table to cells B2 to B31.
3. Convert the Q values to inches of runoff by dividing the volumes by the drainage area.
1. In cell C1, type "R (in)" (without the quotation marks).
2. In cell C2, type in:

=B2*86400/(Area*5280^2)*12

Where "Area" is the drainage area in square miles. What are the other numbers?

3. Use the autofill handle to drag the formula down to cell B31.
4. Save the workbook.
4. Rainfall data is available via the web, though much of it requires payment of a fee to download. Unedited surface weather observations (USWO) observations are available at no charge from the National Climatic Data Center.
1. In cell D1, type "P (in)" (no quotes).
2. In Internet Explorer, click on the NCDC link on the course homepage (lower half of the page).
3. Click the Start Here link (small letters, first one under Data & Products).
4. Hit the Submit button without checking any boxes.
5. Scroll down and choose the Unedited Surface Weather Obs. Subs (USWO) link.
6. Query by Like Station Name. Type in Willimantic and hit View Station(s). Select the Willimantic station and hit Select a Date.
7. You will need to enter the dates from Apr 01 2004 to Apr 30 2004 by hand. When you have entered a date, hit the 10B – Unedited Daily Summaries button to get a form with daily climate data.
8. The rainfall is the 24-hr Precp Water Equiv (ins) field (#59) in the lower right. Record the rainfall for each day in cells D2 through D31 of the worksheet where you recorded the streamflow data.
9. Save.
5. Create a chart of runoff and precipitation.
1. Highlight cells A1:A31 and C1:D31 (click and drag over A1 to A31, release the mouse, hold down control then click and drag over C1 to D31).
2. Choose Insert, Chart…
3. Select XY (Scatter) as the chart type. Click Finish.
4. Pretty up the chart by connecting the points of each data series with a line and by changing the range of the x-axis to 4/1/2004-4/30/2004.
5. Under Chart Options, add a chart title, an x-axis title, and a y-axis title.
6. Save.
6. Take a look at the graph.
1. What do you see? In particular, what is the relationship between the precipitation line and the streamflow line?
2. Sum the R and P values and compare the totals. Are they the same? Would you expect them to be?
3. Save.

For this lab, turn in print outs of your data and your graph and submit the Excel file by email (abenoit@trcc.commnet.edu). Include answers to the questions in 3.2, 6.1, and 6.2

ENV* K245 home