New Features of the Solver in Excel 97

Frontline Systems has upgraded the Solver in Excel 97, which Microsoft is now shipping as part of the new Office 97. The improved Solver borrows some of the ease-of-use features of our Premium Solver for Microsoft Excel (though it has none of the capacity or performance advantages of the Premium Solver), and it also makes use of some new features of Excel 97 itself. And we've preserved compatibility with your existing Solver models, macros, and VBA code utilizing the Solver.

Here are some of the highlights of the new Solver in Excel 97:

• Up to 10 times faster on some existing models!
• Easier selection using the new Range Selector button
• One-step entry of binary integer constraints
• Assume Non-Negative check box in Solver Options
• Control of the nonlinear Solver's Convergence tolerance
• Use Automatic Scaling works for linear models
• Improved linearity test for Assume Linear Model

Speed Improvements

The Solver is faster thanks to improvements in recalculation and data handling in Excel 97 itself, as compared to Excel 95 (aka Excel 7.0) and earlier releases. Improvements vary greatly from one model to another, but solution times for almost all models using the nonlinear Solver are at least 20% faster, and some models are as much as 10 times faster!

If you are solving a linear model with the Assume Linear Model box checked in the standard Excel 97 Solver, you are unlikely to see much improvement. However, dramatic speedups are possible for linear models if you are using our Premium Solver or Large-Scale LP Solver for Excel: We've seen speed gains of as much as 15 times, on a wide variety of models! The speed gains are most significant for models which are NOT in the form required for "fast problem setup" -- we expect that most large linear models of this type will enjoy order-of-magnitude speedups. You can cut solution time even further -- by as much as another factor of 5 -- if you convert such models into "fast problem setup" form.

Solver Parameters Dialog

The buttons to the right of the Set Target Cell and By Changing Cells edit boxes activate Excel 97's new Range Selector, which allows you to select cells without having a dialog box in the way. These buttons also appear in the Add Constraint and Change Constraint dialogs. The Relation dropdown list in the Add and Change Constraint dialogs now includes a fifth choice bin, which defines a binary integer constraint on a range of decision variables. A binary integer constraint such as A1:A10 = binary is equivalent to three "regular" constraints: A1:A10 = integer, A1:A10 >= 0 and A1:A10 <= 1. This forces each variable to be either 0 or 1 at the optimal solution. Binary integer variables are often used to represent yes/no decisions, such as whether to open a new plant or set up a machine for a new job.

Solver Options Dialog

The Solver Options dialog includes a new Assume Non-Negative check box and a new Convergence edit box. The Assume Non-Negative check box allows you to place implicit lower bounds of zero on decision variables which do not have explicit lower bounds in the Constraint list box. This is a real convenience for problems in which most or all of the variables represent quantities which cannot be negative. The Convergence edit box allows you to control the nonlinear GRG Solver's test for slow improvement in the objective, as explained below.

Improved Robustness

Although the underlying algorithmic methods and performance of the Solver in Excel 97 are the same as in previous versions, we've improved the Solver's robustness on certain common problems.

In previous versions, the Use Automatic Scaling option was effective only for nonlinear models, and the linearity test (which gave rise to the message "The conditions for Assume Linear Model are not satisfied") could be "fooled" by a poorly scaled linear model. In Excel 97, the Use Automatic Scaling option is effective for linear and nonlinear models, and the linearity test has been made more efficient and less sensitive to scaling (regardless of whether Use Automatic Scaling is checked).

Users of the nonlinear GRG Solver in previous versions sometimes found that the Solver stopped prematurely with the message "Solver has converged to the current solution." This message appeared when the change in the objective (Set Cell) for the past few interations was consistently less than a fixed tolerance. In Excel 97, you have control of this tolerance through the new Convergence edit box in the Solver Options dialog -- so it can be set to a smaller value when necessary.

Copyright © 1996 Frontline Systems Inc.
Last modified: January 19, 1997