USDA Forest Service

Pacific Southwest Research Station
Pacific Southwest
Research Station

800 Buchanan Street
Albany, CA 94710-0011
(510) 883-8830
United States Department of Agriculture Forest Service. USDA logo which links to the department's national site. Forest Service logo which links to the agency's national site.

Research Topics

Water & Watersheds: Fine Sediment in Pools

Instructions for using the spreadsheet "Fip"
  1. 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
    1. To calculate systematic cross-section locations:
      1. 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.
      2. 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.
    2. 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.
  2. 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.
  3. 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.
  4. 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.
  5. Hit F9 to calculate and read total volume, fine sediment volume, and V* in A75, B75, and C75.
  6. 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.
  7. 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 xsec.
  8. 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"
  1. Enter the name of the creek (or reach or study unit) in B1
  2. 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).
  3. Press F9 to calculate.
  4. 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.
  5. 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:
Sue Hilton
(707) 825-2927

Last Modified: Aug 29, 2016 11:25:40 AM