Use of Excel Formulas in a Spreadsheet Template to Put it all Together
The Excel spreadsheet template shown at the left contains the design calculations for a storm sewer line along Maple Avenue, from 8th Street to 4th Street, based on the manhole layout map shown in the previous section. The column numbers given in the spreadsheet will be used to discuss the various parts of the spreadsheet calculations.
Columns 1, 2, and 3 contain information from a map that is drawn to scale, like the one in the previous section and used for this example. Column 4 is the cumulative area draining to the downstream sections of sewer pipe. In this example, the manhole at 8th Street and Maple Avenue is assumed to be the uppermost part of this sewer line. Column 3 is an estimate of the runoff coefficient. Column 3 is the inlet time from the farthest point in the drainage area. For the first section of sewer pipe, the inlet time is the time of concentration. For subsequent sections of sewer pipe, the time of concentration is the inlet time to the first inlet plus pipe flow time to the inlet of the pipe section being designed, as given in column 7.
Column 8 is the calculated design rainfall intensity. The portion of the Excel template shown at the right is a linear regression of storm duration, δ, vs the inverse of storm intensity, 1/i, using I-D-F data for the location of interest, to get an equation for storm intensity as a function of storm duration. This linear regression makes use of the fact that the relationship between i and δ is typically of the form i = a/(δ + b), where a and b are constants. Column 9 is simply the calculation, Q = CiA.
Columns 10 through 15 make use of the Manning Equation and Q = VA to determine the minimum standard pipe diameter and pipe slope needed, as well as a check on Vfull and Qfull when the pipe is receiving the design stormwater runoff flow rate.
Columns 16 and 17 are used to calculate the pipe flow time to be used for the time of concentration calculation in column 7. Columns 18 and 19 give ground surface elevations taken from the manhole layout map. Columns 20 and 21 calculate the pipe invert elevations. The invert elevation of the uppermost end of the pipe is taken to be the surface elevation minus the minimum cover (5' in this case) plus the pipe diameter. The invert elevation at the lower end of the pipe section is calculated using the pipe slope that was previously determined. Columns 22 and 23 are a check on the depth of cover at each manhole, and column 24 is a listing of the final design pipe slope.
In order to see the formulas used for each part of the calculation, click here to download this Excel spreadsheet template (with U.S. units) for storm sewer design calculations.
Click here to download this Excel spreadsheet template (with S.I. units).