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.