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.

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

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.

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

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

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

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.

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

__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.

📌 ** Steps:**Â

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

- Next, click the
**Options**button at the bottom of the window.

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**.

- 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.Â- Following this, move to the
**D9**cell and enter the cell reference for the**D8**cell.

`=D8`

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

__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.

This opens the **Visual Basic Editor** in a new window.

#### 📌 __Step-02:__ Insert VBA Code

- Secondly, go to the
**Insert**tab >> select**Module**.

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
```

**⚡**** 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.

**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.

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

**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.

**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

- How to Solve for x in ExcelÂ
- How to Solve an Equation for X When Y is Given in ExcelÂ
- How to Solve Algebraic Equations with Multiple Variables
- How to Solve System of Equations in Excel
- How to Solve Simultaneous Equations in Excel
- How to Solve Nonlinear Equations in Excel
- How to Solve Differential Equation in Excel
- How to Solve Exponential Equation in Excel
- How to Solve Cubic Equation in Excel
- How to Solve Quadratic Equation in Excel VBA

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