Flow Duration Curves: Spreadsheet and PowerPoint Tutorial

1
Flow Duration CurvesSpreadsheet and PowerPoint
Tutorial
Office of Water Quality Indiana Department of
Environmental Management November 2003
By Ernest L. Johnson III1 and Bruce
Cleland2 1Assessment Branch, Office of Water
Quality Indiana Department of Environmental
Management 2525 North Shadeland Avenue, Box
6015 Indianapolis, IN 46206-6015 2Americas
Clean Water Foundation 750 First Street N.E.
Suite1030 Washington, D.C. 20002 IDEM
32/02/083/2003
2
Assumptions for Tutorial

• Process of the tutorial was done using the
  following programs
• Internet Explorer 6.0
• Excel 97 SR-2
• PowerPoint 97 SR-2
• This is a draft copy.
• Issues regarding the mechanics of the spreadsheet
  or tutorial contact ejohnson_at_dem.state.in.us
• Issues regarding Flow Duration concepts contact
  b.cleland_at_acwf.org

• Problems encountered
• Netscape Navigator does not save U.S.G.S.
  discharge data in .txt form
• All workbooks in Excel 97 need to be open in the
  same session of Excel
• If you print this please do not do so in color.
  You will need to uncheck the Black White
  check box before printing or you will not get any
  backgrounds.

3
Go to http//waterdata.usgs.gov/nwis/sw This is
where you will get your flow data. Click the
Streamflow button.
4
Click the State box under Site Location, then
click Submit.
5
Select Your State and click Submit
6
Find the gauging station nearest to your sampling
area and click on the Site Number.
7
For example purposes, Ive chosen Eel River near
Logansport. The data set is current. Note if
doing a Load Duration Curve the flow data must
include the dates your samples were collected.
8
When you get to this screen, drop down the
Available data for this site and Select
Station Home Page.
9
Here is where you will copy the Station
Description information into the Duration Curve
Spreadsheet. 1. Click and Drag your cursor from
Location to the end of the email address
h20team_at_usgs.gov. 2. Select Edit and then Copy
from the menu bar, or press Ctrl C.
10
This is what the screen will look like when you
select the data.
11
Notice the cells with the red triangle in the
upper right corner? Place your cursor over
these for helpful information.
Open your !WQ Tool(Template).xls file. Select
the cell you see here A4 and click the Paste
button or type CtrlV. You may need to adjust the
column width if your data show a bunch of
. In this workbook, anyplace you see
Neon Green is a place where you will need to
enter data. Also, when copying data from a web
page it may not post just like this. You may
need to delete out the original data and paste in
as you see fit.
12
You can switch between the USGS site page and
here to either copy or retype the information for
Station Description in A1 and the information
for Station ID in H2, Station Name H3, and
Drainage area G4. This information is linked
throughout the workbook and you wont need to
retype them again. If you Copy, Paste the above
information from the USGS Website, you should use
Paste Special, Values.
13
Notice that the Station ID and Name have copied
over automatically here for you.
Click on the Raw_Data tab at the bottom and
then go back to the USGS site to finish your flow
data retrieval. We wont do anything here just
yet.
14
To get here we clicked in the Available data for
this site and Selected Surface-water Daily
streamflow.
If you do not want/need all the data, you can
select your own dates by typing in the range of
interest.
Select the Tab-separated data button and Click
Submit
15
You will get the File Download screen. Click
Save. The default name is discharge. Leave
the default name as it appears. The name must
be the same or the copy program will not work
later in the spreadsheet.
16
Ive created a folder in My Documents as a
standard place to put the discharge file. Notice
that there is only one file. We do not need to
keep the raw data once we copy it into our
spreadsheet, so I will just overwrite the
previous file.
17
The computer will tell you the file exists and
Do you want to replace it?. Click Yes.
18
Now we want to open the file. Click Open.
If you do not get the Download Complete window,
Do Not Panic. You will need to manually open the
file. Go to your Excel session and select File,
Open and select the discharge file from the
folder it was placed.
19
Sometimes Windows wont know which program to
open the file in and you will get the Open With
window. Simply select Microsoft Excel and click
OK.
20
You should now be looking at the raw data from
the USGS. Now, go back to the Flow Duration
spreadsheet. Click Window and select the !WQ
Tool workbook. You may have noticed that we
have yet to save your working file. Do not
worry, this will happen shortly. Well save it
when you have moved your raw data (and sample
data if doing a Load Duration Curve).
21
Now that you are back to the !WQ
Tool(Template).xls workbook, be sure you are in
the Raw_Data worksheet. The data will not show
until you do the next step. Click on the Click
here to Get Data from USGS discharge file
buttonandvoilayour data should appear. If
not, seek some assistance.
We are only doing a Flow Duration Curve for this
tutorial, so you can now save the workbook. This
is a read-only document so it will ask you for a
new name.
22
Here is the Flow Duration Summary. You will be
copying the cfs data into the PowerPoint
template, but well get to that in a second.
Were in the Flow Duration Spreadsheet.
23
You need to open the Flow Analysis(Template)
PowerPoint File.
Here is the template that we will be changing.
Start by double clicking on a blank area of the
graph.
24
You should see a spreadsheet open up, if not
click View, then Datasheet. Youll want to
expand the data sheet to see more of the column
data.
25
We will be copying our cfs flow data from Excel
into this column.
These numbers are what will draw your vertical
division lines at 10, 40, 60 and 90 percent.
More on this later.
26
Copy the cfs data by selecting the data with your
mouse and typing Ctrl C. Go back to the
PowerPoint template.
27
Select the first cell next to the 0.005 and
paste your copied data here by selecting the
paste button or typing Ctrl V
Notice that as soon as you pasted your data your
brown flow line moved, and your black vertical
lines are now offset. We will adjust these lines
in the next screen.
28
Leave the cells with 0.001 alone and for the
other cells with 10, 40, 60, and 90 type in the
corresponding values from your Flow column
(Column A).
As you change the values you should see the black
line move to meet the brown flow line. If you
want the vertical lines to extend to the top,
change the values to the max on your y-axis.
29
Now for the cosmetic changes. You should now
change the following text boxesTitle, cfs
values, and square miles. Any place there is
text you can modify.
30
You now have a completed Flow Duration Curve.
You can move any of the text boxes around by
simply clicking on it and dragging it to a new
location.
31
Now is a good time to save your graph using
whatever naming scheme you prefer.
The next couple screens show you how to insert a
picture into your PowerPoint.
32
Select Insert, Picture, From File, then go to the
location where the picture is you want to insert.
33
Resize and place where you want. There are many
other things you can do with PowerPoint to
spice it up if you like, just ask.
34
Suggested Reading

• Cleland, Bruce, 2003, TMDL Development
  from the Bottom Up -- Part III Duration Curves
  and Wet-weather Assessments, http//www.tmdls.net/
  tipstools/docs/TMDLsCleland.pdf

35
The End