Page 425 - Excel for Scientists and Engineers: Numerical Methods

P. 425

402 EXCEL: NUMERICAL METHODS

GoalSeek
Returns the value of the independent variable x necessary to make the formula F(x)
have a specified value. The function uses the Newton-Raphson method.

Syntax
Goalseek( target-cellf changing-cellf objective-value, initiacvalue)
target-cell reference to a cell containing a formula F(x).
changing-cell cell reference corresponding to x, the variable to be changed.
objective-value the value to be returned by target-cell.
initiaLvalue optional argument specifying the initial estimate to be used in the
Newton-Raphson procedure
Remarks
The argument target-cell can be either a reference to a cell that contains a formula,
or a name. The formula must depend on changing-cell.
The argument changing-cell must be a reference to a cell.
The argument objective-value can be either a number, a reference to a cell
containing a number, a reference to a cell containing a formula, or a name.
The argument initiacvalue can be either a number, a reference to a cell containing
a number, a reference to a cell containing a formula, or a name. Use initiacvalue
for functions that have more than one value of x that satisfies the relationship F(x)
= objective-value, to control the value of x that is returned.
The workbook can be set to either RlC1- or Al-style.
Microsoft does not provide a goal-seeking function, only Goal Seek... in the Tools
menu. The Goal Seek... tool accepts only a fixed value as the objective, not a
reference to a cell. In contrast, the GoalSeek function allows the user to use a cell
reference as the objective. The cell can contain either a number or a formula. In
addition, Goal Seek... is a Sub procedure that must be run each time the formula
in the target cell or the objective value is changed. The GoalSeek function updates
automatically when either the formula or the objective is changed.
Note that, unlike Goal Seek. .., the custom function does not change the value of
changing-cell on which the cell containing target-cell depends. If you think that
there is a possibility that an incorrect value could be returned, you should enter a
copy of the formula in another cell, and make the formula depend on the value
returned by Goalseek, to confirm that the desired objective was found.

Limitations
None of the precedent cells of the argument expression may contain references to
the argument reference.
The function cannot handle implicit references; that is, a name or range reference
cannot be used for a range of values.

Example
If cell 85 contains the formula = A5"2+8*A5-10 and cell A5 contains the value 0, the
expression GoalSeek(B5,A5,210) returns 12.1327, a value of the independent variable
that makes the formula have the value 210. Since the formula describes a parabola,
there are two values of the independent variable that cause the formula to return the
value 210. The expression GoalSeek(B5,A5,H,-20) returns -18.1327, the other value.