XL: Method Used by Goal Seek to Find a Solution

MORE INFORMATION

In the Goal Seek dialog box, you specify the cell containing the formula (Set Cell), the result you want the formula to return (To Value) and one of the cells that the formula is dependent on (By Changing Cell). 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.