Supplement to homework 2

Some tips on using Excel and Quattro for these problems:

Problem 1:

Whether you use Excel or Quattro for problem 1, there are a couple of basic steps. Although you can sort and rank the values in the data set and divide the rank by n in order to get cumulative probability and exceedence probabiliity , you don't actually need to calculate this probability for every single one of the data points. In fact both Excel and Quattro have functions that perform a frequency analysis without requiring as much manipulation. In Excel you use the histogram function; in Quattro you use the frequency function.

A. If you are using Excel:

1) Go to the "Tools" menu, click on "Add-ins" and make sure the "Analysis ToolPak " option is checked.

2) Make sure you create a list of values, in ascending order, that correspond to the same set of values that are listed under "Bin" in the frequency analysis on the upper right part of the watt7787 file. You can copy the list from one file and paste it into another.

3) Go back to the Tools menu and click on "Data Analysis" and then click on the "Histogram" option. This option also does frequency analyses , whether or not you decide to make a chart.

4) Highlight the input range in the appropriate column in the spreadsheet. You can use the mean daily flow values without sorting and it should work without going through the other steps we discussed. Instead of calculating the exceedence probability for every daily value, you will get values only for the values in the "bin" column.

5) Pick a location in the spreadsheet that will be the upper left corner of output range and identify its address in the "Output Range" box. Make sure this box is highlighted. Also check the "Cumulative Percentage" box. Click on the "OK" button and the output will be placed at the specified location.

6) In order to calculate the exceedence probability, type the following formula in the column next to the "cumulative frequency" column. Assuming that the first cell in the "cumulative frequency" column is R4 (this depends on where you placed your output), the formula in S4 should be "=1- R4 ". Copy this formula down the column.

7) Excel cannot make probability plots, therefore you can plot the exceedence probability on the x-axis and the flow value in the bin column on the y-axis of the plot that was handed out in class. If you are working with the 1957--1967 data set, draw a dashed line to distinguish this flow duration curve from the printed curve.

B. If you are using Quattro:

1) Although Quattro also has a Histogram option, it treats the bins in a different manner from Excel. Although last week I told you I prefer Quattro over Excel, I do not recommend using the histogram function in Quattro or you will get inconsistent results. So this part of the exercise will actually take a little more work in Quattro.

2) Go to the "Tools" menu and select "Numeric tools" and then "Frequency". Highlight the input column and the bin block. When you hit "OK" the frequency for each value in the bin block will appear in the column immediately to the right, so make sure this part of the spreadsheet is empty. The frequency will be the number of values from your input column that are greater than the previous entry in the bin block and less than or equal to the current value in the bin block. (The problem with the Histogram option is that it uses a different definition; it counts the values greater than or equal to the current value and less than the next value.)

3) Once you have your output, you can use the formulas given in the columns in the watt7787 spreadsheet that are labeled "Cumulative probability", " Exceedence probability", "Cumulative percent", and " Exceedence percent". These will give you the same results as the Histogram option in Excel. Although it takes a few more steps, you will actually be able to see what you are doing rather than having it appear magically on the screen without an explanation.

Problem 2:

This should work pretty much the same way whether you are in Quattro or Excel. There are minor differences in the way formulas are written (start with "=" in both and you'll be ok; Quattro can use other options as well, but don't worry about those). I have given you all the data you need, but you will need to manipulate the data in order to be able to get the output you need.

1. Under "rainfall increments" you want to calculate the amount of rain that fell between the previous sampling point and the current sampling point, then convert it to an intensity in inches per hour. This is a simple subtraction and division operation, i.e. subtract the cell above the cell to your left from the cell to your left, then divide the result by the length of the time interval in hours. Although the gage records discharge at 15-minute increments, I have skipped some of these increments so you need to make sure that you calculate the length of the time interval instead of just assuming it will be 0.25 hour. The subtraction and division operation should be incorporated in a single formula. Copy this formula down the column to get all of the individual rainfall increments.

2. Under "Discharge in inches/hour" you simply need to convert seconds to hours and convert cubic feet to the equivalent number of inches spread over the total watershed area. (The watershed area is given at the top of the spreadsheet.) Convert directly from the discharge values in column D.

3. Instead of "cumulative discharge" create a column headed "flow volume". Here you need to calculate the total volume of flow that passed the stream gage between the previous sampling time and the current sampling time. Because you have a beginning and an ending value of flow rate, you need to calculate the average flow rate for that time interval and multiply it by the length of the time interval to get the volume of flow during that time interval. You can do this in inches of runoff, since that is the column immediately to your left. Copy your formula down the column.

4. In order to calculate cumulative runoff (in inches), you need to write a formula that adds the amount of flow from the most recent time interval (this should be in the cell immediately to your left) to the total amount of flow accumulated during all previous time intervals (this should be in the cell immediately above you; for the first time interval that cell will be empty). The formula is very simple. If you can't figure out how to do it, ask me or one of the other students in the class, don't waste hours thinking about it. Again, copy your formula down the column.

5. Make two plots, both with time on the x-axis. The first plot should show both rainfall increments and discharge values on the y-axis, so you can see the temporal distribution of rainfall in comparison with the shape of the hydrograph . In order to compare these using the same y-axis, plot discharge in inches/hour rather than in cfs.

The second plot should show cumulative rain in inches and cumulative runoff in inches. Discuss the comparisons in both plots.