Sharp Crested Rectangular Weir Flow Calculator Spreadsheet

Where to Find a Rectangular Weir Flow Calculator Spreadsheet

For a rectangular weir flow calculator spreadsheet, click here to visit our spreadsheet store. Obtain a convenient, easy to use spreadsheet to use as a rectangular weir flow calculator at a reasonable price.   Read on for information about Excel spreadsheets that can be used as contracted rectangular weir open channel flow calculators.

The following section, which gives background on sharp crested rectangular weirs in general, also appears in the companion article, “Suppressed Rectangular Weir Calculations with an Excel Spreadsheet

Background on Sharp Crested Rectangular Weirs in General

The picture at the left shows a rectangular weir measuring open channel flow rate in a natural channel.  The diagram below right shows a longitudinal cross-section of a sharp crested weir, with some of the terminology and parameters often used for sharp crested weirs included on the diagram.

The weir crest is the top of the weir. For a rectangular weir it is the straight, levelsharp crested weir parameters for rectangular weir flow calculatorbottom of the rectangular opening through which water flows over the weir. The term nappe is used for the sheet of water flowing over the weir. The equations for calculating flow rate over a weir in this article require free flow, which takes place when there is air under the nappe. The drawdown is shown in the diagram as the decrease in water level going over the weir due to the acceleration of the water.  The head over the weir is shown as H in the diagram; the height of the weir crest is shown as P; and the open channel flow rate in the open channel (and over the weir) is shown as Q.

Image Credits:  Rectangular, Sharp-Crested Weir: flowmeterdirectory.co.uk

Sharp Crested Weir Parameters:  H. H. Bengtson, Ref #2

The Francis Equation for a Rectangular Weir Flow Calculator

contracted rectangular weir diagram for rectangular weir flow calculatorA contracted rectangular weir is one for which the weir extends across only part of the channel, so that the length of the weir, L, is different from as the width of the channel.  The picture at the left shows a contracteded rectangular weir being used to measure the flow of water in a triangular open channel.  The diagram below right shows some of the key parameters used in contracted rectangular weir flow rate calculations. contracted rectangular weir diagram - rectangular weir flow calculator Specifically, the height of the weir crest, P, the head over the weir, H, the weir length, L, and the channel width, B, are shown on the diagram of a contracted rectangular weir in a rectangular channel.  The U.S. Bureau of Reclamation, in their Water Measurement Manual (Ref #1 below), recommend the use of the Francis equation (shown below) for completely contracted rectangular weirs, subject to the condition that  H/L < 0.33,  B – L > 4 Hmax,  and > 2Hmax.

For U.S. units:  Q = 3.33(L – 0.2H)H3/2,  where

  • Q is the water flow rate in ft3/sec,
  • L is the length of the weir in ft,
  • H is the head over the weir in ft,
  • B is the width of the channel in ft, and
  • Hmax is the maximum expected head over the weir in ft.

For S.I. units:  Q = 1.84(L – 0.2H)H3/2, where

  • Q is the water flow rate in m3/sec,
  • L is the length of the weir in m, and
  • H is the head over the weir in m.
  • B is the width of the channel in m, and
  • Hmax is the maximum expected head over the weir in m.

Image Credits:  Contracted Rectangular Weir picture: Food and Agricultural Organization of the United Nations.

Contracted Rectangular Weir Diagram – Bengtson, Harlan H.

The Kindsvater-Carter Formula for a Rectangular Weir Flow Calculator

If any of the three required conditions given in the previous section are not met, then the more general Kindsvater- Carter Equation, shown below should be used.

U.S. units: Q  =  Ce(2/3)[(2g)1/2](L + kb)(H + 0.003)3/2

S.I. units: Q  =  Ce(2/3)[(2g)1/2](L + kb)(H + 0.001)3/2

Ce is a function of L/B and H/P, while  kb is a function of L/B.  There are graphs, tables and equations available for obtaining values for Ce and kb for specified values of L/B and H/P. The equations given below were prepared from information in Reference #3 at the end of the article.

Ce is dimensionless, so the equation for Ce is as a function of L/B and H/P is the same for both S.I. and U.S. units and is as follows:

Ce = α(H/P) + β, where  β = 0.58382 + 0.016218(L/B), and

α = [-0.0015931 + 0.010283(L/B)]/[1 – 1.76542(L/B) + 0.870017(L/B)2]

The equation for kb as a function of L/B has different constants for S.I. and U.S. units.  The two versions of the equation for kb are as follows:

U.S. units: for 0 < L/B < 0.35:   kb = 0.007539 + 0.001575(L/B)  – (kb is in ft)

for 0.35 < L/B < 1.0:  kb = -0.34806(L/B)4 + 0.63057(L/B)3 – 0.37457(L/B)2 + 0.09246(L/B) – 0.000197 (kb is in ft)

S.I. units: for 0 < L/B < 0.35:   kb = 0.002298 + 0.00048(L/B) (kb is in m)

for 0.35 < L/B < 1.0:  kb = -0.10609(L/B)4 + 0.1922(L/B)3 – 0.11417(L/B)2 + 0.028182(L/B) – 0.00006 – (kb is in m)

Note that if H/L < 0.33,  B – L > 4 Hmax,  and P > 2Hmax, then the Francis Equation and the Kindsvater-Carter Equation will give nearly the same value for Q.  As conditions diverge more and more from the requirements, the calculations from the two equations will diverge more and more.  In these cases the value calculated by the Kindsvater-Carter formula should be used.

An Excel Spreadsheet as a Contracted Rectangular Weir Flow Calculator

The Excel spreadsheet template shown below can be used as a contracted rectangular weir flow calculator, using both the Francis equation and the Kindsvater-Carter equation.  Only four input values are needed.  They are the height of the weir crest above the channel invert, P; the width of the channel, B; the weir length L; and the measured head over the weir, H. With these four input values, the Excel formulas will calculate the parameters needed and check on whether the conditions required for use of the Francis equation are met. If the conditions are all met, then the value of Q calculated with the Francis equation can be used.  If any of the conditions aren’t met, then the value of Q calculated with the Kindsvater-Carter formula should be chosen.  This Excel spreadsheet and others for suppressed and contracted rectangular weir calculations are available in either U.S. or S.I. units at a very low cost in our spreadsheet store.

Rectangular weir flow calculator spreadsheet screenshot

References

1. U.S. Dept. of the Interior, Bureau of Reclamation, 2001 revised, 1997, 3rd ed,  Water Measurement Manual

2. Bengtson, H.H., Sharp Crested Weirs for Open Channel Flow Measurement, an Amazon Kindle ebook.

3. Bengtson, H.H., Open Channel Flow Measurement – Weirs and Flumes, An online continuing education course for PDH credit for Professional Engineers

4. Bengtson, H. H., Sharp-Crested Weirs for Open Channel Flow Measurement, An online continuing education course for PDH credit for Professional Engineers.

5. Merkley, Gary P., Weirs for Flow Measurement Open Course Ware, Utah State University.

 

 

ISO 5167 Orifice Coefficient Calculation Spreadsheet

Introduction to an ISO 5167 Orifice Coefficient Calculation Spreadsheet

For an ISO 5167 orifice coefficient calculation spreadsheetclick here to visit our spreadsheet store.   Read on for information about Excel spreadsheets that can be used to make these ISO 5167 orifice meter calculations.

When ISO 5167 came out in 1991, it included three standard configurations for the pressure taps in an orifice flow meter and equations to calculate the orifice discharge coefficient for a specified ratio of orifice diameter to pipe diameter for any of those three standard pressure tap configurations.  This provided greater flexibility for orifice meters, because orifice plates with different orifice diameters could be used in a given orifice meter, while still allowing accurate determination of the orifice discharge coefficient.

Background on ISO 5167 Orifice Coefficient Calculation Spreadsheet

ISO 5167 Orifice Coefficient Calculation Spreadsheet DiagramAn orifice meter is a simple device for measuring pipe flow rate through the use of a circular plate with a hole in the center (the orifice plate), held in place between pipe flanges, as shown in the diagram at the left.  The fluid pressure decreases downstream of the orifice plate due to the accelerated flow.  The pressure difference shown in the diagram as P1 – P2 can be measured and used to calculate the flow rate passing through the meter (and thus the pipe flow rate) using the equation shown at the right.  This equation allows calculation of pipe flow rate, Q, for measured pressure difference, P1 – P2, and known density of the fluid, ρ, the ratio of orifice diameter to pipe diameter, β, the cross-sectional area of the orifice, Ao, and the orifice discharge coefficient, Cd.

For more details about the orifice, flow nozzle, and venturi meter, see the article, “Excel Spreadsheets for Orifice and Venturi Flow Meter Calculations.”

ISO 5167 Standard Pressure Tap Locations

Prior to ISO 5167 coming out in 1991, the downstream pressure tap of an orifice meter was typically located at the vena contracta (the minimum jet area downstream of the orifice plate) as shown in the diagram above.  The correlations in place for determining the orifice discharge coefficient were for the downstream pressure tap at the vena contracta.  Unfortunately, the distance of the vena contracta fro the orifice plate changes with orifice diameter, so changing to an orifice plate with a different hole diameter required moving the ISO 5167 Orifice Coefficient Calculation Spreadsheet - pressure tapsdownstream pressure tap in order to be able to accurately estimate the orifice discharge coefficient.

The three standard pressure tap configurations identified for orifice flow meters, known as corner taps, flange taps, and D – D/2 taps, are shown in the diagram at the left.  As shown in the diagram, the distance of the pressure taps from the orifice plate is given as a fixed distance, or as a function of the pipe diameter, independent of the orifice diameter, so the orifice discharge coefficient can be calculated for several orifice diameters in a given orifice meter.

Equations for ISO 5167 Orifice Coefficient Calculation Spreadsheet

Included in ISO 5167 is an equation allowing calculation of the orifice discharge coefficient, Cd, for known values of β (d/D), Reynolds number, Re, and L1 & L2, where L1 is the distance of the upstream pressure tap from the orifice plate and L2 is the distance of the downstream pressure tap from the orifice plate.  For corner taps:  L1 = L2 = 0;  for flange taps:  L1 = L2 = 1″ ;  and for D-D/2 taps:  L1 = D & L2 = D/2.   The ISO 5167 equation for the orifice discharge coefficient is:

Cd – 0.5959 + 0.0312 β2.1 – 0.1840 β8 + 0.0029 β2.5(106/Re)0.75 + 0.0900(L1/D)[β4/(1 – β4)] – 0.0337(L2/D)β3

This equation is usable  to find the orifice discharge coefficient for an orifice flow meter with any of the three standard pressure tap configurations, but not for any other arbitrary values of L1 and L2. The introduction of these standard pressure tap configurations and the equation for Cd, allows a given orifice flow meter to conveniently use different size orifice openings and cover a wide flow measurement range.

An iterative (trial and error) calculation is needed to get a value for Cd, because the upstream velocity needed for Re isn’t known until Cd is known.  The ISO 5167 orifice coefficient calculation spreadsheet template shown in the screenshot at the right will calculate the orifice discharge coefficient based on the indicated input information.  The spreadsheet uses an iterative calculation procedure.  It is necessary to assume a value for Re to start the process and replace that value with the calculated Re as any times as necessary until the two Re values are the same.  This ISO 5167 orifice calculation spreadsheet is available in either U.S. or S.I. units at a very low cost in our spreadsheet store.

References:

1. U.S. Dept. of the Interior, Bureau of Reclamation, 2001 revised, 1997 third edition, Water Measurement Manual.

2. International Organization of Standards – ISO 5167-1:2003 Measurement of fluid flow by means of pressure differential devices, Part 1: Orifice plates, nozzles, and Venturi tubes inserted in circular cross-section conduits running full. Reference number: ISO 5167-1:2003.

3. Bengtson, Harlan H., Flow Measurement in Pipes and Ducts, An online continuing education course.

4. Bengtson, Harlan H., “Orifice and Venturi Pipe Flow Meters: for Liquid Flow or Gas Flow,”  an Amazon Kindle e-book.


V Notch Weir Calculator Excel Spreadsheet

Where to Find a V Notch Weir Calculator Excel Spreadsheet

To obtain a V notch weir calculator Excel spreadsheet, click here to visit our spreadsheet store. Why use online calculators or hand calculations when you can buy a V-notch weir calculator excel spreadsheet for only $11.95.  Read on for information about Excel spreadsheets that can be used as v-notch weir open channel flow calculators.

As you can see in the diagrams and picture below, the name, v notch weir, is a good description of the device, simply a v shaped notch in a plate placed in an open channel so that the water is forced to flow through the v notch.  It can be used to measure the open channel flow rate, because the height of water above the point of the v notch can be correlated with flow rate over the weir.  The v-notch weir works well for measuring low flow rates, because the flow area decreases rapidly as the head over the v notch gets small.

Background for Sharp Crested Weirs

Picture for V notch weir calculator excel spreadsheetThe v notch weir is only one of several possible types of sharp crested weirs.  The image at the left shows a picture of a v-notch weir. Acknowledgement of Image Source:              RS Hydro www.rshydro.co.uk                            The diagram below right shows a longitudinal cross-section of a sharp crested weir with several commonly used parameters identified on the diagram.  The weir crest is the term used for the top of the weir.  In the case of a v notch weir, the crest is the point of the v-shaped notch.  The term nappe refers to the sheet of water flowing over the weir.  The equations to bediagram for v notch weir calculator excel spreadsheet  discussed in this article for calculating flow over a v-notch weir require free flow over the weir.  This means that there must be air under the nappe, as shown in the diagram.  The drawdown is the decrease in water level going over the weir caused by the acceleration of the water.  The measurement, H, shown in the diagram is referred to as the head over the weir.  P in the diagram is the height of the weir crest, and the open channel flow rate (also the flow rate over the weir) is shown as Q.

Picture Credit:  U.S. Forest Service

A V Notch Weir Calculator Excel Spreadsheet for a 90 Degree Notch Angle

v notch weir calculator excel spreadsheet diagram

The equation shown below is recommended by the U.S. Dept. of the Interior, Bureau of Reclamation in their Water Measurement Manual (ref #1 below) for calculations with a fully contracted, 90o, v notch, sharp crested weir with free flow conditions and 0.2 ft < H < 1.25 ft.

In U. S. units:  Q = 2.49H2.48, where Q is discharge in cfs and H is head over the weir in ft.

In S.I. units:  Q = 1.36H2.48, where Q is discharge in  m3/s and H is head over the weir in m.

The conditions for the v notch weir to be fully contracted are:

H/P < 0.4,    H/B < 0.2,    P > 1.5 ft (0.45 m),   B > 3 ft (0.9 m)

The diagram above shows the parameters H, P, θ and B for a v notch weir as used for open channel flow rate measurement in a v notch weir calculator excel spreadsheet.

Screenshot of a V Notch Weir Calculator Excel Spreadsheet

The screenshot below shows a v notch weir calculator excel spreadsheet for making 90o, v-notch weir calculations in U.S. units.  Based on specified values for H, P, & S, along with Hmax, the maximum expected head over the weir, the spreadsheet checks on whether the required conditions for fully contracted flow are met and then calculates the flow rate, Q.  This Excel spreadsheet and others for v notch weir calculations are available in either U.S. or S.I. units at a very low cost (only $11.95)  in our spreadsheet store.

References:

1. U.S. Dept. of the Interior, Bureau of Reclamation, 2001 revised, 1997 third edition, Water Measurement Manual, available for online use or download at: http://www.usbr.gov/pmts/hydraulics_lab/pubs/wmm/index.htm.

2. Bengtson, Harlan H., Open Channel Flow III – Sharp Crested Weirs, an online continuing education course for PDH credit, http://www.online-pdh.com/engcourses/course/view.php?id=87

3. Munson, B. R., Young, D. F., & Okiishi, T. H., Fundamentals of Fluid Mechanics, 4th Ed., New York: John Wiley and Sons, Inc, 2002.



Partially Full Pipe Flow Calculator with Excel Spreadsheets

Where to Find Partially Full Pipe Flow Calculator Spreadsheets

To obtain Excel spreadsheets for partially full pipe flow calculationsclick here to visit our spreadsheet store  for partially full pipe flow calculator spreadsheets. Read on for information about Excel spreadsheets that can be used as a partially full pipe flow calculator.

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(R2/3)(S1/2) for (U.S. units) or Q = (1.0/n)A(R2/3)(S1/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.

Graph for use with a partially full pipe flow calculatorDirect 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/Qfull and V/Vfull 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.  Qfull and Vfull 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.

Is the Manning Roughness Coefficient Variable for Partially Full Pipe Flow Calculations?

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(R2/3)(S1/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.

Excel Spreadsheet/Partially Full Pipe Flow Calculator for Pipe Less than Half Full

Diagram to for Partially Full Pipe Flow CalculatorThe 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.

  • h = y
  • θ = 2 arccos[ (r – h)/r ]
  • A = K = r2(θ – sinθ)/2
  • P = S = rθ

The equations to calculate n/nfull, in terms of y/D for y < D/2 are as follows

  • n/nfull = 1 + (y/D)(1/3) for 0 < y/D < 0.03
  • n/nfull = 1.1 + (y/D – 0.03)(12/7) for 0.03 < y/D < 0.1
  • n/nfull = 1.22 + (y/D – 0.1)(0.6) for 0.1 < y/D < 0.2
  • n/nfull = 1.29 for 0.2 < y/D < 0.3
  • n/nfull = 1.29 – (y/D – 0.3)(0.2) for 0.3 < y/D < 0.5

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, nfull; 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.

screenshot of partially full pipe flow calculator spreadsheet

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.

  • h = 2r – y
  • θ = 2 arccos[ (r – h)/r ]
  • A = πr2 – K = πr2 – r2(θ – sinθ)/2
  • P = 2πr – S = 2πr – rθ

The equation used for n/nfull for 0.5 < y//D < 1 is: n/nfull = 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.

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., “Manning Equation Partially Filled Circular Pipes,”  An online blog article

7. Bengtson, H.H., “Partially Full Pipe Flow Calculations with Spreadsheets“, available as an Amazon Kindle e-book and as a paperback.

 


 

 

 


Activated Sludge Aeration Tank Design with Excel Spreadsheets

Where to Find Spreadsheets for Activated  Sludge Aeration Tank Design

For Excel spreadsheets to use for activated sludge aeration tank design or operation calculations, click here to visit our spreadsheet store.  Get an easy to use spreadsheet to make a variety of activated sludge aeration tank design calculations for a very reasonable price. Read on for information about activated sludge aeration tank design and operation calculations.

The activated sludge process is widely used for biological wastewater treatment all over the world.  This method of biological wastewater treatment was invented in England in about 1914.  An activated sludge wastewater treatment system always has an aeration tank and secondary clarifier, which function as a unit to remove biodegradable organic matter (biochemical oxygen demand or BOD) and suspended solids from the wastewater and keep the aerobic microorganisms that carry out the treatment in the system.  Most types of activated sludge processes also have a primary clarifier that removes settleable solids from the incoming wastewater before it goes to the aeration tank.  A flow diagram for a typical activated sludge wastewater treatment plant is shown in the diagram below.  The diagram shows the typical flow pattern through the primary clarifier, aeration tank and secondary clarifier mentioned above, plus initial preliminary treatment (screening, flow measurement and grit removal) and disinfection, which is usually the final step in wastewater treatment.

diagram for activated sludge aeration tank design

This article will emphasize the equations, parameters, and calculations for design and operation of the aeration tank/secondary clarifier of an activated sludge plant.

Parameters for Activated Sludge Aeration Tank Design Calculations

Activated Sludge Aeration Tank Design Flow DiagramThe diagram at the left shows a typical flow pattern for a conventional activated sludge aeration tank and secondary clarifier.  Some of the parameters that are typically used in activated sludge aeration tank design calculations are shown for the primary effluent, secondary effluent, waste activated sludge, and recycle activated sludge. The following list shows those parameters, along with some others that are used for activated sludge calculations, with their U.S. and S.I. units.

  • primary effluent flow rate, Qo,  (MGD – U.S. or m3/day – S.I.)
  • primary effluent biochemical oxygen demand (BOD) concentration, So, (mg/L – U.S. or g/m3 – S.I.)
  • primary effluent suspended solids conc., Xo, (mg/L – U.S. or g/m3 – S.I.)
  • aeration tank volume, V, (ft3 – U.S. or m3 – S.I.)
  • aeration tank MLSS (suspended solids conc.), X, (mg/L – U.S. or g/m3 – S.I.)
  • secondary effluent flow rate, Qe,  (MGD – U.S. or m3/day – S.I.)
  • secondary effluent susp.solids conc., Xe, (mg/L – U.S. or g/m3 – S.I.)
  • secondary effluent biochemical oxygen demand (BOD) concentration, Se, (mg/L – U.S. or g/m3 – S.I.)
  • waste activated sludge flow rate, Qw, (MGD – U.S. or m3/day – S.I.)
  • waste activated sludge biochemical oxygen demand (BOD) conc., Sw, (mg/L – U.S. or g/m3 – S.I.)
  • waste activated sludge susp. solids conc., Xw (mg/L – U.S. or g/m3 – S.I.)
  • recycle activated sludge flow rate, Qr, (MGD – U.S. or m3/day – S.I.)
  • Food to Microorganism ratio, F:M, (lb BOD/day/lb MLVSS – U.S. or kg BOD/day/kg MLVSS – S.I.)
  • Hydraulic retention time, HRT, (hours for U.S or S.I.)
  • Sludge retention time (also called sludge age), SRT, (days for U.S. or S.I.)
  • Volumetric loading, VL, (lb BOD/day/1000 ft3 – U.S. or kg BOD/day/m3 – S.I.)
  • % volatile solids in the aeration tank mixed liquor suspended solids, %Vol.

Activated Sludge Aeration Tank Design Calculations with Excel Spreadsheets

The table below shows the typical range of values for three commonly used activated sludge aeration tank design parameters: volumetric loading, hydraulic retention time, and food to microorganism ratio. The table includes design information for three activated sludge variations: conventional plug flow, complete mix, and extended aeration activated sludge plants.

Activated Sludge Aeration Tank Design Parameters Table

The spreadsheet image below uses Excel formulas to calculate the required aeration tank volume based on values provided for the input parameters shown in the spreadsheet: primary effluent flow rate, Qo; primary effluent biochemical oxygen demand (BOD), So; Aeration tank MLSS, X; design volumetric loading, VL; and % volatile solids in the aeration tank, %Vol.  The Excel spreadsheet uses the input data to calculate the design value of aeration tank volume, V, and values for two other design parameters that are sometimes used to size the aeration tank, hydraulic retention time in the aeration tank, HRT, and food to microorganism ratio, F:M, using the following equations.

  • V = [ (8.34*So*Qo)/VL ] (1000)
  • VMGV*7.48/1,000,000
  • HRT = 24*VMG/Qo
  • F:M = (8.34*So*Qo)/(8.34*%Vol*X*VMG)  =  (So*Qo)/(%Vol*X*VMG)

For Excel spreadsheets to make a variety of activated sludge aeration tank design and operation calculations in either U.S. or S.I. units for a very reasonable price, click here to visit our spreadsheet store.

Activated Sludge Aeration Tank Design Calculations Spreadsheet

Activated Sludge Aeration Tank Operational Calculations with Excel Spreadsheets

The table below shows typical ranges for several commonly used operational activated sludge waste water treatment process parameters. Note that the values for all of these parameters remain the same for U.S. or S.I. units.  MLSS concentration will have the S.I. unit of g/m3, which is numerically equal to the U.S. units of mg/L. SRT will have units of days for either the U.S. or S.I. system.  F:M will have the S.I. unit of kg BOD/day/kg MLVSS, which is numerically equal to the U.S. unit of lb BOD/day/lb MLVSS. The % unit for Qr/Qo remains the same for U.S. or S.I. units.

Activated Sludge Aeration Tank Design Operational ParametersThe Excel spreadsheet image shown below uses the input values to calculate the activated sludge operational parameters shown.  The input parameters required are: primary effluent information (flow rate, Qo; BOD, So; and TSS, Xo); TSS in the waste and recycle activated sludge streams, Xw; the aeration tank volume, V; aeration tank MLSS, X; % volatile solids in the MLSS, %Vol; and sludge retention time, SRT.

The activated sludge operational parameters calculated by the Excel formulas in the spreadsheet are: recycle activated sludge flow rate, Qr; waste activated sludge flow rate, Qw, and aeration tank food to microorganism ratio, F:M. The equations used are as follows:

  • Qr = (X – Xo)/(Xw – X)
  • VMGV*7.48/1,000,000
  • Qw = (8.34*VMG*X)/(8.34*SRT*Xw)  =  (VMG*X)/(SRT*Xw)
  • F:M = (8.34*So*Qo)/(8.34*%Vol*X*VMG)  =  (So*Qo)/(%Vol*X*VMG)

For Excel spreadsheets to make a variety of activated sludge aeration tank design and operation calculations in either U.S. or S.I. units at a very reasonable price, click here to visit our spreadsheet store.

Activated sludge aeration tank design and operations spreadsheet

References

References for Further Information:

1. Metcalf & Eddy, Inc, (revised by Tchobanoglous, G, Burton, F.L., Stensel, H.D., Wastewater Engineering Treatment and Reuse, 4th Edition, New York, NY, 2003.

2. Vesilind, P.A. and Morgan, S.M., Introduction to Environmental Engineering, 2nd Edition, Belmont, CA, Brooks/Cole, 2004.

3. Grady, C.P.L., Daigger, G.T., Lim, H.C., Biological Wastewater Treatment, 2nd Edition, New York, NY, Marcel Dekker, Inc., 1999.

4. Bengtson, Harlan H. “Biological Wastewater Treatment Process Design Calculations,” available as an Amazon Kindle e-book and as a paperback.

5. Bengtson, Harlan H., “Activated Sludge Calculations Spreadsheet: Aeration Tank Calculations,” an Amazon Kindle e-book.

Hydrology/Hydrograph Calculations Excel Spreadsheets

Introduction to Hydrology/Hydrograph Calculations Excel Spreadsheets

For hydrologyhydrograph calculations Excel spreadsheetsclick here to visit our spreadsheet store.  Read on for information about hydrographs, their components and baseflow separation.

Use of hydrographs in hydrology applications often involves calculations with tables of values.  Thus Excel spreadsheets are very useful for such calculations.  Read on for information about the components of hydrographs, baseflow separation, generation of unit hydrographs and use of unit hydrographs.

What is a Hydrograph?

For use in hydrology, the term hydrograph means a graph or table of values showing the changes in flow rate over time at a point on a river or stream or some other point of interest.  Possible points of interest for a hydrograph include locations like a storm water drainage outlet from a drainage area or the entrance to a storm water detention system.  Hydrographs are used to show flow patterns following a storm, thus providing information about the storm water runoff rate at the point of interest.

Excel Spreadsheets for Hrdrology Hydrograph Calculations FigureFor a storm hydrograph at a point on a river or stream, there will typically be a gradually decreasing flow rate before the beginning of the storm.  After the storm begins, the flow rate increases as storm water runoff from 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 a hydrograph where there’s no non-storm water flow, the hydrograph will start at zero flow prior to the storm and go back down to zero flow.

Hydrograph Components – Baseflow and Direct Runoff

Spreadsheets for Hydrology Hydrograph Calculations FigureThe flow represented by a hydrograph for a point on a river or stream is considered to be made up of two parts, the baseflow, which is the normal dry weather flow of the river or stream, and direct runoff, which is the component of flow due to storm water runoff. The direct runoff due to the storm is often the part that is of interest.  In order to determine the direct runoff from a storm, its necessary to separate the baseflow from the hydrograph, leaving the direct runoff hydrograph.  The diagram at the left shows three methods used for baseflow separation: the constant discharge method; the constant slope method; and the concave method.

The constant discharge method simply uses a horizontal line from the point where the hydrograph begins to rise to its intersection with the receding limb.  The baseflow separation line for both the constant slope method and the concave method should intersect with the receding limb at the inflection point (where the receding limb changes from convex to concave).  The time from the peak of the hydrograph to the inflection point of the receding limb is often calculated with the equation:  N = A0.2,  as shown on the diagram.  This is a dimensional equation, in which A is the watershed area in mi2, and N is time in days.  The constant slope method uses a straight line from the point where the hydrograph begins to rise to the inflection point on the receding limb.  The concave method extends the pre-storm slope of the baseflow line to a point directly below the peak and then uses a straight line to the inflection point on the receding limb.

For Excel spreadsheets to make a variety of hydrograph calculations, including baseflow separation by the concave method, click here to visit our spreadsheet store.

Reference

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

 

Orifice and Venturi Flow Meter Calculations Spreadsheet

Introduction to Orifice and Venturi Flow Meter Calculations Spreadsheet

For an orifice and venturi flow meter calculations spreadsheetclick here to visit our spreadsheet store.  Read on for information about Excel spreadsheets that can be used as orifice and venturi flow meter, pipe flow rate calculators.

Excel spreadsheets are convenient for differential pressure flow meter calculations,  for meters such as the commonly used orifice flow meter and venturi meter.  The general equation for differential pressure flow meters can be built into the spreadsheets with Excel formulas.  Also, for gas flow, the ideal gas law can be used to calculate the gas density based on its temperature, pressure, and molecular weight.  An Excel spreadsheet can also be used to calculate the orifice coefficient for anorifice meter with one of the ISO standard pressure tap configurations.

Background for an Orifice and Venturi Flow Meter Calculation Spreadsheet

Orifice and Venturi Flow Meter Calculations Spreadsheet diagramOrifice and venturi meters both function by sending pipe flow through a constricted area (the orifice plate or the venturi throat), as shown in the diagrams at the right.  Due to the increased fluid velocity passing through the constriction, there will be a decreased pressure at that location.   The pipe flow rate can then be calculated from the measured pressure difference between the undisturbed pipe flow and the flow through the constriction.

The general equation for calculating fOrifice and Venturi Flow Meter Calculations Spreadsheet Equationlow rate through either an orifice or venturi meter is shown at the left, where the parameters in the equation and their units are as follows:

 

  • Q is the flow rate through the pipe and through the meter  (cfs – U.S. or m3/s – S.I.)
  • Cd is the discharge coefficient, which is dimensionless
  • Ao is the constricted area perpendicular to flow  (ft2 – U.S. or m2 – S.I.)
  • P1 is the undisturbed upstream pressure in the pipe  (lb/ft2 – U.S. or N/m2 – S.I.)
  • P2 is the pressure in the pipe at the constricted area, Ao (lb/ft2 – U.S. or N/m2 – S.I.)
  • β = D2/D1 = (diam. at A2/pipe diam.), which is dimensionless
  • ρ is the fluid density (slugs/ft3 – U.S. or kg/m3 – S.I.)

Orifice and Venturi Flow Meter Calculations Spreadsheet Screenshot

The image below shows an Excel spreadsheet that can be used as a pipe flow rate calculator, based on the measured pressure difference across a flow nozzle, venturi, or Orifice flow meter.  This spreadsheet is suitable when the fluid density is known (as for a liquid) and the meter coefficient, C, is known.  For this spreadsheet and another to calculate the density of a gas using the ideal gas law model, click here to visit our spreadsheet store.

Orifice and Venturi Flow Meter Calculations Spreadsheet ScreenshotReferences

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. U.S. Dept. of the Interior, Bureau of Reclamation, 2001 revised, 1997 third edition, Water Measurement Manual, available for on-line use or download at: http://www.usbr.gov/pmts/hydraulics_lab/pubs/wmm/index.htm

3. International Organization of Standards – ISO 5167-1:2003 Measurement of fluid flow by means of pressure differential devices, Part 1: Orifice plates, nozzles, and Venturi tubes inserted in circular cross-section conduits running full. Reference number: ISO 5167-1:2003.

4. Bengtson, Harlan H., “Orifice and Venturi Flow Meters: for Liquid Flow and Gas Flow.” an Amazon Kindle e-book.

Storm Sewer Hydraulic Design with Excel Spreadsheets

Where to Find Storm Sewer Hydraulic Design Spreadsheets

For storm sewer hydraulic design spreadsheets, click here to visit our spreadsheet store.  Read on for information about the use of Excel spreadsheets for storm sewer hydraulic design calculations with the Manning Equation.

One part of storm sewer hydraulic design is determination of the design pipe diameter and sewer slope for the storm sewer pipe between adjacent 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.  A storm sewer hydraulic design spreadsheet typically makes hydraulic calculations for full pipe flow.  For full pipe flow, the hydraulic radius becomes: R = A/P = (πD2/4)/(πD) = D/4.

The Manning Equation in a Storm Sewer Hydraulic Design Spreadsheet

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:

  • V is the flow velocity in the pipe (ft/sec – U.S. and m/s – S.I.).
  • n is the Manning roughness coefficient, an empirical, dimensionless constant.
  • D is the pipe diameter (ft -U.S. and m – S.I.).
  • S is the pipe slope, which is dimensionless.

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 with a Storm Sewer Hydraulic Design Spreadsheet

Diagram for Storm Sewer Hydraulic Design SpreadsheetThe required diameter and slope for the length of storm sewer between two manholes can be calculated with a storm hydraulic sewer design spreadsheet 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 above 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 Storm Sewer Hydraulic Design Calculations

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 Manning roughness coefficient (concrete pipe) is: n = 0.011.  Find a standard pipe diameter and sewer slope that will meet the two criteria: Qfull > Qdes and Vfull > Vmin for 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 sewer 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 Design 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 spreadsheet store.

References

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

5. Bengtson, Harlan H., “Hydraulic Design of Storm Sewers with Excel”  an online blog article.

 

 

Storm Sewer Design Spreadsheet Calculations

Where to find a Storm Sewer Design Spreadsheet

For a storm sewer design spreadsheetclick here to visit our spreadsheet store.  Read on for information about the use of Excel spreadsheets for storm sewer design calculations.

The storm sewer design spreadsheet discussed in this article uses Excel formulas with the rational method to find design storm water runoff rate and the Manning equation to find pipe diameter and slope.

The hydraulic portion of stormwater sewer design proceeds in the form of calculations between each pair of manholes in the storm sewer line. The first part of the spreadsheet is essentially a rational method design spreadsheet used to determine the design stormwater runoff flow rate for each section of storm sewer being designed. The next part of the spreadsheet is used to calculate the pipe diameter and slope for each section of storm sewer with the Manning Equation. Finally, the pipe invert elevation at each manhole is calculated in the last part of the spreadsheet.  Each part of the storm sewer design spreadsheet will be discussed briefly in the next several sections, followed by presentation and discussion of an Excel spreadsheet template to make the calculations.

Peak Storm Water Runoff Rate for Storm Sewer Design Spreadsheet

The rational method equation (Q = CiA for U.S. units and Q = 0.0028 CiA for S.I. units) is widely used to calculate the design stormwater runoff rate to use for a variety of storm water projects, including storm water sewer design.  The parameters in the rational method equations are:

  • Q, the design storm water runoff rate (cfs – U.S. and m3/s – S.I.)
  • C, the runoff coefficient, which is an estimate of the fraction of rainfall that becomes surface runoff (dimensionless)
  • i, the design rainfall intensity (in/hr – U.S. and mm/hr – S.I.)
  • A, the runoff area that drains to the section of sewer pipe being designed (acres – U.S. and ha – S.I.)

Manhole Map for Storm Sewer Design Spreadsheet ExampleThe storm sewer design spreadsheet being discussed here will assume that the manhole locations have already been determined, as shown in the diagram above.  A street map like this would be used to determine the area draining to each of the manhole inlets for the length of storm sewer being designed.

Criteria Used in Storm Sewer Design Spreadsheet

Following are the criteria typically used to calculate the design pipe diameter and sewer slope for a length of sewer pipe:

  1. The pipe must be sized to carry the design peak stormwater runoff rate.
  2. The velocity in the sewer pipe must be greater than or equal to the design minimum velocity (usually 3 ft/s).

The use of these design criteria, together with the Manning equation

[ Q = (1.49/n)(A)(R2/3)(S1/2) ]  and Q = VA, to calculate the pipe diameter and slope is discussed and illustrated with an example in the article, “Storm Sewer Hydraulic Calculations with the Manning Equation.”  The procedure is also illustrated in the spreadsheet template presented later in this article.

Invert Elevations at Manholes in the Storm Sewer Design Spreadsheet

The sewer pipe invert elevation (or depth) at the uppermost manhole is determined by the minimum required depth of cover above the sewer pipe to protect it from freezing. This required minimum cover is usually specified by a state or local agency.  For subsequent manholes, the required minimum cover, the required pipe slope, and the ground surface elevations from a street/manhole map like that shown in a previous section above, are used to calculate the pipe invert elevations.  Calculation of the invert elevations at manholes with a storm sewer design spreadsheet is presented in the next section.

Putting it together in a Storm Sewer Design Spreadsheet

The storm sewer design spreadsheet template shown in the two images below contains design calculations for a storm sewer line along one of the streets on the manhole layout map shown above in the second section of this article.  The spreadsheet makes the calculations described above.  The various parts of the spreadsheet will now be discussed briefly with reference to the column numbers given on the spreadsheet.

Columns 1, 2, and 3 contain information from a scale street/manhole map, such as the one shown earlier in this article. Column 4 is the calculated cumulative area draining to downstream sections of storm sewer pipe. The uppermost part of the sewer line in this example is the manhole at 8th Street and Maple Avenue.  An estimate of the runoff coefficient is given in column 5.  Column 6 shows the inlet time from the farthest point in the drainage area. For the uppermost section of sewer pipe, the inlet time is equal to the time of concentration.  For the other sections of sewer pipe in the line being designed, the time of concentration is the inlet time to the first inlet plus the pipe flow time to the inlet of the pipe section currently being designed.  This is calculated in column 7.

Column 8 is the calculated design rainfall intensity. The portion of the Excel template shown at the right below has the Excel formulas for derivation of an equation for storm intensity vs storm duration for a given return period, using linear regression of storm duration, δ, vs the inverse of storm intensity, 1/i.   This requires at least some values of i vs δ, from  I-D-F data for the location of interest.  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 the calculation of peak storm water runoff rate (the design flow rate) with the rational method equation:  Q = CiA.

Columns 10 through 15 use of the Manning Equation and Q = VA to determine the minimum standard pipe diameter and sewer slope needed, as well as to make a check on Vfull and Qfull when the pipe is receiving the design stormwater runoff flow rate.  This set of calculations is discussed in some detail in the article: Storm Sewer Hydraulic Calculations with the Manning Equation.”

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 (taken to be 5′ ) plus the pipe diameter. The invert elevation at the lower end of the pipe section is calculated using the sewer 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.

For a low cost, easy to use storm water design spreadsheet and spreadsheets for several other types of storm water calculations, click here to visit our spreadsheet store.

screenshot of part of a storm sewer design spreadsheet

References

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, 1998.

3. Knox County Tennessee, Stormwater Management Manual, section on the Rational Method

4. Bengtson, Harlan H., Hydraulic Design of Storm Sewers with a Spreadsheet,” an Amazon Kindle ebook

5. Bengtson, Harlan H., “Hydraulic Design of Storm Sewers with Excel”  an online blog article.


 

 

 

Pipe Flow-Friction Factor Calculator Excel Spreadsheet

Where to Find a Pipe Flow-Friction Factor Calculator Excel Spreadsheet

For a pipe flow-friction factor calculator Excel Spreadsheet, click here to visit our spreadsheet store.  Why read values from a Moody diagram, make iterative calculations to get friction factor, or use online calculators, when you can get a pipe flow-friction factor calculator Excel Spreadsheet for only $19.95?  Read on for information about the Darcy Weisbach equation and its use in an Excel spreadsheet as a friction factor/pipe flow calculator.

Excel spreadsheets are very convenient for Darcy Weisbach equation/pipe flow calculations, such as frictional pressure drop calculation or use of a friction factor calculator, at least in part because some of the calculations require iterative solutions.  The Darcy Weisbach equation is applicable to pressure flow in pipes, rather than gravity flow (as in sewer pipes), which is handled by open channel flow equations like the Manning equation.  The Darcy Weisbach equation provides the relationship among the following parameters: pipe diameter and length, pipe flow rate, and  frictional pressure drop or head loss.  Any one of these can be calculated if the others are known along with the density and viscosity of the fluid.

A Friction Factor Calculator and the Darcy Weisbach Equation

Moody diagram for pipe flow-friction factor calculator excel spreadsheetThe Darcy Weisbach equation  is hL= f(L/D)(V2/2g), with the parameters in the equation as follows: hLis 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 (Re) 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 right shows the nature of the dependence of the friction factor, f,  on Re and e/D.

Friction factor equations for pipe flow-friction factor calculator Excel spreadsheetEquations for f as a function of Re and e/D would be more convenient than a graph like the Moody Diagram for use with Excel spreadsheets making  pipe flow calculations with the Darcy Weisbach equation.  Such equations are shown in the box at the left, giving the relationships between Moody friction factor and Re & e/D for four different portions of the Moody diagram.  The four portions of the Moody diagram are:

  • laminar flow(Re < 2100 – the straight line at the left side of the Moody
  • smooth pipe turbulent flow(the dark curve labeled “smooth pipe” in the Moody diagram – f is a function of Re only in this region)Pipe Roughness Values for Pipe Flow-Friction Factor Calculator Excel Spreadsheet
  • completely turbulent region(the portion of the diagram above and to the right of the dashed line labeled “complete turbulence” – f is a function of e/D only in this region)
  • transition region(the portion of the diagram between the “smooth pipe” solid line and the “complete turbulence” dashed line – f is a function of both Re and e/D in this region and this is not an explicit equation for f)

The table above right gives pipe roughness values for several common pipe materials.  These can be used to calculate the pipe roughness ratio, e/D.

For a low cost Moody friction factor calculator download, that will calculate f for Reynolds number above 2100, see: www.engineeringexceltemplates.com

Frictional Head Loss and Frictional Pressure Drop Calculation

After using the Moody friction factor calculator to get a value for the friction factor, f, frictional head loss calculation is quite straightforward if the pipe length & diameter and average flow velocity are known.  You simply need to substitute values for L, D, V, and f into the Darcy Weisbach equation [hL= f(L/D)(V2/2g) ].  The Darcy Weisbach equation is a dimensionally consistent equation, so any consistent set of units can be used.  For U.S. units, hL, L, and D are typically in ft, V is in ft/sec, and g is 32.2 ft/sec2.  For S.I. units, hL, L and D are typically in m, V is in m/s, and g is 9.81 m/s2.  If volumetric flow rate, Q, is known rather than average velocity, V, then V can be calculated from:

equation to use with pipe flow-friction factor calculator Excel spreadsheet

Frictional pressure drop calculation from frictional head loss is done through the equation:

equations for use with pipe flow-friction factor calculator Excel spreadsheet

A Screenshot for a Pipe Flow-Friction Factor Calculator Excel Spreadsheet

The Excel spreadsheet screenshot below shows a pipe flow-friction factor calculator excel spreadsheet that is available as part of the “Pipe Flow-Friction Factor Calculation Package,”  at our spreadsheet store in either U.S. or S.I. units at a very low cost (only $16.95).  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 serve as a pipe flow rate calculator for known head loss/pressure drop, and pipe diameter, length & material; and one to calculate required pipe diameter for known head loss/pressure drop, flow rate, and pipe length & material.

spreadsheet screenshot for pipe flow friction factor calculator Excel spreadsheet
References

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, and online continuing education course for Professional Engineers.

5. Bengtson, Harlan H., “Pipe Flow Friction Factor Calculations with Spreadsheets,” available as an Amazon Kindle e-book and as a paperback.

6. Bengtson, Harlan H., “Pipe Flow Calculations with the Darcy Weisbach Equation“, an online blog article