Microsoft Office Tutorials and References

In Depth Information

Figure 2-10:
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 work-

sheet now displays the found value in cell C4 ($355,974). As a result of this value, the monthly payment

amount is $1,800. Now, you have two options:

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

• 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
⇒
Data Tools
⇒
What-If Analysis
⇒
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).

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.