Hydrology/Hydrograph Calculations Excel Spreadsheets

Introduction to Hydrology/Hydrograph Calculations Excel Spreadsheets

For hydrologyhydrograph calculations Excel spreadsheetsclick here to visit our spreadsheet store.  Read on for information about hydrographs, their components and baseflow separation.

Use of hydrographs in hydrology applications often involves calculations with tables of values.  Thus Excel spreadsheets are very useful for such calculations.  Read on for information about the components of hydrographs, baseflow separation, generation of unit hydrographs and use of unit hydrographs.

What is a Hydrograph?

For use in hydrology, the term hydrograph means a graph or table of values showing the changes in flow rate over time at a point on a river or stream or some other point of interest.  Possible points of interest for a hydrograph include locations like a storm water drainage outlet from a drainage area or the entrance to a storm water detention system.  Hydrographs are used to show flow patterns following a storm, thus providing information about the storm water runoff rate at the point of interest.

Excel Spreadsheets for Hrdrology Hydrograph Calculations FigureFor a storm hydrograph at a point on a river or stream, there will typically be a gradually decreasing flow rate before the beginning of the storm.  After the storm begins, the flow rate increases as storm water runoff from more of the drainage area reaches the river.  The flow rate (discharge) will typically increase to a peak value and then gradually decrease to the pre-storm level again, as shown in sample hydrograph in the figure at the right.  For a hydrograph where there’s no non-storm water flow, the hydrograph will start at zero flow prior to the storm and go back down to zero flow.

Hydrograph Components – Baseflow and Direct Runoff

Spreadsheets for Hydrology Hydrograph Calculations FigureThe flow represented by a hydrograph for a point on a river or stream is considered to be made up of two parts, the baseflow, which is the normal dry weather flow of the river or stream, and direct runoff, which is the component of flow due to storm water runoff. The direct runoff due to the storm is often the part that is of interest.  In order to determine the direct runoff from a storm, its necessary to separate the baseflow from the hydrograph, leaving the direct runoff hydrograph.  The diagram at the left shows three methods used for baseflow separation: the constant discharge method; the constant slope method; and the concave method.

The constant discharge method simply uses a horizontal line from the point where the hydrograph begins to rise to its intersection with the receding limb.  The baseflow separation line for both the constant slope method and the concave method should intersect with the receding limb at the inflection point (where the receding limb changes from convex to concave).  The time from the peak of the hydrograph to the inflection point of the receding limb is often calculated with the equation:  N = A0.2,  as shown on the diagram.  This is a dimensional equation, in which A is the watershed area in mi2, and N is time in days.  The constant slope method uses a straight line from the point where the hydrograph begins to rise to the inflection point on the receding limb.  The concave method extends the pre-storm slope of the baseflow line to a point directly below the peak and then uses a straight line to the inflection point on the receding limb.

For Excel spreadsheets to make a variety of hydrograph calculations, including baseflow separation by the concave method, click here to visit our spreadsheet store.

Reference

McCuen, Richard H., Hydrologic Analysis and Design, 2nd Ed, Upper Saddle River, NJ, 1998.

 

Storm Sewer Hydraulic Design with Excel Spreadsheets

Where to Find Storm Sewer Hydraulic Design Spreadsheets

For storm sewer hydraulic design spreadsheets, click here to visit our spreadsheet store.  Read on for information about the use of Excel spreadsheets for storm sewer hydraulic design calculations with the Manning Equation.

One part of storm sewer hydraulic design is determination of the design pipe diameter and sewer slope for the storm sewer pipe between adjacent manholes.  Although storm sewers are circular pipes, the storm water typically flows under gravity, rather than as pressure flow, so the Manning equation for open channel flow can be used for the calculations.  A storm sewer hydraulic design spreadsheet typically makes hydraulic calculations for full pipe flow.  For full pipe flow, the hydraulic radius becomes: R = A/P = (πD2/4)/(πD) = D/4.

The Manning Equation in a Storm Sewer Hydraulic Design Spreadsheet

The general form of the Manning equation in terms of velocity is: V = (1.49/n)(R2/3)(S1/2) for U.S. units and  V = (1.0/n)(R2/3)(S1/2) for S.I. units.  As noted above, R = D/4 for full pipe flow, so the Manning equation in U.S. units becomes  V = (1.49/n)[(D/4)2/3](S1/2) -U.S. units or V = (1.0/n)[(D/4)2/3](S1/2) – S.I units, for full pipe, gravity flow in a storm sewer pipe.  The parameters in the equations are as follows:

  • V is the flow velocity in the pipe (ft/sec – U.S. and m/s – S.I.).
  • n is the Manning roughness coefficient, an empirical, dimensionless constant.
  • D is the pipe diameter (ft -U.S. and m – S.I.).
  • S is the pipe slope, which is dimensionless.

The volumetric flow rate is related to the other parameters through the equation Q = VA or, for a circular pipe flowing full:  Q = (πD2/4)V, where Q will be in cfs for U.S. units or m3/s for S.I. units.

Calculation of Diameter and Slope with a Storm Sewer Hydraulic Design Spreadsheet

Diagram for Storm Sewer Hydraulic Design SpreadsheetThe required diameter and slope for the length of storm sewer between two manholes can be calculated with a storm hydraulic sewer design spreadsheet using the equations presented in the last section (Mannings equation and Q = VA) together with the typical design criteria that 1) the full pipe flow rate that the pipe can carry must be at least equal to the design peak storm water runoff rate to the inlet for that section of storm sewer and 2) the full pipe velocity must be equal to or greater than a specified minimum velocity.  The diagram above shows a sectional view of a storm sewer pipe between two manholes and the parameters being discussed here. The calculation procedure is illustrated by the example in the next section.

Example Storm Sewer Hydraulic Design Calculations

Problem Statement: For a section of storm sewer between two manholes, the design flowrate is: Qdes = 6.4 cfs. The required minimum full pipe storm water velocity is: V min= 3 ft/sec.  The Manning roughness coefficient (concrete pipe) is: n = 0.011.  Find a standard pipe diameter and sewer slope that will meet the two criteria: Qfull > Qdes and Vfull > Vmin for this section of storm sewer pipe.

Problem Solution: First the pipe diameter needed for a full pipe velocity of 3 ft/sec at design flow rate will be calculated using the equation: Q = VA.   Then the Manning equation will be used to calculate the sewer slope needed to give full pipe velocity equal to 3 ft/sec with the next larger standard pipe size.

Step 1:  The equation, Q = VA becomes: Qfull = Vfull(πD2/4). Substituting known values for Qfull and Vfull, the equation becomes: 6.4 = 3(πD2/4).  Solving for D gives: D = 1.65 ft = 19.8 in.  From the list of standard storm sewer pipe sizes in the next section it can be seen that the next standard size larger than 19.8 inches is 21 “, so that will be used for the diameter.

The Manning equation will then be used to calculate the slope for D = 21 in. = 1.75 ft, and V = 3 ft/sec. The Manning equation is: V = (1.49/n)[(D/4)2/3](S1/2).  Substituting values for V, D, and n gives:  3 = (1.49/0.011)[(1.75/4)2/3](S1/2).  Solving this equation for S gives: S = 0.00148.

Thus, the solution is: D = 21″, S = 0.00148. These values of D and S will give Qfull > 6.4 cfs, because Qfull = 6.4 cfs for Vfull = 3 ft/sec and D = 19.8″. With D = 21 ” and V = 3 ft/sec, Qfull must be greater than 6.4 cfs. The equation Q = (πD2/4)V can be used to check this.

Standard Pipe Sizes

Standard U.S. pipe sizes in inches for most types of pipe used as storm sewers:                          4, 6, 8, 10, 12, 14, 16, 18, 21, 24, 27, 30, 33, 36, 39, 42, 48, 54, 60

Standard S.I. pipe sizes in mm for most types of pipe used as storm sewers:                           100, 150, 200, 250, 300, 350, 400, 450, 500, 600, 650, 700, 750, 800, 850, 900, 950, 1000, 1050

Use of Excel Spreadsheets for Storm Sewer Design Calculations

For information on making storm sewer calculations with Excel spreadsheets, see the related article: “Excel Spreadsheets for Storm Sewer Hydraulic Design.”  For low cost, easy to use spreadsheets for several types of storm water calculations, including storm sewer hydraulic design, click here to visit our spreadsheet store.

References

1. Bengtson, Harlan H., Hydraulic Design of Storm Sewers, Including the Use of Excel, an online, continuing education course for PDH credit.

2. McCuen, Richard H., Hydrologic Analysis and Design, 2nd Ed, Upper Saddle River, NJ, Prentice Hall, 1998.

3. Steele, E.W. and McGhee, T.J., Water Supply and Sewerage, New York, NY, McGraw-Hill Book Co, 1979.

4. Bengtson, Harlan H., Hydraulic Design of Storm Sewers with a Spreadsheet,” an Amazon Kindle ebook

5. Bengtson, Harlan H., “Hydraulic Design of Storm Sewers with Excel”  an online blog article.