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.

# Iterative Calculations

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.