How to Use the GRG Nonlinear Tab in Excel’s Solver Options Dialog Box - dummies

How to Use the GRG Nonlinear Tab in Excel’s Solver Options Dialog Box

By Stephen L. Nelson, E. C. Nelson

The GRG Nonlinear tab in Excel provides buttons and boxes for managing the way Solver works when you’re using the GRG Nonlinear solving method. This may be something you will never need, but it’s good to have the option.


Saying when

Have you ever been to a restaurant where your server wanders around at some point in the meal with a huge peppermill asking whether you want black pepper on your salad? If you have, you know that part of the ritual is that at some point, you tell the server when she has ground enough pepper for your green salad.

The Convergence text box provided on the GRG Nonlinear tab of the Solver Options dialog box works in roughly the same way. If you are using the GRG Nonlinear Solving method, you use the Convergence box to tell Excel when it should stop looking for a better solution. The Convergence text box accepts any value between 0 and 1.

When the change in the objective function formula result is less than the value shown in the convergence text box, Excel figures that things are getting close enough, so additional iterations aren’t necessary.

Oh, and something you should know: With larger convergence values, Excel reaches a reasonable solution more quickly and with less work. And with smaller or very precise convergence values, Excel works harder and takes more time.

Forward versus central derivatives

Select from the two Derivatives radio buttons — Forward and Central — to tell Excel how to estimate partial derivatives when it’s working with the objective function and constraint formulas. In most cases, everything works just fine if Excel uses forward derivatives. But, in some cases, forward derivatives don’t work. And in this situation, you might be able to specify that Excel use central derivatives.

Using central derivatives requires much more work of Excel, but some highly constrained problems can more easily and more practically be solved using central derivatives.

Working with the Multistart settings

If you check the Multistart box on the GRG Nonlinear tab, you tell Solver to, in effect, solve the optimization problem by beginning from several different starting points. The Population Size box lets you specify the number of starting points. The Random Seed box lets you provide an integer to be used as the seed for a random number generator that produces the actual starting points.

Finally, you can check and uncheck the Require Bounds On Variables box to specify that this whole multistart craziness only occurs when you’ve had the decency to define both upper and lower limits for the variables.