Goal Seek

This allows you to calculate an unknown value in a given formula.
The Goal Seek feature is only useful for problems that involve finding a single variable.
You can build complex worksheet models and then employ the Goal Seek feature to derive different solutions to your models.
The Goal Seek is useful for problems that involve an exact target value that depends on a single variable.
This uses a single variable to find a desired result.

Goal Seek Dialog Box

You can select (Tools > Goal Seek) to display the Goal Seek dialog box.

microsoft excel docs

Set cell - This specifies the cell containing the formula that is used to obtain the final result.
To value - This is the value which you are trying to reach. Excel will only accept values and not a cell reference.
By changing cell - This specifes the cell of the variable you want to change, in order to obtain the final result.

Advanced "What If" Analysis

Some problems can be very complex and some cannot be solved at all.
If a calculation takes a long time to calculate then you may want to pause it or even step through it to see the values that are being tried.
To resume as normal press the Continue button.
It is important to remember that Excel will stop trying to find a solution after 100 iterations or when it gets to within 0.001 of the specified goal.

Getting More Precision

It is possible to change the settings if you need more precision by selecting (Tools > Options)(Calculation tab).
The Iteration value is workbook specific however Excel only examines the Iteration check box the first time a workbook is opened.
The settings are not changed for any subsequent workbooks that are opened.

microsoft excel docs

If the Iteration check box is not ticked and you open a workbook containing a circular reference you will get an error message displayed.
Please refer to the Circular References page for more details.
You can change the maximum number of iterations and the accuracy of the change.
You can prevent this by explicitly setting the Iteration checkbox value to ticked in code


For multi-variant problem solving in Excel you should use the Solver Add-in. This is a special Excel add-in that can be used for more complex "what if" calculations and is discussed in more detail later in this section.

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext