▼

Solving the Colebrook-White Equation with Excel

The Colebrook-White describes the relationship between the Reynolds Number Re, the pipe relative roughness e, and the friction factor f in turbulent pipe flow.


Usually, we fix a value for Re and e, and compute f.  However, this equation cannot be solved explicitly, so numerical iteration is needed.

There's several ways we can do this in Excel.  I'll outline the two most convenient methods.  If you just want the spreadsheet, scroll to the bottom of this post and download the spreadsheet.

Spreadsheet Setup
This is a screengrab of the spreadsheet included with this post.


Note that the parameters to be entered by the user are highlighted green. I've also assigned cell names to each parameter, so the pipe roughness is e, the pipe diameter is D, and so on.

Method 1 - Using VBA and Goal Seek
This VBA macro fires up Goal Seek whenever there is any change in the worksheet (due to the Worksheet_Change() event).

Private Sub Worksheet_Change(ByVal Target As Range)
Dim bSuccess As Boolean
    On Error Resume Next
    bSuccess = Range("fCheck").GoalSeek(0, Range("f"))
    On Error GoTo 0
    If Not bSuccess Then
        MsgBox "Goal Seek Failed for Cell ""X""!"
    End If
End Sub

The code finds the value of f that will make fCheck equal 0.

Method 2 - Worksheet Iteration
You'll need to enable worksheet iteration so that Excel can correctly resolve a circular reference.  In Excel 2010 go to File>Options>Formulas, and check Enable Iterative Calculations.  You might also want to increase the number of iterations to 500, and reduce the maximum change to 0.0001).

The trick here is to rearrange the Colebrook-White equation so you have nothing but the friction factor f on the left hand side of the equation (it can appear on the right-hand side together with the other terms in the equation).


This is the formula I've typed into Cell B17 of the spreadsheet - it's just the rearragned Colebrook-White Equation, but with one minor change.

=1/(-2*LOG(e/(D*3.7) + 2.51/(Re*SQRT(B17+1E-300))))^2

When Excel starts iterating, it initializes B17 with a value of zero.  However, this will a divide-by-zero error.  To resolve this, I've added a very small number (1E-300) to B17.  This doesn't significantly change the accuracy of the computer friction factor.

Both Method 1 and 2 give roughly the same value of f.  I prefer Method 1 (purely because enabling spreadsheet iteration means you will not necessarily be informed of any unintended circular references).

3 comments:

  1. robhansonFebruary 10, 2012 at 5:40 AM

    Hi,

    I've been using the second method but have a problem. If you remove one of the variables, then re-enter the formula no longer works, unless you change it very slightly, i.e. by removing a 0 from b8. Any idea why this happens? It happens in yours also, bit confusing!

    thanks,
    Rob

    ReplyDelete
  2. UnknownFebruary 10, 2012 at 11:08 AM

    That happens with me as well. I'll look into this when I have time.
    S

    ReplyDelete
  3. PezFebruary 22, 2012 at 2:27 PM

    i tend to nest "iferror()" statements around problematic areas, such as square roots and places where "div0#" errors may occur, with default, or starting values, this stops the formula from causing an error, if it does, the calculation simply resets.

    typically once an error of some kind occurs in an iterative solution, it is not as simple as resetting the values to reset the formula, you need to fix the result from the error to the default to allow the calculation to proceed.

    ReplyDelete