In addition to solving nonlinear equations like the Colebrook equation graphically, you can also solve them numerically using a feature called Goal Seek. Our worksheet is set up to do just that. This spreadsheet is set up to use a guess for the value of f as an input. You’ll use the “Outputs” table to calculate the left and right side of the Colebrook equation.
After you set up those calculations, it will be easy to use Excel to iterate through guesses to determine the value of f that causes the left side of the equation to equal the right side.
Enter in a value of 0.03 for f as an initial guess. As in the previous section’s worksheet, the cells containing variables already have names assigned, so you can use those names in formulas.
In the cell for the left side of the Colebrook equation, enter the formula:
=1/SQRT(f)
The formula for the right side of the equation will be:
=-2*LOG10(eps/(3.7*DH)+2.51/(Re*SQRT(f)))
Find the difference between the two with a simple subtraction:
=C10-C11
This cell is formatted for scientific notation because we’ll be looking at relatively small values.
You could keep changing the guess value to find a good guess for f, but Excel has a built-in tool to do those iterations for you. This tool is called Goal Seek in the Data tab under What-If Analysis. This is a useful tool, so you may want to add it to your Quick Access Toolbar.
We’re trying to find the value of f (cell G5) that will make the difference between the left and right sides of the equation (cell C12) equal to zero.
Therefore, we’ll use C12 as the set cell, because we’re trying to set cell C12 to 0 (enter this in the second box). The value to change is cell G5.
Any time you use the Goal Seek tool, the set cell must contain a formula, and the change cell must contain a value.
Click OK and Excel will iterate through guesses until it gets the set cell within some tolerance of the target value.
This gives almost the same result as the graphical method in the last section, but much faster and with slightly more accuracy. One thing to remember is if there are changes to the inputs of the set cell, then Goal Seek will need to be run again to find the friction factor. It doesn’t update automatically. For example, if you change the Reynolds number to 16,000, you’ll need to re-run Goal Seek to find the new friction factor.
