Here you will find informational articles on topics related to the Excel spreadsheets for civil and mechanical engineering calculations available from the DOWNLOADS page. This includes articles in the clickable categories below: pipe flow calculations, open channel flow, heat transfer/heat exchangers, storm water/hydrology, continuous beam analysis and design, open channel flow measurement, and pipe flow measurement topics. Scroll down on each category page to see all of the articles.
Similar blog articles are available at our companion site, www.EngineeringExcelSpreadsheets.com.
To obtain an energy grade line and hydraulic grade line calculation spreadsheet, click here to visit our spreadsheet store. Intended for use in calculating and plotting HGL and EGL for up to 10 points of stormwater flow along a storm sewer line, you can buy a convenient spreadsheet to make HGL and EGL calculations and plots for a very reasonable price. This spreadsheet is available in either U.S. or S.I. units. Read on for information about a spreadsheet for calculating and plotting storm sewer hydraulic grade line and energy grade line.
The equations for the Hydraulic Grade Line and Energy Grade Line (EGL) are:
HGL = hydraulic grade line in ft EGL = energy grade line in ft
P = pressure in psf γ = specific weight in lb/ft3
h = elevation in ft V = velocity in ft/sec
g = acceleration of gravity in ft/sec2
An Energy Grade Line and Hydraulic Grade Line calculation spreadsheet is partially shown in the image below. This Excel spreadsheet can be used to calculate and plot the hydraulic grade line and energy grade line for up to ten points of stormwater flow through a storm sewer. This Excel spreadsheet, as well as others for stormwater management calculations, is available in either U.S. or S.I. units for a very reasonable price in our spreadsheet store.
1. Bengtson, Harlan H., Hydraulic Grade Line and Energy Grade Line Spreadsheet, An informational blog article
Where to Find a Chicago Storm Hyetograph Generation Spreadsheet
To obtain a Chicago Storm Hyetograph Generation excel spreadsheet, click here to visit our spreadsheet store. For use in stormwater management calculation, you can buy a convenient Chicago Storm Hyetograph generation excel spreadsheet for a very reasonable price? This spreadsheet will generate a design storm hyetograph using the Chicago storm model, a triangular storm model or the "alternating blocks" procedure in either U.S. or S.I. units. Read on for information about Excel spreadsheets that can be used for Chicago storm hyetograph generation.
Models for Design Storm Hyetograph Generation
Several different hyetograph models can be used for design storm hyetograph generation, including the Chicago storm, triangular, or rectangular (constant intensity design storm) models or the "alternating blocks" procedure for constructing a design storm hyetograph. An initial step typically needed is the generation of an equation for storm intensity as a function of storm duration at the design location, for the design recurrence interval.
The Chicago Storm Hyetograph
For example, the Chicago storm hyetograph model uses the equation at the left for the portion of the hyetograph before the peak storm intensity. A slightly different equation is used for the portion of the design storm hyetograph that is after the peak storm intensity. The resulting hyetograph has the general shape shown in the diagram at the right. A user specified parameter is r, which is the fraction of the hyetograph that is before the point of peak storm intensity. The triangular hyetograph model is similar in shape, but the lines before and after the peak storm intensity are straight instead of curved.
Example Chicago Storm Hyetograph Generation Excel Spreadsheet
The Design Storm hyetograph generation excel spreadsheet partially shown in the image below can be used to generate a triangular or Chicago storm hyetograph as discussed above. The portion shown is for generating an equation for storm intensity as a function of storm duration. This Excel spreadsheet, as well as others for stormwater management calculations, is available in either U.S. or S.I. units for a very reasonable price in our spreadsheet store.
1. American Iron and Steel Institute, Modern Sewer Design, 4th Edition, 1999.
2. Bengtson, Harlan H., "Design Storm Hyetograph Generation Spreadsheet," an online informational blog article.
Scroll down for the following blog articles in this category:
To obtain an Excel spreadsheet for carrying out detention pond routing calculations and plotting inflow and outflow hydrographs, click here to go to our download page. Read on for information about the use of an Excel spreadsheet for storm water detention pond routing.
Detention pond routing is used to project an outflow hydrograph from a stormwaterdetention 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
In addition to an inflow hydrograph like that shown above, stage-storage and stage-outflow information is needed. 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.
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 = CdL(h – P)1.5 where the parameters in the equation are as follow:
Equations like this are also available for an orifice outlet, two stage outlet, and pipe outlet. These equations are given and used in the Excel spreadsheets for pond routing from our download page.
The Storage Indication Routing Equation
In addition to the input information described above, a routing equation is needed. A commonly used routing equation is the Storage Indication Equation:
0.5(I1 + I2 )Δt + (S1 – 0.5O1Δt) = (S2 + 0.5O2Δt) Where:
For a given inflow hydrograph, I1, I2 , and all subsequent values of inflow for the duration of the storm are known. Thus if the initial pond volume, S1, and initial pond outflow, O1, 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 (S2 + 0.5O2Δ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 Ocan be determined for a known value of S + 0.5OΔt. Thus, by stepwise calculations, the outflow hydrograph (O vs t) can be obtained.
An Excel Spreadsheet as a Pond Routing Calculator
The Excel spreadsheet template shown below can be used to carry out the pond routing 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 from our download page. These spreadsheets also generate a table and graph showing the inflow and outflow hydrographs for a given set of input parameters.
1. McCuen, Richard H., Hydrologic Analysis and Design, 2nd Ed, Upper Saddle River, NJ, 1998.
If you want to obtain an Excel spreadsheet to use for storm sewer hydraulic calculations, click here to visit our download page. Read on for information about the use of storm sewer hydraulic calculations with the Manning Equation.
One part of storm sewer hydraulic design is determination of the design diameter and slope for the storm sewer pipe between subsequent 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. Storm sewer hydraulic calculations are typically made for full pipe flow. Under these conditions the hydraulic radius becomes: R = A/P = (πD2/4)/(πD) = D/4.
The Manning Equation Applied to Full Pipe Flow
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:
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 for a Storm Sewer Pipe
The required diameter and slope for the length of storm sewer between two manholes can be calculated 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 at the right 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 Calculation of Pipe Diameter and Slope
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 pipe roughness coefficient (concrete pipe) is: n = 0.011. Find a standard pipe diameter and pipe slope that will meet the two criteria: Qfull > Qdes and Vfull > Vminfor 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 pipe 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 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 download page.
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
Introduction to the Time of Concentration
If you want to obtain an Excel spreadsheet for watershed time of concentration calculations, click here to visit our download page. Read on for information about Excel spreadsheets that can be used for watershed time of concentration calculations.
The time of concentration for a watershed is the time for rainfall that lands on the farthest point of the watershed to reach the outlet. The main reason for interest in the watershed time of concentration is for it use as the storm duration in finding the design rainfall intensity to use in Rational Method calculation of peak storm water runoff rate.
The reason that the time of concentration is used as design storm duration is because it gives the largest peak storm water runoff rate for a given return period. This can be reasoned out as follows: If the storm duration is less than the time of concentration, then the storm will end before runoff from the entire watershed reaches the outlet. Thus flow from the entire watershed will never all be contributing to the outflow. If the storm duration is greater than the time of concentration, then the storm will continue longer than it takes for the entire watershed to contribute to the outflow, but the storm intensity will be less for a storm of longer duration than one of short duration for a given return period. Thus the maximum peak storm water runoff rate for a specified return period on a given watershed will be for a storm with duration equal to the time of concentration of that watershed.
We can now move on to a discussion of how to calculate values for the time of concentration of a given watershed.
Methods for Estimating Time of Concentration
There are several empirical equations that have been developed for calculating travel time/time of concentration for different types and conditions of watersheds. Some examples are the Kerby equation, the Izzard equation, the Manning Kinematic equation, the Bransby Williams equation, the National Resources Conservation Service (NCRS) method, and the Manning equation. The following three equations will be discussed in this article: 1) the Manning Kinematic equation for use with overland sheet flow, 2) the NRCS method for shallow concentrated flow, and 3) the Manning equation for channel flow. These three methods are recommended by the U.S. Soil Conservation Service (SCS) in ref #1 at the end of this article. The Iowa Stormwater Management Manual (ref #2) also recommends these three methods. Typically overland sheet flow will occur in the upper portion of the watershed, followed by shallow concentrated flow, with channel flow for the final portion of watershed before the outlet.
Calculations with the Manning Kinematic Equation
The boxes at the right show the Manning Kinematic equation for U.S. and for S.I. units. The parameters in the Manning Kinematic equation and their units are as follows:
*See table of n values below.
The screenshot of an Excel spreadsheet template shown below will calculate overland sheet flow travel time with U.S. units using the Manning kinematic equation, based on the input values entered for the other parameters listed above. A tables with values of the Manning roughness coefficient for various overland flow conditions is also given below. This Excel spreadsheet and others for time of concentration calculations are available in either U.S. or S.I. units at a very low cost from our download page.
Calculations with the NRCS Method
The Manning Kinematic equation is recommended for travel length of no greater than 300 ft in ref #1 and for no greater than 100 ft in ref #2. Both of these references recommend use of the NCRS method for the shallow concentrated flow that normally develops within 100 to 300 ft into the watershed. The NCRS method calculates the velocity of the shallow concentrated flow first, based on the slope and the type of surface. The travel time is then calculated as travel length divided by velocity of flow. The equations used for the NRCS method are:
An explanation of each of the parameters used in these equations follows:
The screenshot of an Excel spreadsheet template shown at the left will calculate shallow concentrated flow travel time with S.I. units using the NRCS method, based on the input values indicated. This Excel spreadsheet and others for time of concentration calculations are available in either U.S. or S.I. units at a very low cost at www.engineeringexceltemplates.com or in our spreadsheet store.
Calculation of Travel Time with the Manning Equation
The Manning equation is used for quite a variety of open channel flow calculations. It is recommended in ref#1 and ref #2 for any channel flow portion of the watershed runoff path. The following equations are used for Manning equation calculations:
An explanation of the parameters in these equations and their U.S. and S.I. units follows:
The screenshot of an Excel spreadsheet template shown at the right will calculate channel flow travel time with U.S. units using the NRCS method, based on the input values indicated. This Excel spreadsheet and others for time of concentration calculations are available in either U.S. or S.I. units at a very low cost at www.engineeringexceltemplates.com or in our spreadsheet store.
The overall time of concentration can now be calculated as the sum of t1, t2 and t3.
1. U.S. Soil Conservation Service, Technical Note - Hydrology No N4, June 17, 1986.
2. Iowa Stormwater Management Manual, Section on Time of Concentration.
3. Knox County Tennessee Stormwater Management Manual, section on the Rational Method.
4.Bengtson, Harlan H., Hydraulic Design of Storm Sewers, Including the Use of Excel, an online, continuing education course for PDH credit.
5. Bengtson, Harlan H., "Spreadsheets for Rational Method Hydrological Calculations," an Amazon Kindle e-book.
Learn about hydrographs and their use in hyrdologic analysis. This includes how to carry out baseflow separation, how to create a unit hydrograph from a storm hydrograph, and how to predict the runoff from a given storm based on a unit hydrograph for the drainage area.
A hydrograph, as used in hydrology, is a graph or table of values showing the changes in flow rate over time at a point on a river or some other point of hydrologic interest. Such points of interest for a hydrograph, other than a point on a river or stream, include locations such as the entrance to a storm water detention system or a storm water drainage outlet from a drainage area. Hydrographs are commonly used to show flow patterns following a storm, thus providing information about the storm water runoff rate at the point of interest.
A storm hydrograph for a point on a river will typically have a gradually decreasing flow rate prior to a storm. Then after the storm starts, the flow rate will gradually increase as storm water runoff from more and 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 the flow at an outlet from a drainage area, where there is no non-storm water flow, the hydrograph will start at zero flow prior to the storm and go back down to zero flow.
The Parts of a Hydrograph - Baseflow Separation
The portion of the hydrograph between the point where it starts to rise to the peak is called the rising limb and the portion to the right of the peak is called the receding limb.
The area under a hydrograph represents the volume of runoff. It can be considered to be made up of two parts: i) the normal flow that would have been present even if the storm hadn't occurred, called baseflow, and ii) the flow due to the storm, called direct runoff. The direct runoff due to a storm is often of interest, so the baseflow component must be separated from it.
The diagram at the right illustrates three baseflow separation methods: i) the constant discharge method, ii) the constant slope method, and iii) the concave method. The constant discharge method consists of simply extending a horizontal line from the point where the hydrograph begins to rise to its intersection with the receding limb. The intersection with the receding limb for both the constant slope and concave methods is at the inflection point (where the receding limb changes from convex to concave). The time from the peak to the inflection point of the receding limb is often approximated by N = A^0.2, as shown on the diagram, where A is the watershed area in sq mi, and N is time in days.
Excel templates for baseflow separation by the concave method are available from the download page of EngineeringExcelTemplates.com.