How to Use the All Methods Tab in Excel’s Solver Options Dialog Box

By Stephen L. Nelson, E. C. Nelson

The All Methods tab’s option in Excel provides boxes you can use for any solving method. You may never need to use this option, but it’s there if you need to do some fine-tuning.


Using automatic scaling

You can select the Use Automatic Scaling check box when you’re working with variables that greatly differ in magnitude. For example, if you’re working with interest rates and multimillion dollar account balances, you might want to use the automatic scaling option to tell Excel, “Hey dude, the Solver variable values greatly differ in magnitude, so you ought to automatically scale these babies.”

Showing iteration results

If you don’t have anything better to do, select the Show Iteration Results check box. When you do, Excel stops after it calculates each objective function using a new set of Solver variable values and shows you the intermediate calculation results. Most people won’t and shouldn’t care about seeing intermediate calculation results. In some cases, you might want to see how Solver is working toward the objective function optimal result.

Solving with integer constraints

Using integer constraints may complicate your optimization modeling, so Excel’s Solver provides some tweaks you can make to models that “technically” should return integer values. For example, you can check the Ignore Integer Constraints box to tell Excel you want to try solving the problem (just for giggles) without the integer constraints.

Another integer-constraint-related tweak: The Integer Optimality % box lets you specify the maximum percentage difference that you’ll accept between the best solution that uses integer constraints and the best solution that ignores integer constraints.

Setting a limit on Solver

Use the Max Time and Iterations text boxes to limit the amount of work that Solver does to solve an optimization modeling problem. Now, this simple example doesn’t take much time to solve. But real-life problems are often much more complicated. A real-life problem might have many more Solver variables to deal with.

The constraints might be more numerous and more complicated. And you might complicate optimization by doing things such as working with lots of integer or binary constraints.

When you work with large, complex, real-life problems, the time that Solver takes to optimize might become very lengthy. In these cases, you can set a maximum time limit that Solver takes by using the Max Time text box. You can also set a maximum number of iterations that Solver makes by using the Iterations text box.

You can stop the Solver’s calculations by pressing Esc.

If you’re using the Evolutionary solving method in a situation with integer constraints, you can also specify the maximum number of subproblems you want the Solver to work on using the Max Subproblems box and then the maximum number of feasible integer solutions you want Solver to produce using the Max Feasible Solutions box.