During the past few years, I have had a datalogger and transducer installed in the backwater of a v-notch weir, which in this case is a large plywood panel set vertically in the bed of an agricultural drainage ditch near Crookston, Minnesota. Water backs up behind the weir and is forced to flow through a large v-notch cut in the top. The purpose of the installation is to get a nearly continuous record of the height of the water stored behind the weir. If one knows the height of the water behind the v-notch weir, then the discharge (volume per time) of water through the v-notch can be found using the following formula:
0.43 * g1/2 * tan (q/2) * H
where g is gravitational acceleration (32.2 ft s-2)
q is the angle of the v-notch (degrees)
H is the height of water behind the weir (feet)
We want to create a graph showing the discharge of the ditch in cubic feet per second (y-axis) as a function of time (x-axis), where minor tick marks show the start of each day and major tick marks show the start of each week, beginning with 29 July 2001. (Little discharge occurred before this date). Follow the basic steps below, but ask if you need help.
1. Download and unzip the data file (n_weir100101.dat). Import this into EXCEL. The first line gives serial number of the datalogger. Use the "Text to Columns" command under the "Data" menu item to get the remainder of the records (day, time, and water level) into separate columns.
2. Unfortunately, the logger was not calibrated correctly. All of the measurements are 15 millimeters greater than the actual water level behind the weir. (There are 3.281 feet per meter). Calculate the correct measurements in a new column to the right. Next, copy the water level column and paste the numbers into the next column, replacing any water level below 0.05 feet with a zero. (Use EXCEL’s conditional IF statement: =IF(logical_test,value_if_true,value_if_false)). This column will now contains the adjusted water level record.
3. In the next column, calculate the discharge using
the formula given above. The weir has an angle "q" of 88 degrees. (Note
that EXCEL assumes radian measurements, so the equation will require a
unit conversion: 1 radian = 57.2957795 degrees). Type the value of "g"
in cell C1 and then reference this cell location in the equation,
rather than typing 32.2. Note – when you copy a column of formulas and
you want to reference a fixed cell location in the formula, use
"$"s, i.e. $C$1. The dollar sign "fixes" the column ($C) and the row
($1)
4. Next, work on the dates and times. We want to
convert the separate date and time cells into a single date with
time cell. Copy columns A and B to the next two open columns to the
right. Convert the format of the date to a number with two decimal
places. Convert the time column into a number with three decimal
places. Add them together in the next column to the right. Convert
these to the date – time format "3/14/01 13:30".
5. Click on the graph wizard button and create a
scatter plot of date – time on the x-axis against discharge on the
y-axis. (What happens if you try to create the graph using "line plot"
instead?) You will need to select the proper series for the discharge
(y values) and time (x values).
6. Work on the attributes of the graph until it looks
the same as this one. A right click on the
various elements of the graph will generally give you a drop-down menu
for making changes. Turn in a hard copy of your graph with your name on
it.
