
Solving the
Colebrook Equation for Friction Factors
UserDefined 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 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 “AltF11”. 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^{8}. 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 “AltF11”. In
the Visual Basic Editor, from the menu bar, do a File/Import File/Hilite 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, “Runtime 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.^{3}
A = 2 log_{10}[(RelRough / 3.7) + (12 / Reynolds#)]
B = 2 log_{10}[(RelRough / 3.7) + (2.51*A / Reynolds#)] C
= 2 log_{10}[(RelRough / 3.7) + (2.51*B / Reynolds#)] f
= (A(BA)^{2} / (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.^{4} f
= 1 / (2log_{10}(RelRough / 3.7  5.02 / Reynolds# * log_{10}(RelRough /
3.77 – 5.02
/ Reynolds# * log_{10}(RelRough / 3.77 + 13 / Reynolds#))))^{2} 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.^{5}
f = .25 / (log_{10}((RelRough / 3.7) + (5.74 / Reynolds#^.9)))^{2} 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^{8} 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. AltshulTsal^{6}
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 
