Microsoft Office Tutorials and References

In Depth Information

**Goal Seeking**

Figure 2-9:
The Goal Seek dialog box.

Almost immediately, Excel announces that it has found the solution and displays the Goal Seek

status box. This box tells you the target value and what Excel came up with. In this case, Excel

found an exact value. The worksheet now displays the found value in cell C4 ($250,187). As a

result of this value, the monthly payment amount is $1,200. Now, you have two options:

h
Click OK to replace the original value with the found value.

h
Click Cancel to restore your worksheet to its original form before you chose Goal Seek.

More about goal seeking

If you think about it, you may realize that Excel can’t always find a value that produces the result

that you’re looking for — sometimes a solution doesn’t exist. In such a case, the Goal Seek Status

box informs you of that fact. Other times, however, Excel may report that it can’t find a solution

even though you believe one exists. In this case, you can adjust the current value of the changing

cell to a value closer to the solution, and then reissue the command. If that fails, double-check

your logic and make sure that the formula cell does indeed depend on the specified changing cell.

Like all computer programs, Excel has limited precision. To demonstrate this, enter
=A1^2
into

cell A2. Then, choose Data

Goal Seek to find the value in cell A1

that causes the formula to return 16. Excel returns a value of 4.00002269 — close to the square

root of 16, but certainly not exact. You can adjust the precision in the Calculation section of the

Formulas tab in the Excel Options dialog box (make the Maximum change value smaller).

Data Tools

What-If Analysis

➜

➜

➜

In some cases, multiple values of the input cell produce the same desired result. For example, the

formula =A1^2 returns 16 if cell A1 contains either –4 or +4. If you use goal seeking when two

solutions exist, Excel gives you the solution that is nearest to the current value in the cell.

Perhaps the main limitation of the Goal Seek command is that it can find the value for only one

input cell. For example, it can’t tell you what purchase price
and
what down payment percent

result in a particular monthly payment. If you want to change more than one variable at a time,

use the Solver add-in.