Where to Find a Detention Pond Routing Spreadsheet

For detention pond routing spreadsheet to carry out routing calculations and plot inflow and outflow hydrographs, click here to visit our spreadsheet store.  Read on for information about the use of a storm water detention pond routing spreadsheet.

Overview Detention Pond Routing with a Spreadsheet

A detention pond routing spreadsheet is used to project an outflow hydrograph from a stormwater detention pond based on a given inflow hydrograph, stage-storage information for the pond, and stage-outflow information based on the outflow control device.  An output from the routing process is typically a plot of the inflow and outflow hydrographs similar to that shown at the right.  The outflow is often controlled by a rectangular weir, an orifice, and/or a pipe.  In some cases two-stage control is used with perhaps an orifice to provide outflow control for small storms and a weir to control the outflow rate from larger storms.  The routing process should be set up so that changes can be made in outflow control parameters and effects on the outflow hydrograph can then be observed.

Input Information Needed for a Detention Pond Routing Spreadsheet

In addition to an inflow hydrograph like that shown above, stage-storage and stage-outflow information is needed for a detention pond routing spreadsheet.  The stage-storage information would typically be in the form of a table, graph, or equation showing the pond volume, V, as a function of the pond depth, h.  The stage-outflow information is typically in the form of an equation for outflow, O, as a function of pond depth, h, based on the type of outflow control device, as described in the next section.

Stage-Outflow Equations for a detention pond routing spreadsheet

A rectangular weir is one possible outflow control device, often in a riser as shown in the diagram at the left.  The equation for pond outflow  is:     O = C_dL(h – P)^1.5 where the parameters in the equation are as follow:

• O = pond outflow = discharge over the rectangular weir in cfs for U.S. units (m³/s for S.I. units)
• C_d = the discharge coefficient for the weir.  Typical value for U.S. units is 3.3 (1.84 for S.I. units)
• L = weir length in ft for U.S. units (m for S.I. units)
• h = stage (depth of water in pond) in ft for U.S. units (m for S.I. units)
• P = height of weir crest above pond bottom in ft for U.S. units (m for S.I. units)

Equations like this are also available for an orifice outlet, two stage outlet, and pipe outlet.  These equations are given and used in the detention pond routing spreadsheet in either S.I. units or U.S. units in  our spreadsheet store.

The Storage Indication Routing Equation for Detention Pond Routing Spreadsheet Calculations

In addition to the input information described above, a routing equation is needed for a detention pond routing spreadsheet.  A commonly used routing equation is the Storage Indication Equation:

0.5(I₁ + I₂ )Δt  +  (S₁ – 0.5O₁Δt)  =  (S₂ + 0.5O₂Δt) Where:

• Δt is the time interval used for the inflow and outflow hydrographs in minutes
• I₁ and I₂ are successive values of the inflow from the inflow hydrograph (cfs – U.S. or m³/s – S.I.)
• S₁ is the initial value of pond storage (pond volume at the beginning of the storm in cfs – U.S. or m³/s – S.I.)
• O₁ is the initial outflow rate at the beginning of the storm in ft³ – U.S. or m³ – S.I.)
• S₂ and O₂ are the pond storage and outflow respectively at time Δt after the beginning of the storm in the same units shown above.

For a given inflow hydrograph, I₁, I₂ , and all subsequent values of inflow for the duration of the storm are known.  Thus if the initial pond volume, S₁, and initial pond outflow, O₁, are known, then all of the parameters on the left hand side of the equation are known so the value of the right hand side of the equation (S₂ + 0.5O₂Δt) can be determined.

Now comes the elegant part of the storage indication routing procedure.  As described above S vs h and O vs h must be available, in the form of tables, graphs or equations.  Thus for any value of h, the parameter, S + 0.5OΔt can be determined and values of S and O can be determined for a known value of S + 0.5OΔt.  Thus, by stepwise calculations in a detention pond routing spreadsheet, the outflow hydrograph (O vs t) can be obtained.

An Excel Spreadsheet as a Pond Routing Calculator

The template shown below is a  detention pond routing spreadsheet to carry out the procedure described above.   Why bother to make these calculations by hand?  This Excel spreadsheet can handle rectangular weir, orifice, two-stage (orifice/weir), pipe outflow control, and two-stage (pipe/weir), and is available in either U.S. or S.I. units at a very low cost in our spreadsheet store.  These spreadsheets also generates a table and graph showing the inflow and outflow hydrographs for a given set of input parameters.

References

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