Colebrook Equation - A simple Excel Macro to solve this implicit equation

Excel has several methods of solving for implicit equations, which is what the Colebrook Equation is, used for calculating the Darcy friction factor, which is then used with another equation in order to get the pressure drop due to turbulent flow in a pipe.

The first is by turning on the Iterate option inside the Excel worksheet then using circular references (eg the equation refers to itself) and iterating such that the values converge. It can however cause some problems if the values DON'T converge or zero errors. Dismally, as the calculations involved with  Colebrook is quite sensitive, it's necessary to change the default settings so that changes between iterations have a low maximum value (in the region of 1e-6). Crucially, changing the values of ANY cells causes the spreadsheet to recalculate all the information, and in a large spreadsheet, editing can quickly become a pain especially with the increased sensitivity requirements.

Another method is via nesting, whereby you design the equation such that the results of one is nested into the same equation. However, designing such an equation is not trivial especially using computer systems (parentheses quickly become a pain), although the process is relatively straightforward. The advantage of this is that you do it once and never have to go back and do it again, and you are limited only by how tenacious you are in nesting the equations (after about 5 or 6, it gets really long). The advantages are that you can iterate exactly how many times you need, and that it's hardcoded into Excel as an equation, so no problems with recalculating or error when moving the file around.

Finally, a simpler method has been devised by Diddler Clamond, written in a paper called Efficient Resolution of the Colebrook Equation. This relatively recent paper (2009) outlines a simple algorithm that produces, in the words of the author himself, extremely accurate results (around machine precision). The trick of the equation relies on two magical numbers, 0.123968186335418 and -0.779397488455682, of which the specific derivation can be found in the paper. Its a statistical method and seems to work really well, and quickly solves the equation.

The macro is:

Function Colebrook(R As Double, K As Double) As Double
    Dim X1 As Double, X2 As Double, F As Double, E As Double
    X1 = K * R * 0.123968186335418
    X2 = Log(R) - 0.779397488455682
    F = X2 - 0.2
    E = (Log(X1 + F) + F - X2) / (1 + X1 + F)
    F = F - (1 + X1 + F + 0.5 * E) * E * (X1 + F) / (1 + X1 + F + E * (1 + E / 3))
    E = (Log(X1 + F) + F - X2) / (1 + X1 + F)
    F = F - (1 + X1 + F + 0.5 * E) * E * (X1 + F) / (1 + X1 + F + E * (1 + E / 3))
    F = 1.15129254649702 / F
    Colebrook = F * F
End Function

Simply insert this into your Excel worksheet and you can call up the function =Colebrook(Re, Rel.Roughness) and it automatically returns the Friction Factor. Note that the values of Re and Rel.Roughness needs to be in the Double format, otherwise the macro will scream.

In my tests with the equation, it seems to be very accurate as the author claims. Using values of Re = 165000 and Rel.Roughness as 0.00453, the traditional Iterative method gets the results as 0.030097679 while the statistical method yields 0.030097700. Not bad for a simple two-step iterative statistical method, and you will definitely see this being applied into numerical and CAD/CAE systems very soon, especially those simulating pipelines where millions of the equations are being calculated, and the small processing savings of an improved algorithm will quickly add up over the course of the simulation.

Definitely a much improved method to get the F.F compared to traditional ways of either using the GoalSeek Excel function (the least efficient process, thus not discussed here) or, gasp, the trial and error method.