Using the Goal Seek Command

By clicking the What-If Analysis button on the Data tab and then clicking Goal Seek, you can compute an unknown value that produces the result you want. For example, suppose you want to know the maximum 30-year mortgage you can afford if the interest rate is 6. 5 percent and if you must limit your monthly payments to $2, 000. To use the Goal Seek command to answer this question, first set up the problem using trial values. For example, in the mortgage problem shown in Figure 18-19, a $500, 000 mortgage would require monthly payments in excess of the $2, 000 target.

Figure 18-19: Use the Goal Seek command to find the maximum mortgage you can borrow if you want to keep your payments under a certain limit.

Here's how to perform goal seeking on this problem:

1. Select the formula cell-in this case, B4-to make it the active cell.

2. Click the What-If Analysis button on the Data tab, and click Goal Seek to display the Goal Seek dialog box shown in Figure 18-20.

3. Accept the value in the Set Cell box (make sure it specifies the cell containing the formula). In the To Value box, type the maximum value you want as the result of the formula-in this case, -2000. (You type a negative number because the payment represents cash spent rather than received.)

4. In the By Changing Cell box, type the reference or click the cell on the worksheet whose value is unknown-in this case, cell B1 (the Principal value). Alternatively, if you have assigned a name, such as Principal, to cell B1, you can type that name in the By Changing Cell box.

5. Click OK, or press Enter. Excel displays the Goal Seek Status dialog box shown in Figure 18-21. The answer you are looking for appears in the cell specified in the By Changing Cell box.

6. To type this value on the worksheet, click OK in the Goal Seek Status dialog box. To restore the value that was in B1 before you used the Goal Seek command, click Cancel.

Figure 18-20: To use goal seeking, complete the Goal Seek dialog box.

Figure 18-21: The Goal Seek Status dialog box informs you when Excel finds a solution.

Excel uses an iterative technique to perform goal seeking. It tries one value after another for the variable cell specified in the By Changing Cell box until it arrives at the solution you requested. Excel solves the mortgage problem we just looked at quickly. Other problems might take longer, and some might not be solvable at all.

While Excel is working on a complex goal-seeking problem, you can click Pause in the Goal Seek Status dialog box to interrupt the calculation, and then click Step to display the result of each successive iteration. A Continue button appears in the dialog box if you are solving a problem in this stepwise fashion. To resume full-speed goal seeking, click Continue.

Precision and Multiple Solutions

Suppose you enter the formula =A2^2 in cell A1 of a blank worksheet and then use the Goal Seek command to find the value of A2 that will make A1 equal to 4. (In other words, in the Goal Seek dialog box, type $A$1 in the Set Cell box, 4 in the To Value box, and $A$2 in the By Changing Cell box.) The result, shown in Figure 18-22, might be surprising. Excel seems to be telling you that the closest value it can find to the square root of 4 is 2.000023.

Figure 18-22: The Goal Seek command returns this result when asked to find the square root of 4.

By default, the Goal Seek command stops when it has either performed 100 iterations (trial solutions) or found an answer that comes to within 0.001 of your specified target value. If you need greater precision than this, you can change the default limits by clicking the Microsoft Office Button, clicking Excel Options, selecting the Formulas category, and then changing the Maximum Iterations value to a number greater than 100 or setting the Maximum Change value to a number less than 0.001, or both.

For more information about worksheet calculation options, see Chapter 12, "Building Formulas."

This example illustrates another factor you should be aware of when you use the Goal Seek command. The Goal Seek command finds only one solution, even though your problem might have several. In this case, the value 4 has two square roots: +2 and –2. In situations like this, the Goal Seek command gives you the solution with the same sign as the starting value. For instance, if you start with a value of –1 in cell A2, the Goal Seek command reports the solution as –1.999917, instead of +2.000023.