How to Solve Colebrook Equation in Excel (3 Simple Ways)

What Is a Colebrook Equation?

The Colebrook equation shows the relationship between the Reynolds number, Pipe Roughness, and Diameter based on the Friction factor of the pipe.

How to Solve Colebrook Equation in Excel

Friction Factor, f
Pipe Roughness, k
Pipe Diameter, D
Reynolds Number, Re

How to Solve a Colebrook Equation in Excel: 3 Simple Ways

Let’s consider the dataset shown in the B4:D12 cells. We have the distribution of the Pipe Roughness and the Reynolds Number for a fixed Pipe Diameter.

Dataset

Method 1 – Using the Goal Seek Tool to Solve the Colebrook Equation

Steps:

Enter the left side of the Colebrook equation in the C9 cell.

=1/SQRT(B9)

The B9 cell refers to the Friction Factor.

Move to the D9 cell and enter the right side of the Colebrook expression:

=-2*LOG((D4/(3.7*D5))+(2.51/(D6*SQRT(B9))))

The D4, D5, and D6 cells represent the Pipe Roughness, Pipe Diameter, and Reynolds Number respectively. Moreover, use the LOG function according to the equation.

Navigate to the E9 cell and calculate the difference between the right side and the left side:

=D9-C9

C9 and D9 cells point to the LHS and RHS, respectively.

Go to the Data tab and click the What-If Analysis drop-down.
Select the Goal Seek option.

This opens the Goal Seek wizard.
In the To value field, type in 0.
For the By changing cell option, select the B9 cell.

Your output should look like the picture given below.

Method 2 – Utilizing Worksheet Iteration to Solve the Colebrook Equation

We’ll rearrange the Colebrook equation as shown below.

Steps:

Click the File tab at the top left corner.

Click the Options button at the bottom of the window.

This opens the Excel Options dialog box.
Click the Formulas tab and check the Enable iterative calculation option.
You can set the Maximum Iterations to 500 and the Maximum Change to 0.0001.
Hit the OK button.

Rearrange the Colebrook equation as shown below and enter it into the D8 cell:

=1/(-2*LOG(D4/(D5*3.7) + 2.51/(D6*SQRT(D8+1E-300))))^2

The D4, D5, D6, and D8 cells represent the Pipe Roughness, Pipe Diameter, Reynolds Number, and Friction Factor, respectively.

Note: When Excel begins iterating, it sets the value in the D8 cell to zero. This would return #DIV/0! Error. To solve this, we’ve added a small number (1E-300) which doesn’t affect the accuracy of the result.

Move to the D9 cell and enter the cell reference for the D8 cell.

=D8

Excel performs the iterations and returns the result as shown in the image below.

Method 3 – Applying VBA to Solve the Colebrook Equation

Steps

Navigate to the Developer tab and click on Visual Basic.

This opens the Visual Basic Editor in a new window.
Go to the Insert tab and select Module.

Copy the code from below and paste it into the window.

Sub Solve_Colebrook()

Static Computing As Boolean
If Round(Range("E9").Value, 3) <> 0 And Not Computing Then
    Computing = True
    Range("B9").Value = 0.01
    Range("E9").GoalSeek goal:=0, ChangingCell:=Range("B9")
    Computing = False
End If

End Sub

⚡ Code Breakdown:

The If statement checks if the value in the E9 cell is not equal to zero and not equal to Computing.
The code sets the value in the B9 cell to 0.01 using the Range object.
The code applies the Goal Seek method to determine the value of the B9 cell (Friction factor) which gives zero in the E9 (RHS – LHS) cell.