Solve equations by iteration or Goal Seek

Sometimes an equation is not solvable analytically. One example of this is the Colebrook equation for finding the friction coefficient λ. Two methods are shown below on how to solve it either by "hand" or in a spreadsheet e.g. Excel or LibreOffice Calc. The methods described here is not by any means the only ways of finding the solution but are the ones that I typically resort to. The Colebrook equation where &lamda; shall be determined:

\begin{align} \frac{1}{\sqrt{\lambda _0}}= 1.74-0.87\ln \left( \frac{2k_s}{D_i}+\frac{18.7}{Re\sqrt{\lambda _0 }}\right) \end{align}

Following parameters are used in the example. Reynolds number=100000, k_s=0.045mm and D_i=50mm.

Finding the solution manually

Finding the solution by hand or manually may seem daunting at first but it can be done relatively easy and rather quickly.

Step 1 is to estimate a first guess. In this example Blasius equation for smooth pipes can be used to calculate the first value for our iterations. The iteration process will converge faster with a good first estimate.

\begin{align} \lambda_0 = 0.316Re^{-0.25} = 0.01777 \end{align}

Step 2 Evaluate the two sides of the equation. They will for simplicity be called left and right based on the location relative to the equal sign.

\begin{align} left & = \frac{1}{\sqrt{\lambda _0 }}\\ right & = 1.74-0.87\ln \left( \frac{2k_s}{D_i}+\frac{18.7}{Re\sqrt{\lambda _0 }}\right) \end{align}

Step 3 Evaluate the difference between the right and left. The iteration process is finished ones the difference is small.

Step 4 Calculate new λ value based on the result calculated on the right side.

The new λ calculated for the first iteration.

\begin{align} \frac{1}{\sqrt{\lambda_1 }} & = right \\ \lambda _1 & = \frac{1}{right} = \frac{1}{6.7370429} = 0.0220324 \end{align}

Step 5 Use the new λ value in step 2 and repeat until the difference is small enough

The table show the results from the iteration process. As seen the result converge very quickly.

Iteration number	λ	Left	Right	Difference
0	0.0177700	7.5016441	6.7370429	0.7646012
1	0.0220324	6.7370429	6.7767753	-0.0397323
2	0.0217748	6.7767753	6.7746653	0.0021100
3	0.0217883	6.7746653	6.7747772	-0.0001119

Using LibreOffice Calc or Microsoft Excel to find the solution

Both LibreOffice Calc and Microsoft Excel have Goal Seek which can be used to solve the equation rather easily. The way to use the Goal Seek function is identical in Calc and Excel. As a side note LibreOffice is highly recommended. It is a very capable Open Source replacement for Microsoft Office.

Step 1 is to set up the equation. The goal is similar to the manual approach to ensure equality between the two terms by ensuring the the difference between the two is close to zero. This is ensured by collecting all of the terms on one side and having a zero on the other.

\begin{align} \frac{1}{\sqrt{\lambda }}= 1.74-0.87\ln \left( \frac{2k_s}{D_i}+\frac{18.7}{Re\sqrt{\lambda _0 }}\right) \end{align}

Step 2 Add the equation to the spreadsheet. It is important that the value for λ is placed in its own cell and that an initial guess is entered.

Equation and input added to the spreadsheet

Step 3 Use Goal Seek to find the result. The Goal Seek function is in Calc located under Tools > Goal Seek and in Excel under What if. The first input into the Goal Seek is the formula cell. This is the cell were the equation or formula is. It is in this example B9. The Target Value shall be set to zero as the purpose is it ensure equality between the two terms in our equation. The final input is the Variable Cell. This is the variable in that Calc or Excel shall change in order to obtain the target value. It is in this example λ i.e. cell B8.

The result found by the Goal Seek function is λ=0.021788 which is the same as the result found using the manual approach.

Result after using Goal Seek and transferring the result to cell B8