800 Buchanan Street
Albany, CA 94710-0011
Water & Watersheds: Fine Sediment in Pools
About this Research:
Instructions for using the spreadsheet "Fip"
- Enter creek, pool #, riffle crest depth (can be 0), pool
length, and number of cross sections in the first row: B1, D1, F1, H1, and J1
- To calculate systematic cross-section locations:
- Hit F9 (calculate) to calculate a random
location for the first cross-section. That location will be calculated
in D3. Round to the nearest decimeter and copy to C3.
- Hit F9 again to calculate the locations of all subsequent
cross-sections and a random number to start measurement on each. Cross-section
locations will be in c7, c11, etc, and the random numbers will be in d7,
d11 etc. Round these numbers to the nearest decimeter and type over the
formulas so they won't accidentally get recalculated.
- If you already have cross-section locations or want to define
your own, enter those values in C3, C7, C11, etc. Cross sections must be
in order, with the most upstream first.
- Measure or enter the measured numbers for distance along the
cross-section, water depth, and fine sediment depth at your first measurement
point (the first point where the combined water + sediment depth is > r.c.d.)
in B4, B5, and B6. We use the random number to tell us where to take the
second measurement point on a transect (the first point after the edge-of-the-pool
point), and then measure at constant distances after that. Continue entering
data from the first transect in C4, C5 & C6 and so on until you leave
the pool or run out of data.
- Enter the cross section distances, water depth, and fines
depth for the next cross-section in B8, B9, and B10 and so forth through
all the cross-sections.
- Enter the total length of the pool as the cross-section location
for the cross-section beyond the last measured cross-section: i.e. if you
measured 4 cross sections and the pool is 15.3 meters long, than cross-section
5 is at 15.3 meters. This cross section doesn't have to have any data,
but you can enter some 0's if you like.
- Hit F9 to calculate and read total volume, fine sediment volume,
and V* in A75, B75, and C75.
- We usually copy down the values for total water, total fines,
and V* and then save just the data for the pool, either with file extract
(for the old-style 123) or by coping and pasting to a new spreadsheet.
- There's a 123 macro to plot water and fines depth. To use
it, go to the first cross-section distance point on that cross-section
(cell B4 for the first xsec) hit alt-F3, and choose G, or, if you're in
later Lotus, go to tools-macro-run and choose G. Hit enter to leave the
plot. Scroll down 4 (to cell B8, B12, \'85) and repeat to see the next
- This spreadsheet will take up to 25 points on a cross-section
and up to 11 cross-sections in a pool.
Instructions for using the spreadsheet "Varv"
- Enter the name of the creek (or reach or study unit) in B1
- Enter the pool number, water volume, and fine sediment volume
for each pool in A3, B3, & C3 and so forth (don't enter V*, it's calculated).
- Press F9 to calculate.
- The total and mean pool water volume for the reach are in
B25 and B26 ,and the total and mean fine sediment volumes are in C25 and
C26. Reach mean V* is in B32 and the standard error of the estimate for
V* is in B34.
- To add more pools, insert any number of lines between row
3 and row 24 and copy the formulas down from the row above. The formulas
should expand to include the new pools, but check to make sure that E1,
the number of pools, says the actual number of pools you have.
You're welcome to change the spreadsheets any way you like. If you have
questions, call me: