Microsoft Office Tutorials and References

In Depth Information

**Other Solver options**

Note that when Excel converts numbers to integers, the program effectively rounds down;

the decimal portion of the number is truncated. The integer solution shows that by placing

53 ads in Pub4, you can buy an additional ad in Pub5. For a very small increase in budget,

you can reach an additional two million readers.

Determine whether you need integer constraints

Adding integer constraints to a Solver problem can geometrically increase the

problem’s complexity, resulting in possibly unacceptable delays. The example discussed

in this chapter is relatively simple and does not take an inordinate amount of time to

solve, but a more complex problem with integer constraints might pose more of a

challenge for the Solver. The Solver can solve certain problems only by using integer

constraints. In particular, integer solutions are useful for problems in which variables can

assume only two values, such as 1 or 0 (yes or no), but if you’re looking for “yes or no”

results, you can also use the bin (binary) option in the list in the middle of the Change

Constraint dialog box.

Other Solver options

In the Solver Parameters dialog box, the Select A Solving Method drop-down list offers

three options: GRG Nonlinear (the default), Simplex LP, and Evolutionary. Simply put, here

are the differences:

GRG Nonlinear
The default solving method is optimized for “smooth nonlinear”

problems involving points along a curved (but smooth) line.

●

Simplex LP
This method works best for linear problems that can be defined as

points along a straight line.

●

Evolutionary
This option works best when your model involves “non-smooth,”

or random, discontinuous elements that would not plot along either a straight or

curved line.

●

Click the Options button in the Solver Parameters dialog box to display the Options dialog

box shown in Figure 18-28, which contains many additional settings. It’s best to leave the

options on the GRG Nonlinear and Evolutionary tabs at their default settings—unless you

understand linear optimization techniques. The following options on the All Methods tab

warrant some explanation:

The Constraint Precision setting determines how closely you want values in the

constraint cells to match your constraints. The closer this setting is to the value 1, the

●