Unfortunately, the equation is implicit (meaning that the friction factor, f, appears on both sides of the equation with no way to further simplify).
In order to find the friction factor, we need to solve the implicit equation using an iterative scheme. For this, we will use the Excel Goal Seek tool.
The Colebrook Equation
There are four variables in the equation:
- Darcy-Weisbach Friction Factor, f
- Roughness height, ε
- Hydraulic diameter, Dh
- Reynolds number, Re
We will enter everything but f into the “Inputs” section of the spreadsheet.
In the “Outputs” section of the spreadsheet we will create rows for the friction factor, left and right sides of the Colebrook equation, and an “Objective”.
In order for the Excel Goal Seek tool to work, we need to enter an initial guess value for the friction factor. Any small positive value should work. I chose 0.01.
We will use Goal Seek to find a value of f that minimizes the difference between the left and right sides of the Colebrook-White equation. To do that we will calculate the left and right sides separately, then create an “Objective” function that we will attempt to minimize.
The left side of the equation is entered into cell C9 as follows (I used named ranges to make the equations clearer):
Similarly, the right side of the equation is entered into cell C10.
Iterative Solution using Excel Goal Seek
Before we can use Goal Seek to iterate to a solution for the friction factor, we need to define an Objective. We will set up Goal Seek to minimize this value (i.e. get it as close to zero as possible, within some tolerance).
The objective function must be set up such that it never returns a negative value, otherwise Excel will be sent into an unrecoverable tail spin.
To do this we will square the difference of the left and right hand sides of the equation.
Now we are ready to use the Goal Seek tool.
To access the Goal Seek tool, select Data>What-If Analysis>Goal Seek
Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to solve complex engineering challenges in Excel. Click here to get started.
This opens up the Goal Seek window.
In words, what we are trying to do is: “Set the OBJECTIVE to a value of 0 by changing the FRICTION FACTOR”.
The way we do that in Excel is shown below:
Clicking “OK” returns the following:
The Goal Seek tool managed to drive the objective function to a value of 5.53E-04 (an acceptable level for our purposes), resulting in a friction factor of 0.0375.
We can check the result against a published Moody diagram to verify the calculation.
I would estimate the value on the Moody diagram at about ~0.038-0.039, so our result is pretty close (i.e. within approximately 5% at most).
Automating Goal Seek with VBA
What we have so far is nice, but it has a significant flaw.
Every time we update an input, we have to rerun Goal Seek again.
That means we have to navigate to the tool in the Ribbon, select the Friction Factor and Objective cells, and enter “0” for the goal value.
What we would really like is to set this spreadsheet up so that the friction factor is updated automatically whenever an input value is changed.
Excel VBA can do this for us.
First we create a macro (in a new module) to run Goal Seek for us:
This macro does the following:
- Temporarily Disables Screen Updating
- Sets the value of “f” in Cell C8 to 0.01
- Runs Goal Seek if it is not currently running and the value of the objective cell round to 0 at 4 decimal places
- A Boolean (“isWorking”) that tells us whether or not Goal Seek is current running
- Enables Screen Updating
(Credit this site for the “isWorking” methodology.)
The macro automates setting up the Goal Seek, but it still has to be run manually.
Let’s add some code to this worksheet (in the VBA editor) so that the macro runs every time there is a change to the worksheet (e.g. someone changes an input cell).
And, finally, let’s test it out:
I changed the roughness height to 0.0002 m, and the friction factor updated automatically.
So that’s how you create a Colebook equation solver in Excel. This method can be extended to tables of data to calculate pressure drop vs. flow in a pipe with a few tweaks to the VBA code. You can also use the Goal Seek method shown here for other iterative calculations in Excel.