XL: Method Used by Goal Seek to Find a Solution

 Last reviewed: February 2, 1998
Article ID: Q100782
The information in this article applies to:
  • Microsoft Excel for Windows, versions 3.0, 4.0, 4.0a, 5.0, 5.0c
  • Microsoft Excel for the Macintosh, versions 3.0, 4.0, 5.0, 5.0a
  • Microsoft Excel for OS/2, version 3.0
  • Microsoft Excel for Windows 95, versions 7.0, 7.0a
  • Microsoft Excel 97 for Windows
  • Microsoft Excel 98 Macintosh Edition

SUMMARY

The Microsoft Excel Goal Seek command (on the Formula menu in Excel version 3.0 and 4.0, or on the Tools menu in Exel 5.0 and later) is useful when you know the result you want a formula to return, but do not know the input values the formula needs to reach that result. When you use goal seeking, Microsoft Excel varies the value in a specified cell until the formula that is dependent on that cell returns a specified result. The following section describes the method used by the Goal Seek command to find a result.

MORE INFORMATION

In the Goal Seek dialog box, you specify the Set Cell (cell containing the formula), To Value (the result you want the formula to return), and the By Changing Cell (one of the cells that the formula is dependent on). Both of the cell specifications must be a single cell reference or name. The To Value must be a number.

The Goal Seek command uses a simple linear search beginning with guesses on the positive or negative side of the value in the source cell (By Changing Cell). Excel uses the initial guesses and recalculates the formula. Whichever guess brings the formula result closer to the targeted result (To Value) is the direction (positive or negative) in which Goal Seek heads. If neither direction appears to approach the target value, Goal Seek makes additional guesses that are further away from the source cell. After the direction is determined, Goal Seek uses an iterative process in which the source cell is incremented or decremented at varying rates until the target value is reached.

The Goal Seek command uses a simple algorithm and, as a result, may have problems converging on a solution if the function is not linear. Because the iteration process begins with guesses around the source cell, if Goal Seek is having problems converging on a solution, changing the value in the source cell may help. Alternatively, you can use Microsoft Excel Solver.

REFERENCES

"User's Guide," version 5.0, pages 540-541 "User's Guide 2," version 4.0, pages 78-79

Additional query words: 3.0 3.00 4.0 4.00 4.0a 4.00a 5.0 5.00 5.00a 5.00c
7.00 7.00a 97 98 XL98 XL97 XL7 XL5 XL4
Version : WINDOWS:3.0,4.0,5.0,5.0c,7.0,7.0a,97; MACINTOSH:3.0,4.0,5.0,5.0a,98
Platform : MACINTOSH OS/2 WINDOWS
Issue type : kbinfo

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 2, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.