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.

[Note: 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 easily solve engineering challenges in Excel. Click here to get started.]

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

[Note: 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 easily solve engineering challenges in Excel. Click here to get started.]

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.

[Note: 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 easily solve engineering challenges in Excel. Click here to get started.]