Solving the Colebrook Equation for Friction Factors
Part 2 of 3 in a series of articles

User-Defined Functions

As an alternative to solving Colebrook using Iteration, User Defined Functions (UDFs) can be written that use a variety of methods to solve Colebrook.   In this series, we will examine the use of an iterative like approach that doesn’t require the initiation of the built-in Iteration Function (Goal Seek).  One advantage of this UDF that it can be used in a series of calculations such as a piping network without initiating an Iteration.  Another advantage is that it can be used in conjunction with another Iteration without embedding Iterations.  An example of this second advantage would be to Iterate for a fixed pressure drop by changing the flow, diameter or Relative Roughness.  An example will be presented.

The UDFs presented in the demonstration spreadsheet are defined in the Visual Basic Editor.  (It’s not necessary to examine them but if you desire to do so, they can be viewed from the menu bar Tools/Macro/Visual Basic Editor or from the keyboard shortcut “Alt-F11”.  In the Editor, the UDF “=fEq1()” is Module1.  These UDFs require the bare minimum knowledge of Visual Basic.   This, however, is beyond the scope of this series, although I would encourage the reader to look at how they have been created.)

Each UDF is basically identical with only the difference in the right side of the equation changed for the three Colebrook Equations.  The basic routine is:

-Visualize a plot of the Difference (as described in the previous Section) versus the Friction Factor.

-Get an initial value for “f “ using the Swamee and Jain equation.  (More on Swamee and Jain Equation in the Explicit Section.)

-Enter a “Do Loop Until” loop.

-Calculate the slope of the Difference line.

-Project where a straight line with this slope and “f “ value will cross the Friction Factor line.

-Using the projected value from above, repeat the process until the conditions are satisfied.

For a detailed explanation, refer to “Solving for Friction Factor”, ASHRAE Journal July 2003⁸.

Open the workbook under the Tab “User Defined Func”.   In column G the corresponding UDF is given for each form of Colebrook.  As different values of Relative Roughness and Reynolds Number are entered, the results from the UDFs are changed immediately.  The Iteration Command Button can be clicked to perform the Iterations as before so that the Iteration results can be compared with the UDFs.  “Check Values” in column I show that the UDF’s have successfully calculated the Friction Factor to 1.000000 in all cases.

Example:       

From the “User Defined Func”workbook, first enter a Relative Roughness in cell C3 of .005 and a Reynolds Number of 1,000,000.  The various Friction Factors will be approximately .0304…..   Now lets assume that we want to know what Relative Roughness will give us a Friction Factor of .0200.

-In cell E9 enter the formula “=G9*1000” without the quotation marks.  (This is necessary to achieve the desired accuracy.)

-Place the cursor in cell E9.

-Select Tools\Goal Seek from the menu.

-The “Set Cell” should show “E9” if the cursor started in cell “E9”

-Tab down to the “To Value” and enter 20.  (This is 1000 times the desired Friction Factor of .0200.)

-Tab down to the “By Changing cell” and enter C3.   (This is the Relative Roughness number that will be changed to give the desired result.)

-Hit the “OK” Button and again hit the “OK” Button.

At this point, the Relative Roughness in cell C3 should have changed to .0010124 and the Friction Factor in cell G9 should have changed to .0200….

UDFs exist in the spreadsheet that they were created in or in a spreadsheet were they have been copied to.  They don’t exist in a blank spreadsheet.  To facilitate the process of using the UDF’s in an existing or new spreadsheet, each UDF has been exported to a “filename”.bas file.  To copy a UDF to a blank or existing spreadsheet;

-Place the desired “filename”.bas in a directory where it can be easily found.

-Open the spreadsheet that you wish to add the UDF to.

-Go to the Visual Basic Editor.  (From the menu, Tools/Macro/Visual Basic Editor or use the keyboard shortcut “Alt-F11”.

-In the Visual Basic Editor, from the menu bar, do a File/Import File/Hi-lite the file to be imported, a “filename”.bas file.

-Hit the OK button and close the Visual Basic Editor.

The UDF is now part of your spreadsheet and will be saved as part of the file, when you save it.

To use the new spreadsheet, first decide which cells will contain the Relative Roughness and the Reynolds Number.  Second, you can do either;

-From the Functions List, under User Defined Functions, you can select the Function and assign cell references for the Relative Roughness and Reynolds Number

or

-Simply write in the formula as “=fEq1(C3,C4)” assuming you want to use “fEq1(), the Relative Roughness is in cell C3 and Reynolds Number is in cell C4.

There is a problem with Eq 3; it isn’t capable of producing a result for a Relative Rouhgness of zero (0), (representing smooth pipe).  If the Iteration is run with a zero (0) entered, the Visual Basic dialog box will appear with the message, “Run-time error ‘1004’.  Reference is not valid”  Click on the “End” Button to get out of the macro.  A good approximation can be calculated by entering a very small Relative Roughness, say .000 000 001.  The UDF for Eq 3 will return “#VALUE!” with zero (0) Relative Roughness.

That completes the Section on UDFs.  Next we will examine several Explicit Functions to calculate Friction Factor.

Explicit Forms

As mentioned in the Introduction, there are four Explicit Equations that will be discussed.

Serghide’s Solution.³

            A = -2 log₁₀[(RelRough / 3.7) + (12 / Reynolds#)]

            B = -2 log₁₀[(RelRough / 3.7) + (2.51*A / Reynolds#)]

C = -2 log₁₀[(RelRough / 3.7) + (2.51*B / Reynolds#)]

f = (A-(B-A)² / (C-(2*B) + A))^-2

Serghide can be used across the entire range of the Moody Diagram.   Its accuracy is unparalleled amongst the Explicit Equations evaluated here.  It appears to be based on Eq 1, as do all the Explicit Equations presented.  There is less deviation between Serghide and Eq 1 then there is between Eq 1 and either Eq 2 or Eq 3. 

The soft spot, if one can call this minimal deviation a soft spot, exists with Smooth Pipe (e/D = 0) and a Reynolds Number of 170,000.  At this point, the deviation between Serghide and the iterative solution of Eq 1 is .0031..%.  Because Serghide so closely mirrors Eq 1, it has approximately the same deviation to Eq 2 and Eq 3 as does Eq 1.

Serghide is perhaps the most complex entry that must be made into a spreadsheet.  The A, B and C parameters can be entered into separate cells and then the Friction Factor can be calculated in a fourth cell.  In the actual demonstration spreadsheet, under the Tab “Explicit Eq”, the calculations are placed in cells B42 to C48.  A User Defined Function, fSerg, was written as well.  The VBM for this is fSerg.bas and can be copied to an existing worksheet in the same manner as described in the User Defined Function Section.

Zigrang and Sylvester Solution.⁴

f = 1 / (-2log₁₀(RelRough / 3.7 - 5.02 / Reynolds# * log₁₀(RelRough / 3.77 –

5.02 / Reynolds# * log₁₀(RelRough / 3.77 + 13 / Reynolds#))))²

Zigrang, like Serghide, can be used across the entire range of the Moody Diagram.  Of the Explicit Equations evaluated here, It is second in accuracy to Serghide.  The soft spot exists with Smooth Pipe (e/D = 0) and a Reynolds Number of 64,500.  At this point, the deviation between Zigrang and the iterative solution of Eq 1 is 0.11%.  (This still compares favorable with the maximum deviations between Eq 1 and either Eq 2 or Eq 3.)  The deviation between Zigrang and Eq 2, at these same conditions, is 0.22%.

One significant advantage of Zigrang is that it can be placed in a single cell of a spreadsheet, albeit a long entry.

Swamee and Jain.⁵

            f = .25 / (log₁₀((RelRough / 3.7) + (5.74 / Reynolds#^.9)))²

Swamee & Jain has limits but varies sources state these limits differently.  The referenced source states the limits as:

            10^-6 < e/D < .01 and 5000 < Reynolds Number < 3x10⁸

Statements vary around accuracy but the reference states, “An easier, and almost as accurate procedure as the Moody Diagram is to use the empirical formulas of Swamee and Jain,…..”  Deviation to Eq 1 of 2.8+% is seen at e/D of .01 and Reynolds Number of 5000.

Swamee and Jain is easily entered into a single cell of a spreadsheet.  The fact that is has a limited range of use, while other Explicit Equations, specifically Serghide or Zigrang, do not, is a significant disadvantage.

Altshul-Tsal⁶

            f ’= 0.11 * (RelRough + 68/Reynolds#)^.25

            if f ’ < .018, f = .85 f ’ + .0028

            otherwise, f = f ’

Altshul – Tsal can be found in numerous references and is generally not accompanied with any limited range of use.  This is regrettable as its accuracy is limited to Relative Roughness in the lower half of the Moody Diagram.  In the extreme case of Relative Roughness of .05, there is a 27+% deviation with the iterative solution of Eq 1, across the entire range of Reynolds Numbers.

By: Thomas G. Lester, P.E., Bergmann Associates
tlester@bergmannpc.com