Excel provides us with a quick and easy way to perform simple numerical iteration in Excel. The tool is called Goal Seek, and it first glance it may seem like a simple tool, but applying it properly can allow you to do some powerful things in Excel.
You can find the Goal Seek tool in the What-If Analysis section of the Data tab:
Elevate Your Engineering With Excel
Advance in Excel with engineering-focused training that equips you with the skills to streamline projects and accelerate your career.
Iteration in Excel with Goal Seek
The most obvious way to use Goal Seek is just the way Microsoft intended it: to find an input value that yields a target result. This allows us to “play around” with our models and try different options without having to rearrange the equations or the worksheet.
- Open Goal Seek
- Select the cell that you want to achieve a specific target with in the “set cell” input.
- Enter the target value you want to achieve (“to value”).
- Provide the cell that you want to change to achieve the result, or “by changing cell”.
- Select OK
Goal seek iterates the input to achieve the target.
Finding the Root of an Equation with Excel Iteration
One specific application of finding a target result is to use Goal Seek to find the root (or roots) of an equation. In this case, the target value is simply 0.
Iterative Calculations in Excel
We can also use Goal Seek to perform iterative calculations where it’s not possible to rearrange an equation to solve for a single variable. For instance, if your engineering model contains a table of data, you might be using linear interpolation to bring that data into a calculation. In this case, it may not be possible to rearrange the equation, so use Goal Seek instead to find the result.
Solving Implicit Equations in Excel
A specific application of using Goal Seek for iterative calculations in engineering is using the tool to solve implicit equations. An implicit equation is one in which a variable appears on both sides of the equal sign, with no way to simplify it further. A common example in engineering is the Colebrook-White equation, which describes the Darcy-Weisbach friction factor, f, in a pipe based on the roughness height, hydraulic diameter, and Reynolds number.
We can solve for f by creating a cell for that input value, and creating formulas for the left and right sides of the equation in separate cells. The correct solution for f is the value which makes the left and right sides equal. So we can subtract the left side of the equation from the right side in another cell and target a value of 0 (or the root of the equation) with Goal Seek. Just to keep Excel happy, we’ll square the result so that the target cell always returns a positive value.
Goal Seek vs. Enable Iterative Calculation
You can also choose “enable iterative calculations” in Excel Options as shown in the image below. This enables Excel to handle circular references, or cell formulas that refer to their own result as an input to the cell formula. When choosing this method, the maximum number of iterations and the maximum change (accuracy) need to be set appropriately to avoid a very long iterative loop.
Goal Seek is better than this option because it performs the iteration in a more controlled manner.
Word of Caution
There is a word of caution when working with Goal Seek, however. If you make a change to your worksheet that affects the result, you’ll need to manually re-run Goal Seek. If there’s a risk that you might forget to rerun it, you can also write a VBA subroutine to run Goal Seek anytime there is a change on a worksheet. Alternatively, you could also enable iterative calculations as discussed above.
