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.
Scroll down for the following blog articles in this category:
Where to Find Manning Equation Open Channel Flow Excel Spreadsheets
To obtain Manning equation open channel flow excel spreadsheets, click here to visit our spreadsheet store. Why use online calculators or make open channel flow/Manning Equation calculations by hand when you can buy a variety of Manning equation open channel flow excel spreadsheets or spreadsheet packages for prices ranging from $6.95 to $27.95? Read on for information about Excel spreadsheets that can be used as Manning equation open channel flow calculators.
An excel spreadsheet can conveniently be used as a Manning equation open channel flow calculator. The Manning equation can be used for water flow rate calculations in either natural or man made open channels. Uniform open channel flow calculations with the Manning equation use the channel slope, hydraulic radius, flow depth, flow rate, and Manning roughness coefficient. Image Credit: geograph.org.uk
Uniform Flow for a Manning Equation Open Channel Flow Excel Spreadsheet
Open channel flow may be either uniform flow or nonuniform flow, as illustrated in the diagram at the left. For uniform flow in an open channel, there is always a constant volumetric flow of liquid through a reach of channel with a constant bottom slope, surface roughness, and hydraulic radius (that is constant channel size and shape). For the constant channel conditions described, the water will flow at a constant depth (usually called the normal depth) for the particular volumetric flow rate and channel conditions. The diagram above shows a stretch of uniform open channel flow, followed by a change in bottom slope that causes non-uniform flow, followed by another reach of uniform open channel flow. The Manning Equation, which will be discussed in the next section, can be used only for uniform open channel flow.
Equation and Parameters for a Manning Equation Open Channel Flow Excel Spreadsheet
The Manning Equation is:
Q = (1.49/n)A(R^{2/3})(S^{1/2}) for the U.S. units shown below, and it is:
Q = (1.0/n)A(R^{2/3})(S^{1/2}) for the S.I. units shown below.
The equation V = Q/A, a definition for average flow velocity, can be used to express the Manning Equation in terms of average flow velocity,V, instead of flow rate,Q, as follows:
V = (1.49/n)(R^{2/3})(S^{1/2}) for U.S. units with V expressed in ft/sec.
Or V = (1.0/n)(R^{2/3})(S^{1/2}) for S.I. units with V expressed in m/s.
It should be noted that the Manning Equation is an empirical equation. The U.S. units must be just as shown above for use in the equation with the constant 1.49 and the S.I. units must be just as shown above for use in the equation with the constant 1.0.
The Manning Roughness Coefficient for a Manning Equation Open Channel Flow Excel Spreadsheet
All calculations with the Manning equation (except for experimental determination of n) require a value for the Manning roughness coefficient, n, for the channel surface. This coefficient, n, is an experimentally determined constant that depends upon the nature of the channel and its surface. Smoother surfaces have generally lower Manning roughness coefficient values and rougher surfaces have higher values. Many handbooks, textbooks and online sources have tables that give values of n for different natural and man made channel types and surfaces. The table at the right gives values of the Manning roughness coefficient for several common open channel flow surfaces for use in a Manning equation open channel flow excel spreadsheet.
Example Manning Equation Open Channel Flow Excel Spreadsheet
The Manning equation open channel flow excel spreadsheet shown in the image below can be used to calculate flow rate and average velocity in a rectangular open channel with specified channel width, bottom slope, & Manning roughness, along with the flow rate through the channel. This Excel spreadsheet and others for Manning equation open channel flow calculations in either U.S. or S.I. units are available for very reasonable prices in our spreadsheet store.
References
1. Bengtson, Harlan H., Open Channel Flow I - The Manning Equation and Uniform Flow, an online, continuing education course for PDH credit.
2. U.S. Dept. of the Interior, Bureau of Reclamation, 2001 revised, 1997 third edition, Water Measurement Manual.
3. Chow, V. T., Open Channel Hydraulics, New York: McGraw-Hill, 1959.
4. Bengtson, Harlan H., "Manning Equation Open Channel Flow Calculator Excel Spreadsheets," an online blog article, 2012.
Where to Find a Spreadsheet for Non Uniform Flow in Open Channels
To obtain a spreadsheet for calculating backwater curves for non uniform flow in Open channels, click here to visit our spreadsheet store. Obtain a convenient, easy to use Excel spreadsheet for non uniform flow in open channels at a reasonable price. Read on for information about the use of an Excel spreadsheet for non uniform flow open channel surface profile step wise calculations.
Background on Uniform and Non Uniform Flow in Open Channels
The diagram at the right illustrates uniform and nonuniform open channel flow. Uniform flow in an open channel consists of a constant volumetric flow of liquid through a reach of channel with a constant bottom slope, surface roughness, and hydraulic radius (that is constant channel size and shape). For those constant channel conditions, the water will flow at a constant depth, called the normal depth, for the particular channel conditions and volumetric flow rate. The diagram shows a reach of uniform open channel flow, followed by a change in bottom slope that causes non-uniform flow, ending with another reach of uniform open channel flow. This article is about means of calculating the surface profile (depth vs distance down the channel) for a reach of non uniform flow.
Classifications of Non Uniform Flow in Open Channels
Classifications of Non Uniform Open Channel Flow (Mild or Steep Channel Slope)
The diagram above shows the three possible non uniform flow patterns for a mild slope (channel slope less than the critical slope) and the three for a steep slope (channel slope greater than the critical slope). The three mild slope classifications are M1, M2, and M3. The "M" indicates mild slope and the number shows the relationship among depth of flow, y, critical depth, y_{c}, and normal depth, y_{o} , as shown in the diagram. Similarly the three steep slope classifications are S1, S2, and S3, with the numbers having the same meaning. The diagram shows a typical physical situation that will give rise to each of these six types of non uniform open channel flow.
The Energy Equation for a Backwater Curve Calculations Spreadsheet
The energy equation (the first law of thermodynamics applied to a flowing fluid), which has many applications in fluid mechanics, can be used to calculate surface profiles for non uniform flow in open channels using stepwise calculations. The diagram below shows the parameters that will be used at each end of a reach of channel with non uniform flow.
A Reach of Open Channel with Non Uniform Flow
The energy equation written across a reach of channel is illustrated graphically in the diagram above. The sum of the three items on the upstream end of the channel reach must equal the sum of the three items on the downstream end of the channel reach, giving the equation:
Where the parameters in the equation are as follows:
For specified flow rate, Q, channel bottom slope, S_{o} , Manning roughness coefficient, n, and channel width for a rectangular channel, the energy equation can be used to calculate the length, ΔL, for transition from a known upstream depth, y_{1} , to a selected downstream depth, y_{2} . This process can be repeated as many times as necessary to determine the total distance to a specified downstream depth.
The energy equation can be rearranged to give the following equation for ΔL:
The Manning equation is typically used to calculate the slope of the energy grade line, S_{f} . Although the Manning equation only applies for uniform flow, the use of mean cross-sectional area and mean hydraulic radius with a relatively small step for the calculation gives a good approximation. The equation for S_{f} is as follows:
S_{f} = {Qn/[1.49A_{m}(R_{hm}^{2/3})]]}^{2}, where A_{m }is the mean area and R_{hm}is the mean hydraulic radius between sections 1 and 2. For S.I. units, the 1.49 constant in this equation becomes 1.00.
Surface Profile Calculation Spreadsheet for Non Uniform Flow in Open Channels
Consider a 20 ft wide rectangular channel with bottom slope equal to 0.0003, carrying 1006 cfs. The normal depth for this flow is 10 ft. An M1 backwater curve is generated due to a downstream obstruction. Calculate the channel length for the transition from a depth of 12 ft to a depth of 12.5 ft in this backwater curve.
Solution: The spreadsheet shown in the screenshot below shows the solution. It actually has the entire M1 curve from a depth of 10 ft to a depth of 16 ft. It shows DL for the transition from 12 ft depth to 12.5 ft depth to be 3853 ft.
The Excel spreadsheet template shown above can be used to calculate an M1 surface profile for a rectangular channel with specified flow rate, bottom width, bottom slope, and Manning roughness coefficient. Why bother to make these calculations by hand? This spreadsheet for stepwise surface profile calculation for non uniform flow in open channels and others with similar calculations for a trapezoidal channel, and for any of the six mild or steep nonuniform flow surface profiles are available in either U.S. or S.I. units at a very low cost in our spreadsheet store.
1. Munson, B. R., Young, D. F., & Okiishi, T. H., Fundamentals of Fluid Mechanics, 4^{th} Ed., New York: John Wiley and Sons, Inc, 2002.
2. Chow, V. T., Open Channel Hydraulics, New York: McGraw-Hill, 1959.
3. Bengtson, Harlan H. Open Channel Flow II - Hydraulic Jumps and Supercritical and Nonuniform Flow - An online, continuing education course for PDH credit.
4. Bengtson, Harlan H. "Backwater Curve Calculations Spreadsheet", an online blog article
Introduction
For an Excel spreadsheet to make open channel flow, hydraulic jump calculations, click here to go to our download page. Obtain a convenient, easy to use spreadsheet for rectangular channel hydraulic jump calculations for only $14.95. Read on for information about the use of an Excel spreadsheet for horizontal, rectangular channel hydraulic jump calculations.
Hydraulic Jump Background
In order to discuss hydraulic jumps it's necessary to talk about subcritical and supercritical flow. In general subcritical flow takes place at low velocities and high flow depths, while supercritical flow occurs at high velocities and low flow depths. For more details about critical, subcritical, and supercritical flow, see the article, "Open Channel Flow Spreadsheets - Critical Depth and Critical Slope." The diagram at the right shows supercritical flow on a steep slope, changing to subcritical flow on a mild slope. As shown, the transition from supercritical flow to subcritical flow takes place with a hydraulic jump. Whenever supercritical flow takes place on a slope that isn't steep enough to maintain supercritical flow, the transition to subcritical flow will take place through the mechanism of a hydraulic jump as illustrated in the diagram.
Hydraulic Jump Parameters
Hydraulic jump calculations center on relationships among the supercritical conditions before the jump (upstream or initial conditions) and the subcritical conditions after the jump (downstream or sequent conditions). The diagram at the left shows initial supercritical parameters and sequent subcritical parameters for a hydraulic jump. The parameters and their typical units are summarized below:
An Excel Spreadsheet as a Hydraulic Jump Calculator
The Excel spreadsheet template shown below can be used to carry out hydraulic jump calculations. Why bother to make these calculations by hand? This Excel spreadsheet can calculate the sequent depth, sequent velocity, jump length, head loss across the jump, and hydraulic jump efficiency for specified initial depth, flow rate and channel width. These spreadsheets are available in either U.S. or S.I. units at a very low cost (only $14.95) from our download page. These spreadsheets also have a tab for calculation of flow rate under a sluice gate and all of the equations used in the spreadsheet calculations are shown on the spreadsheets.
Note that some of the equations used in the spreadsheet calculations apply only for rectangular, horizontal channels, so the spreadsheets should be used only for channels that are at least approximately rectangular in cross-section and have a zero or very small slope.
1. Harlan H. Bengtson, "Hydraulic Jumps and Supercritical and Nonuniform Open Channel Flow," an online continuing education course for Professional Engineers.
2. U.S. Department of Transportation, FHWA, Hydraulic Design of Energy Dissipators for Culverts and Channels, Hydraulic Engineering Circular No. 14, 3rd Ed, Chapter 6: Hydraulic Jump.
If you want to obtain an Excel spreadsheet for calculating critical depth and/or critical slope for open channel flow, click here to visit our download page. Read on for information about the use of an Excel spreadsheet for critical depth and critical slope calculations.
The Froude Number and Critical, Subcritical and Supercritical Flow
Any particular example of open channel flow will be critical, subcritical, or supercritical flow. In general, supercritical flow is characterized by high liquid velocity and shallow flow, while subcritical flow is characterized by low liquid velocity and relatively deep flow. Critical flow is the dividing line flow condition between subcritical and supercritical flow.
The Froude number is a dimensionless number for open channel flow that provides information on whether a given flow is subcritical, supercritical or critical flow. The Froude number is defined to be: Fr = V/(gL)^{1/2} , where V is the average velocity, g is the acceleration due to gravity, and L is a characteristic length for the particular type of open channel flow. For flow in a rectangular channel: Fr = V/(gy)^{1/2} , where y is the depth of flow. For flow in an open channel with a shape other than rectangular: Fr = V/[g(A/B)]^{1/2} , where A is the cross-sectional area of flow, and B is the surface width.
The value of the Froude number for a particular open channel flow situation gives the following information:
Calculation of Critical Depth
It is sometimes necessary to know the critical depth for a particular open channel flow situation. This type of calculation can be done using the fact that Fr = 1 for critical flow. It is quite straightforward for flow in a rectangular channel and a bit more difficult, but still manageable for flow in a non-rectangular channel.
For flow in a rectangular channel (using subscript c for critical flow conditions), Fr = 1 becomes: V_{c}/(gy_{c})^{1/2} = 1. Substituting V_{c} = Q/A_{c} = Q/by_{c} and q = Q/b (where b = the width of the rectangular channel), and solving for y_{c} gives the following equation for critical depth: y_{c} = (q^{2}/g)^{1/3}. Thus, the critical depth can be calculated for a specified flow rate and rectangular channel width.
For flow in a trapezoidal channel, Fr = 1 becomes: V_{c}/[g(A/B)_{c}]^{1/2} = 1. Substituting the equation above for V_{c} together with A_{c} = y_{c}(b + zy_{c}) and B_{c} = b + zy_{c}^{2} leads to the following equation, which can be solved by an iterative process to find the critical depth:
Calculation of Critical Slope
After the critical depth, y_{c} , has been determined, the critical slope, S_{c} , can be calculate using the Manning equation if the Manning roughness coefficient, n, is known. The Manning equation can be rearranged as follows for this calculation:
Note that R_{hc} , the critical hydraulic radius, is given by:
R_{hc} = A_{c}/P_{c}, where P_{c} = b + 2y_{c}(1 + z^{2})^{1/2}
Note that calculation of the critical slope is the same for a rectangular channel or a trapezoidal channel, after the critical depth has been determined. The Manning equation is a dimensional equation, in which the following units must be used: Q is in cfs, A_{c} is in ft^{2}, R_{hc} is in ft, and S_{c} and n are dimensionless.
Calculations in S.I. Units
The equations for calculation of critical depth are the same for either U.S. or S.I. units. All of the equations are dimensionally consistent, so it is just necessary to be sure that an internally consistent set of units is used. For calculation of the critical slope, the S.I. version of the Manning equation must be used, giving:
In this equation, the following units must be used: Q is in m^{3}/s, A_{c} is in m^{2}, R_{hc} is in m, and S_{c} and n are dimensionless.
An Excel Spreadsheet as a Critical Depth and Critical Slope Calculator
The Excel spreadsheet template shown below can be used to calculate the critical depth and critical slope for a rectangular channel with specified flow rate, bottom width, and Manning roughness coefficient. Why bother to make these calculations by hand? This Excel spreadsheet and others with similar calculations for a trapezoidal channel are available in either U.S. or S.I. units at a very low cost at our download page.
By Harlan Bengtson
Introduction to Partially Full Pipe Flow Calculations
If you want to obtain an Excel spreadsheet for partially full pipe flow calculations, click here to go to the download page. Read on for information about Excel spreadsheets that can be used as partially full pipe flow calculators.
The Manning equation can be used for flow in a pipe that is partially full, because the flow will be due to gravity rather than pressure. the Manning equation [Q = (1.49/n)A(R^{2/3})(S^{1/2}) for (U.S. units) or Q = (1.0/n)A(R^{2/3})(S^{1/2}) for (S.I. units)] applies if the flow is uniform flow For background on the Manning equation and open channel flow and the conditions for uniform flow, see the article, "Manning Equation/Open Channel Flow Calculations."
Direct use of the Manning equation as a partially full pipe flow calculator, isn't easy, however, because of the rather complicated set of equations for the area of flow and wetted perimeter for partially full pipe flow. There is no simple equation for hydraulic radius as a function of flow depth and pipe diameter. As a result graphs of Q/Q_{full} and V/V_{full} vs y/D, like the one shown at the left are commonly used for partially full pipe flow calculations. The parameters, Q and V in this graph are flow rate an velocity at a flow depth of y in a pipe of diameter D. Q_{full }and V_{full} can be conveniently calculated using the Manning equation, because the hydraulic radius for a circular pipe flowing full is simply D/4.
With the use of Excel formulas in an Excel spreadsheet, however, the rather inconvenient equations for area and wetted perimeter in partially full pipe flow become much easier to work with. The calculations are complicated a bit by the need to consider the Manning roughness coefficient to be variable with depth of flow as discussed in the next section.
Excel Spreadsheet/Partially Full Pipe Flow Calculator for Pipe Less than Half Full
The parameters used in partially full pipe flow calculations with the pipe less than half full are shown in the diagram at the right. K is the circular segment area; S is the circular segment arc length; h is the circular segment height; r is the radius of the pipe; and θ is the central angle.
The equations below are those used, together with the Manning equation and Q = VA, in the partially full pipe flow calculator (Excel spreadsheet) for flow depth less than pipe radius, as shown below.
The equations to calculate n/n_{full,} in terms of y/D for y < D/2 are as follows
The Excel template shown below can be used as a partially full pipe flow calculator to calculate the pipe flow rate, Q, and velocity, V, for specified values of pipe diameter, D, flow depth, y, Manning roughness for full pipe flow, n_{full}; and bottom slope, S, for cases where the depth of flow is less than the pipe radius. This Excel spreadsheet and others for partially full pipe flow calculations are available in either U.S. or S.I. units at a very low cost from the download page.
Excel Spreadsheet/Partially Full Pipe Flow Calculator for Pipe More than Half Full
The parameters used in partially full pipe flow calculations with the pipe more than half full are shown in the diagram at the right. K is the circular segment area; S is the circular segment arc length; h is the circular segment height; r is the radius of the pipe; and θ is the central angle.
The equations below are those used, together with the Manning equation and Q = VA, in the partially full pipe flow calculator (Excel spreadsheet) for flow depth more than pipe radius, as shown below.
The equation used for n/n_{full} for 0.5 < y//D < 1 is: n/n_{full} = 1.25 - [(y/D - 0.5)/2]
An Excel spreadsheet like the one shown above for less than half full flow, and others for partially full pipe flow calculations, are available in either U.S. or S.I. units at a very low cost from the downloads page.
Calculation of Normal Depth for Less Than Half Full Pipes
The diagram, parameters and equations given above in the third section of this article, also apply to normal depth calculation for less than half full, partially full pipe flow. Determination of normal depth requires an iterative (trial and error) calculation, just as with a rectangular channel or a trapezoidal channel.
For normal depth determination with partially full pipe flow, it's necessary to calculate h, A, P, R, and n, for each assumed value of flow depth, y_{o}, so that the parameter, AR^{2/3} can be calculated. Assuming values of y_{o} and calculating values for AR^{2/3}, allows one to zero in on the y_{o} value that comes closest to the target value for AR^{2/3}, as shown in the Excel template example below. The example calculation in this spreadsheet template resulted in y_{o} = 0.35 m, accurate to 2 significant figures.
Note that if the answer comes out to be more than the pipe radius, then the spreadsheet template in the next section should be used, because it uses the slightly different equations that apply for depth greater than the pipe radius. The Excel spreadsheet shown below, and others for partially full pipe flow calculations, are available in either U.S. or S.I. units at a very low cost from the downloads page.
Calculation of Normal Depth for More than Half Full Pipe Flow
The diagram, parameters and equations given in the fourth section of this article, above also apply to normal depth calculation for more than half full, partially full pipe flow. Determination of normal depth requires an iterative (trial and error) calculation, just as with a rectangular or trapezoidal channel.
The table of iterative calculations for pipe flow that is more than half full is much the same as the one shown above, but uses the slightly different equations that apply to flow with a depth greater than the pipe radius.
Note that if the answer comes out to be less than the pipe radius, then the spreadsheet template and equations in the previous section should be used for the calculations.
The four Excel spreadsheets described above for partially full pipe flow calculations are available in either U.S. or S.I. units at a very low cost from the downloads page.
1. Camp, T.R., "Design of Sewers to Facilitate Flow," Sewage Works Journal, 18 (3), 1946
2. Steel, E.W. & McGhee, T.J., Water Supply and Sewerage, 5th ed., New York, McGraw Hill Book Company, 1979
3. ASCE, 1969, Design and Construction of Sanitary and Storm Sewers, NY.
The hydraulic radius is used a lot with Manning equation calculations for uniform open channel flow. Excel spreadsheets can conveniently calculate hydraulic radius for open channel flow through common channel shapes, such as that of a rectangular, triangular, or trapezoidal flume. This article describes the use of parameters such as rectangle, triangle and trapezoid area and perimeter for determining hydraulic radius.
The definition of hydraulic radius for open channel flow is the cross sectional area of flow divided by the wetted perimeter. As an equation: R = A/P, where:
Equations for calculating A, P, and R for common open channel shapes will be covered in the next several sections, followed by information about the use of Excel spreadsheets for the calculations.
Rectangular Channels
The rectangular channel is a common shape for man made channels. Because of the simple calculations for area and perimeter of a rectangle, the calculation of hydraulic radius is quite straightforward. The diagram at the left shows a rectangular channel cross section with the channel width represented by b and the depth of flow represented by y. From the diagram it can be seen that A = by and P - 2y + b. By substituting, the equation for hydraulic radius for open channel flow through a rectangular channel becomes:
R = by/(2y + b)
Trapezoidal Flume
Many man made and natural open channels have a trapezoidal or nearly trapezoidal cross sectional shape. The diagram at the right shows a trapezoidal flume cross section with the parameters typically used to describe its size and shape and to calculate the trapezoid area and wetted perimeter. The parameters shown in the diagram are:
The formula typically used for the area of a trapezoid, when applied to the diagram, gives: A = y(b + B)/2. By using B = b + 2zy, as you can see from the diagram, the trapezoid area becomes: A = (y/2)(b + b + 2zy). This can be simplified to: A = by + zy^{2}. , which gives the area of the trapezoid in terms of y, b, and z, parameters that are often known.
An equation for the wetted perimeter is: P = b + 2λ. The sloped length, λ, is typically unknown, but can be eliminated using the Pythagoras Theorem:
l^{2} = y^{2} + (yz)^{2}, or l = [y^{2} + (yz)^{2}]^{1/2} . Thus the wetted perimeter is:
P = b + 2y(1 + z^{2})^{1/2}, and the hydraulic radius for a trapezoid can be calculated from the equation:
R = (by + zy^{2})/[b + 2y(1 + z^{2})^{1/2}]
Triangular Flumes
The triangular flume is another shape used in open channel flow. The diagram at the left shows a triangular flume and the parameters used for its description. The side slope is the same on both sides of the triangle in the diagram, which is the typical situation. The parameters used for open channel flow calculations with a triangular flume are as follows:
The triangular area is given by A = By/2. You can see from the figure, however, that B = 2yz, so the area can be simplified to: A = y^{2}z.
The wetted perimeter is: P = 2λ , but as with the trapezoidal flume: l^{2} = y^{2} + (yz)^{2}.
This simplifies to the equation: P = 2[y^{2}(1 + z^{2})]^{1/2}
The triangular hydraulic radius can thus be calculated from: R = A/P = y^{2}z/{2[y^{2}(1 + z^{2})]^{1/2}}
Hydraulic Radius Calculation with Excel Spreadsheets
The equations discussed in the last three sections allow you to calculate the hydraulic radius for a rectangular, triangular or trapezoidal flume if the depth of flow and the necessary channel size and shape parameters are known. An Excel spreadsheet like the one shown in the screenshot below, however, can be used as a very convenient hydraulic radius calculator.
For Excel spreadsheet, hydraulic radius calculators, for rectangular, triangular, trapezoidal flumes and partially full pipe flow, go to the DOWNLOAD PAGE. under the "Manning Equation/Open Channel Flow" category.