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

Get FREE Advanced Excel Exercises with Solutions!

Excel is a popular and useful tool for analyzing data and solving complex equations. Now, one such case is the Colebrook equation which has to be solved numerically and this is where Excel becomes a very handy tool to perform such calculations effortlessly. Keeping this in mind, this article demonstrates 3 effective ways to solve the Colebrook equation in Excel.


What Is Colebrook Equation?

In simple terms, the Colebrook equation shows the relationship between the Reynolds number, Pipe Roughness, and Diameter with the Friction factor of the pipe.

The variables of the Colebrook equation are as follows.

How to Solve Colebrook Equation in Excel

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

How to Solve Colebrook Equation in Excel: 3 Simple Ways

By now you’ve probably figured out that the Colebrook is an implicit equation because the friction factor term is present on both sides of the expression. Hence, this equation can be solved either numerically or through trial and error.

Now, let’s consider the dataset shown in the B4:D12 cells. Here, we have the distribution of the Pipe Roughness and the Reynolds Number for a fixed Pipe Diameter. So, without further delay, let’s see each method individually.

Dataset

Here, we have used Microsoft Excel 365 version, you may use any other version according to your convenience.


Method-1: Using Goal Seek Tool to Solve Colebrook Equation

Let’s start with the most popular method of solving the Colebrook equation in Excel. Yes, you’ve guessed it correctly! We’ll utilize Excel’s Goal Seek tool to solve the equation. So, just follow the steps below.

📌 Steps: 

  • At the very beginning, enter the left side of the Colebrook equation in the C9 cell.

=1/SQRT(B9)

Here, the B9 cell refers to the Friction Factor and is also the number argument of the SQRT function.

How to Solve Colebrook Equation in Excel Using Goal Seek Tool

  • Next, move to the D9 cell and type in the right side of the Colebrook expression. For your ease of use, you may copy the expression below.

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

Here, 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.

How to Solve Colebrook Equation in Excel Using Goal Seek Tool

  • Then, navigate to the E9 cell and calculate the difference between the right side and the left side.

=D9-C9

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

How to Solve Colebrook Equation in Excel Using Goal Seek Tool

  • In turn, go to the Data tab >> click the What-If Analysis drop-down >> select the Goal Seek option.

Using Goal Seek Tool

Subsequently, this opens the Goal Seek wizard.

  • Next, in the To value field type in 0 while for the By changing cell option select the B9 cell.

Using Goal Seek Tool

Finally, your output should look like the picture given below.

How to Solve Colebrook Equation in Excel Using Goal Seek Tool


Method-2: Utilizing Worksheet Iteration to Solve Colebrook Equation

For those of you who want to learn about more techniques, Excel has a nifty trick up its sleeve! Simply put, you can use the Worksheet Iteration feature of Excel to solve the Colebrook equation. In order to do this, we’ll rearrange the Colebrook equation as shown below. So, let’s see it in action.

How to Solve Colebrook Equation in Excel

📌 Steps: 

  • To begin with, click the File tab at the top left corner.

How to Solve Colebrook Equation in Excel Using Worksheet Iteration

  • Next, click the Options button at the bottom of the window.

How to Solve Colebrook Equation in Excel Using Worksheet Iteration

This opens the Excel Options dialog box.

  • Now, click the Formulas option >> insert a checkmark on the Enable iterative calculation option >> hit the OK button.

Here, you can set the Maximum Iterations to 500 and the Maximum Change to 0.0001.

Using Excel Options

  • Secondly, 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

In this expression, 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 so to solve this we’ve added a small number (1E-300) which doesn’t affect the accuracy of the result. 

How to Solve Colebrook Equation in Excel Using Worksheet Iteration

  • Following this, move to the D9 cell and enter the cell reference for the D8 cell.

=D8

Using Worksheet Iteration

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

How to Solve Colebrook Equation in Excel Using Worksheet Iteration


Method-3: Applying VBA to Solve Colebrook Equation

Thus far, what we’ve done is nice, but there is a major problem, i.e. each time we change the Friction Factor we’ll have to re-run the Goal Seek tool. However, we can automate this repetitive task using VBA. Now, allow me to demonstrate the process in the steps below.

📌 Step-01: Open Visual Basic Editor

  • Firstly, navigate to the Developer tab >> click the Visual Basic button.

How to Solve Colebrook Equation in Excel Applying VBA Code

This opens the Visual Basic Editor in a new window.

📌 Step-02: Insert VBA Code

  • Secondly, go to the Insert tab >> select Module.

Applying VBA Code

For your ease of reference, you can copy the code from here and paste it into the window as shown below.

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

How to Solve Colebrook Equation in Excel Applying VBA Code

âš¡ Code Breakdown:

Now, I will explain the VBA code for solving the Colebrook equation. Here, the code is divided into 2 sections.

  • In the first portion, the sub-routine is given a name, here it is Solve_Colebrook().
  • Next, define the variable Computing and assign Boolean data type.
  • In the second potion, use the If statement to check if the value in the E9 cell is not equal to zero and not equal to Computing.
  • Now, set the value in the B9 cell to 0.01 using the Range object.
  • Finally, apply the Goal Seek method to determine the value of the B9 cell (Friction factor) which gives zero in the E9 (RHS – LHS) cell.

Code Explanation

Read More: How to Solve 2 Equations with 2 Unknowns in Excel


📌 Step-03: Running VBA Code

  • Now, close the VBA window >> click the Macros button.

This opens the Macros dialog box.

  • Following this, click the Run button.

How to Solve Colebrook Equation in Excel Applying VBA Code

Eventually, the results should look like the screenshot given below.

How to Solve Colebrook Equation in Excel Applying VBA Code

Read More: How to Solve Polynomial Equation in Excel


Practice Section

Here, we have provided a Practice section on the right side of each sheet, so you can practice yourself. Please make sure to do it by yourself.

Practice Section


Download Practice Workbook

You can download the practice workbook from the link below.


Conclusion

I hope all the methods on how to solve Colebrook equation in Excel will now prompt you to apply them in your Excel spreadsheets more effectively. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.


Related Articles


<< Go Back to Excel Solve Equation | Excel Solver Examples | Solver in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Eshrak Kader
Eshrak Kader

Eshrak Kader is a dedicated professional with a BSc. Degree in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. He boasts a rich background in Microsoft Office Suite, and over the past year, he has authored over 120 insightful articles for the ExcelDemy. Currently, Eshrak leads a team of 5 in the ExcelDemy Video project and enjoys problem-solving and making videos on Excel. Eshrak’s passion for continuous learning underscores his commitment to excellence in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo