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:
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.
Finding the Input Value to Achieve a Target Result
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 engineering 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
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.
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
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 with Goal Seek. Just to keep Excel happy, we’ll square the result so that the target cell always returns a positive value.
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.