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 a Spreadsheet for API 570 Piping Inspection Calculations, click here to visit our spreadsheet store. This Excel spreadsheet is intended for making piping inspection calculations from API 570 and API 574.. You can buy a convenient Spreadsheet for API 570 piping Inspection calculations at a very reasonable price. This spreadsheet calculates required minimum pipe wall thickness, long term corrosion rate, short term corrosion rate, remaining life, and maximum allowable working pressure (MAWP). It is available in either U.S. units or S.I. units. Read on for information about using a spreadsheet for API 570 piping inspection calculations.
The required minimum wall thickness for piping being inspected must be the greater of the calculated pressure design wall thickness and the structural minimum wall thickness. The pressure design wall thickness is sometimes calculated with the Barlow formula and sometimes it is calculated with the ASME B31.3 formula. Both are shown below.
In the Barlow formula and the ASME B31.3 formula, P is the design fluid pressure in psig, D is the pipe O.D. in inches, S is the allowable unit stress of the pipe material in psi, E is a longitudinal quality factor, and Y is a coefficient that is a function of the pipe material and the operating temperature.
The structural minimum wall thickness is available in API 574 Table 6 for some pipe materials. The required minimum pipe wall thickness is the greater of the calculated pressure design wall thickness and the structural minimum wall thickness.
The long term corrosion rate and short term corrosion rate are calculated from values for the current measured pipe wall thickness, a previous pipe wall thickness or the initial pipe wall thickness and the time between the two. The remaining life of the pipe is calculated as the difference between the current measured pipe wall thickness minus the required minimum pipe wall thickness divided by the corrosion rate.
The maximum allowable working pressure for a pipe is calculated from the formula:
MAWP = 2SEt/D
In this formula, S, E, and D are as defined above and t is the measured pipe wall thickness in inches.
An example Spreadsheet for API Piping Inspection Calculatons is partially shown in the image below. This Excel spreadsheet can be used to calculate the required minimum pipe wall thickness, corrosion rate, remaining life, and maximum allowable working pressure (MAWP). This Excel spreadsheet, as well as others for pipe flow calculations, is available in either U.S. or S.I. units for a very reasonable price in our spreadsheet store.
References:
1. American Piping Institute, AP! 570, Piping Inspection Code: In-service Inspection, Rating, Repair, and Alteration of Piping Systems, 3rd Ed, Nov, 2009
2. American Piping Institute, API 574, Inspection Practices for Piping System Components, 3rd Ed, Nov, 2009.
3. Bengtson, Harlan H., API 570 Pipeline Inspection Calculations Spreadsheet, an online informational article at www.EngineeringExcelSpreadsheets.com.
To obtain a Redlich Kwong compressibility factor spreadsheet, click here to visit our spreadsheet store. Look in the Fluid Properties category on the Download page. This Excel spreadsheet is intended for calculation of the compressibility factor of a gas using the Redlich-Kwong equation of state. You can buy a convenient Redlich Kwong compressibility factor spreadsheet for a very reasonable price. This spreadsheet calculates the compressibility factor of a gas from the gas temperature, gas pressure, and the critical pressure and critical temperature of the gas. It is available in either U.S. or S.I. units. Read on for information about using a Redlich Kwong compressibility factor spreadsheet.
The compressibility factor of a gas is used in several types of calculations including calculation of real gas density. The Ideal Gas Law, PV = nRT, can be used to calculate the density of a gas that exhibits ideal gas behavior by introducing the molecular weight of the gas and solving for gas density to give: ρ = MW*P/R*T. For information on the use of the ideal gas to calculate gas density, see the article, "Air Density Calculator Excel Spreadsheet."
Conditions required for ideal gas behavior are pressure << critical pressure and/or temperature >> critical temperature. For gases that don't exhibit ideal gas behavior, the compressibility factor, Z can be introduced to give: ρ = Z*MW*P/R*T.
A Redlich Kwong compressibility factor spreadsheet will use of the Redlich-Kwong equation of state, which is shown below:
A, B, and h are constants calculated as shown. The compressibility factor, Z, can be calculated for known values of the gas temperature, T, the gas temperature, P, and the critical temperature and pressure of the gas. The temperatures should be in K and the pressures in atm for use of the equations given above. Calculation of Z requires an iterative calculation because the equations can't be solved explicitly for Z.
An example Redlich Kwong compressibility factor spreadsheet is partially shown in the image below. This Excel spreadsheet can be used to calculate the compressibility factor of a gas, based on user input values for gas temperature, gas pressure, and the critical temperature and pressure of the gas. This Excel spreadsheet, as well as others for fluid property calculations, is available in either U.S. or S.I. units for a very reasonable price in our spreadsheet store.
1. Otto Redlich, and J.N.S. Kwong, "On the Thermodynamics of Solutions. V. An Equation of State. Fugacities of Gaseous Solutions", Chemical Reviews 44 pp. 233-244 (1949).
2. Bengtson, Harlan H., Gas Compressibility Factor Calculator Excel Spreadsheet, an online blog article.
To obtain a spreadsheet for pressure drop in non circular ducts, click here to visit our spreadsheet store. Intended for use in calculating flow rate or frictional head loss and frictional pressure drop, you can buy a convenient spreadsheet for pressure drop in non circular ducts calculations for a very reasonable price. This spreadsheet makes calculations with the Hagen Poiseuille equation for laminar flow and with the Moody friction factor and the Darcy Weisbach equation for turbulent flow for flow through an annulus and for flow through a rectangular duct. Turbulent flow calculations can be made for a general non-circular duct with known cross-sectional area and wetted perimeter. It is available in either U.S. or S.I. units. Read on for information about a spreadsheet for pressure drop in a non circular duct calculations.
The Moody friction factor is used in the Darcy Weisbach equation for turbulent flow pressure drop through non circular ducts. The Darcy Weisbach equation is:
Where the hydraulic diameter is used for D in the Darcy Weisbach equation for flow through non circular ducts. For further discussion of the Moody friction factor and its use in the Darcy Weisbach equation for pipe flow calculations, see the post, "Pipe Flow-Friction Factor Calculations with Excel Spreadsheets."
For laminar flow in an annulus or laminar flow in a rectangular duct, the Hagen Poiseuille equation should be used. For flow through an annulus, it is:
A spreadsheet for flow through non circular ducts calculations is partially shown in the image below. This Excel spreadsheet can be used to calculate the frictional pressure drop and head loss or flow rate for flow through an annulus (laminar or turbulent flow), flow through a rectangular duct (laminar or turbulent flow) or flow through a general non circular duct (turbulent flow only). This Excel spreadsheet, as well as others for pipe flow calculations, is available in either U.S. or S.I. units for a very reasonable price in our spreadsheet store.
Reference:
Bengtson, Harlan, "Spreadsheet for Flow Through Non Circular Ducts", An online Blog article.
To obtain a natural gas pipeline flow calculation spreadsheet , click here to visit our spreadsheet store. Intended for use in making natural gas pipeline design calculations, you can buy a convenient natural gas pipeline flow calculation spreadsheet for a very reasonable price. This spreadsheet makes calculations with the Weymouth equation, the Panhandle A equation, and the Panhandle B equation, and is available in either U.S. or S.I. units. Read on for information about a natural gas pipeline flow calculation spreadsheet.
Several different equations are used for natural gas pipeline flow calculations. If the pressure drop across the pipe is less than 40 % of the average pipeline pressure, then the Darcy Weisbach equation is a possibility. For longer pipelines with larger pressure drop, the three equations that are in most common use are the Weymouth equation, the Panhandle A equation, and the Panhandle B equation. The choice among these three equations depends upon the pipeline diameter, the pipe length, and the average pipeline pressure.
The Weymouth equation was the first of the three to be developed and was the first equation for natural gas pipeline flow calculations that didn't require an iterative calculation to get a value for the friction factor. The Weymouth equation is:
Where:
A spreadsheet for Natural Gas Pipeline Flow Calculations is partially shown in the image below. It can be used to calculate the natural gas pipeline flow rate with the Weymouth equation, the Panhandle A equation and the Panhandle B equation. This Excel spreadsheet, as well as others for pipe flow calculations, is available in either U.S. or S.I. units for a very reasonable price in our spreadsheet store.
1. Crane Co., (1988), "Flow of Fluids through Valves, Fittings and Pipes, Technical Paper 401.
2. GPSA (Gas Processors Suppliers Association), (1988), Engineering Data Book, 11th Ed.
3. Bengtson, Harlan H., (2016), "Natural Gas Pipeline Flow Calculations," available as a paperback book or as an Amazon Kindle ebook.
4. Bengtson, Harlan H., (2017), "Pipe Flow/Friction Factor Calculations with Spreadsheets", available as a paperback book or as an Amazon kindle e-book.
5. Bengtson, Harlan H. (2014) "Natural Gas Pipeline Flow Calculation Spreadsheet," A blog article at www.EngineeringExcelSpreadsheets.com.
To obtain Excel spreadsheets for Manning Equation partially filled circular pipes calculations, click here to visit our spreadsheet store or click here to go to a download page for Manning equation partially filled Circular pipes calculation spreadsheets. 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 with Excel Spreadsheets."
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.
Using the geometric/trigonometric equations discussed in the next couple of sections, it is relatively easy to calculate the cross-sectional area, wetted perimeter, and hydraulic radius for partially full pipe flow with any specified pipe diameter and depth of flow. If the pipe slope and Manning roughness coefficient are known, then it should be easy to calculate flow rate and velocity for the given depth of flow using the Manning Equation [Q = (1.49/n)A(R^{2/3})(S^{1/2})], right? No, wrong! As long ago as the middle of the twentieth century, it had been observed that measured flow rates in partially full pipe flow aren't the same as those calculated as just described. In a 1946 journal article (ref #1 below), T. R. Camp presented a method for improving the agreement between measured and calculated values for partially full pipe flow. The method developed by Camp consisted of using a variation in Manning roughness coefficient with depth of flow as shown in the graph above.
Although this variation in Manning roughness due to depth of flow doesn't make sense intuitively, it does work. It is well to keep in mind that the Manning equation is an empirical equation, derived by correlating experimental results, rather than being theoretically derived. The Manning equation was developed for flow in open channels with rectangular, trapezoidal, and similar cross-sections. It works very well for those applications using a constant value for the Manning roughness coefficient, n. Better agreement with experimental measurements is obtained for partially full pipe flow, however, by using the variation in Manning roughness coefficient developed by Camp and shown in the diagram above.
The graph developed by Camp and shown above appears in several publications of the American Society of Civil Engineers, the Water Pollution Control Federation, and the Water Environment Federation from 1969 through 1992, as well as in many environmental engineering textbooks (see reference list at the end of this article). You should beware, however that there are several online calculators and websites with equations for making partially full pipe flow calculations using the Manning equation with constant Manning roughness coefficient, n. The equations and Excel spreadsheets presented and discussed in this article use the variation in n that was developed by T.R. Camp.
The parameters used in Manning equation partially filled circular pipes 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 in our spreadsheet store.
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 at www.engineeringexceltemplates.com or in our spreadsheet store.
References
1. Bengtson, Harlan H., Uniform Open Channel Flow and The Manning Equation, an online, continuing education course for PDH credit.
2. Camp, T.R., "Design of Sewers to Facilitate Flow," Sewage Works Journal, 18 (3), 1946
3. Chow, V. T., Open Channel Hydraulics, New York: McGraw-Hill, 1959.
4. Steel, E.W. & McGhee, T.J., Water Supply and Sewerage, 5th Ed., New York, McGraw-Hill Book Company, 1979
5. ASCE, 1969. Design and Construction of Sanitary and Storm Sewers, NY
6. Bengtson, H. H., "Partially Full Pipe Flow Calculations with Excel Spreadsheets," an online blog article
Scroll down for the following articles in this category:
Where to Find a Compressible Air Flow Rate Pressure Drop Calculator Spreadsheet
To obtain a compressible air flow rate pressure drop calculator spreadsheet, click here to visit our spreadsheet store. Why use online calculators or hand calculations when you can buy a compressible air flow rate pressure drop calculator spreadsheet for a very reasonable price. Read on for information about compressible air flow rate pressure drop calculator spreadsheets for compressible flow of air in a pipe.
Air Pipe Flow Conditions for a Compressible Air Flow Rate Pressure Drop Calculator Spreadsheet
For flow of air in a pipe with the frictional pressure drop less than 20% of the inlet air pressure, satisfactory results can be obtained using incompressible flow calculations with the Darcy-Weisbach equation. For background information on that type of calculation, see the article, "Pipe Flow-Friction Factor Calculations with Excel Spreadsheets." If the frictional pressure drop is more than 20% of the incoming air pressure, then Fanno Flow equations should be used, as discussed here using a compressible air flow rate pressure drop calculator spreadsheet.
Equations for Air Pipe Flow Calculations with a Compressible Air Flow Rate Pressure Drop Calculator Spreadsheet
Shown below are the primary equations used in the compressible air flow rate pressure drop calculator spreadsheet being discussed here for compressible flow of air in a pipe. These equations are for compressible pipe flow with negligible heat transfer with the surroundings, but including effects of friction in the flow.
The parameters in these fanno flow equations are as follows:
Parameters with an asterisk refer to conditions at the "choke point" in the pipe, where the Mach number is 1.
The screenshot below shows part of a compressible air flow rate pressure drop calculator spreadsheet. These calculations are for air flow in a pipe, in S.I. units. This Excel spreadsheet and others for compressible pipe flow calculations in a fanno flow excel spreadsheet are available in either U.S. or S.I. units at a very reasonable cost in our spreadsheet store.
1. Bengtson, Harlan H., "Fanno Flow Excel Spreadsheet for Air Flow in a Pipe," an online blog article
For Excel spreadsheets to make culvert design calculations in either U.S. or S.I. units, click here to visit our spreadsheet store. Obtain convenient, easy to use spreadsheets for culvert design calculations at reasonable prices. Read on for information about the use of Excel spreadsheets for this application.
One of the general conditions for culvert design calculations is inlet control, in which the flow rate through the culvert is controlled at the inlet end of the culvert by the culvert diameter and other inlet conditions. The other general condition is outlet control, in which the flow rate is controlled by the outlet conditions and the entire length of the culvert.
An equation that relates culvert parameters for inlet control conditions is:
where:
An equation that relates culvert parameters for outlet control conditions is:
Spreadsheets for Circular Culvert Design Calculations
The Excel spreadsheet screenshot below shows part of a spreadsheet for circular culvert design calculations based on inlet control. Based on the indicated input values, the spreadsheet will calculate the minimum required culvert diameter and the headwater depth for the next larger standard culvert diameter.
For low cost, easy to use spreadsheets to make these calculations in S.I. or U.S. units, click here to visit our spreadsheet store.
1. Hydraulic Design of Highway Culverts,Third Edition, Publication No. FHWA-HIF-12-026, U.S. DOT/Federal Highway Administration, April, 2012.
2. Bengtson, Harlan H., "Pipe Culvert Design Spreadsheet Calculations", an online article
Introduction
If you want to obtain an Excel spreadsheet to use for pipe flow/friction factor calculation, visit our download page. Read on for information about the Darcy Weisbach equation and its use in an Excel spreadsheet as a friction factor/pipe flow calculator.
The Darcy Weisbach equation is widely used for pipe flow calculations. This equation is applicable to pressure flow in pipes, rather than gravity flow, which is handled by open channel flow equations like the Manning equation. The Darcy Weisbach equation can be used for fully developed, turbulent flow of a gas or liquid. It provides the relationship among several parameters of interest: pipe diameter and length, frictional head loss or pressure drop, and pipe flow rate. Any one of these can be calculated if the others are known along with the density and viscosityof the fluid.
The Darcy Weisbach Equation and a Friction Factor Calculator
The Darcy Weisbach equation is h_{L} = f*(L/D)(V^{2}/2g), where h_{L} is the frictional head loss for flow of a fluid at average velocity, V, through a pipe of length, L, and diameter, D. The Reynolds number for the flow and the relative roughness of the pipe (e/D) are needed to get a value for the friction factor, f. The Moody Diagram at the left shows the general relationship among the friction factor, f, the Reynolds number, Re, and the relative pipe roughness, e/D.
For calculations with an Excel spreadsheet, equations for f as a function of Re and e/D are more convenient than using a graph like the Moody diagram. A set of equations for friction factor as a function of Re and e/D for four different flow regimes is shown in the boc at the right. The four different flow regimes appear as different portions of the Moody diagram as follows:
The table at the right shows pipe roughness values for some common pipe materials, for use in calculating the pipe roughness ratio, e/D.
For a low cost Moody friction factor calculator download, that will calculate f for Reynolds number above 2100, visit our download page.
Frictional Head Loss and Frictional Pressure Drop Calculations
After a value of the friction factor, f, is obtained with the Moody friction factor calculator, it is quite straightforward to calculate frictional head loss and pressure drop, if the pipe length & diameter and average flow velocity are known. One simply needs to substitute values for L, D, V and f into the Darcy Weisbach equation [ h_{L} = f(L/D)(V^{2}/2g) ]. The Darcy Weisbach equation is a dimensionally consistent equation, so any consistent set of units can be used. For U.S. units, h_{L}, L, and D are typically in ft, V is in ft/sec, and g is 32.2 ft/sec^{2} . For S.I. units, are typically in m, V is in m/s, and g is 9.81 m/s^{2} . If volumetric flow rate, Q, is known rather than average velocity, V, then V can be calculated from
Frictional pressure drop can be calculated from frictional head loss, using the equation:
The Excel spreadsheet screenshot below shows a spreadsheet available as part of the "Pipe Flow-Friction Factor Calculations Package," from our download page in either U.S. units or S.I. units. This spreadsheet package has three worksheets: one to calculate frictional head loss and pressure drop for known pipe diameter, length, & material and flow rate; one to calculate flow rate for known head loss or pressure drop, and pipe diameter, length & material; and one to calculate pipe diameter for known head loss or pressure drop, flow rate, and pipe length & material.
1. Munson, B. R., Young, D. F., & Okiishi, T. H., Fundamentals of Fluid Mechanics, 4th Ed., New York: John Wiley and Sons, Inc, 2002.
2. Darcy Weisbach equation history – http://biosystems.okstate.edu/darcy/DarcyWeisbach/Darcy-WeisbachHistory.htm
3. Source for pipe roughness values – http://www.efunda.com/formulae/fluids/roughness.cfm
4. Bengtson, H.H., Pipe Flow/Friction Factor Calculations with Excel, an online continuing education course for Professional Engineers
5. Bengtson, Harlan, "Advantages of Spreadsheets for Pipe Flow/Friction Factor Calculations", Available as an Amazon Kindle e-book and as a paperback.