If You Aren't Getting the Solution You Expect

[Dividing Line Image]

If the Solver stops with a solution (set of values for the decision variables or changing cells) which is different from what you expect, or what you believe is correct, follow the suggestions outlined on this page. You can usually narrow down the problem to one of a few possibilities.

Check the Solver Completion Message shown in the Solver Results dialog. Users sometimes call us about "wrong solutions", but they don't know which Solver Completion Message they received. This is crucial to diagnosing the problem.
Consider carefully the possibility that the solution found by the Solver is correct, and that your expectation is wrong. This may mean that what your model actually says is different from what you intended.
Check the "Show Iteration Results" box in the Solver Options dialog and re-solve. The Solver will pause with the message "Solver paused, current solution values displayed on worksheet." Click Continue to see the path towards the solution taken by the Solver.
Consider the possibilities for poorly scaled models, role of the Tolerance option for integer problems, limitations on nonlinear problems, and problems with discontinuous functions outlined below.

Problems with Poorly Scaled Models

A poorly scaled model is one in which the typical values of the objective and constraint functions differ by several orders of magnitude. A classic example is a financial model with some dollar amounts in millions, and other rate of return figures in percent. Poorly scaled models often cause difficulty for both linear and nonlinear Solver algorithms; the effect is often more severe for the nonlinear GRG Solver.

The Solver must perform many calculations where quantities derived from the values of the objective and constraints must be divided into and subtracted from one another. Because of the finite precision of computer arithmetic, when these calculations are performed with values of very different magnitudes, roundoff error builds up to the point where the Solver can no longer reliably find the optimal solution.

When the Use Automatic Scaling box in the Solver Options dialog is checked, the Solver will attempt to scale the values of the objective and constraint functions internally in order to minimize the effects of a poorly scaled model. In the built-in Microsoft Excel Solver, this option is effective only for nonlinear problems solved with the standard GRG solver. In the enhanced solvers, this option works for all types of models.

Poor Scaling and Assume Linear Model

Because the standard Microsoft Excel Solver does not automatically re-scale the objective function and constraints for linear problems, a model which is completely linear but which is poorly scaled may cause sufficient roundoff error to make the Solver's internal model inaccurate -- yielding the error message "The conditions for Assume Linear Model are not satisfied." See the Assume Linear Model option for further details. The solution to this problem is to re-scale the model so that the typical values of the objective function and constraints do not differ from each other, or from the values of the decision variables, by more than three or four orders of magnitude. As a short-term fix, you may also "loosen" the Precision setting, as described in Precision and Tolerance.

All of Frontline's enhanced solvers include both the ability to automatically re-scale linear models internally, and a more robust test for linearity, part of which is performed at the beginning of the solution process, rather than at the end. These methods will eliminate almost all cases where a poorly scaled linear model could yield the error message above. Since no automatic scaling method will work in all situations, we recommend that you take steps to ensure that the model on your worksheet is reasonably well scaled -- even if you do take advantage of the Use Automatic Scaling option.

The Tolerance Option and Integer Constraints

When you solve a problem with integer constraints (a mixed-integer programming problem, which can take a great deal of time), the solution process is governed by the Tolerance option in the Solver Options dialog. Since the default setting of the Tolerance option is 0.05, the Solver stops when it has found a solution satisfying the integer constraints whose objective is within 5% of the true integer optimal value. Therefore, you may know of or be able to discover an integer solution which is "better" than the one found by the Solver.

The reason that the default setting of the Tolerance option is 0.05 is that the solution process for integer problems -- which can take a great deal of time in any case -- often finds a near-optimal solution (sometimes the optimal solution) relatively quickly, and then spends far more time exhaustively checking other possibilities to find (or verify that it has found) the very best integer solution. The Tolerance option default setting is a compromise value that often saves a great deal of time, and still ensures that a solution returned by the Solver will be within 5% of the true integer optimal solution.

To ensure that the Solver finds the true integer optimal solution -- possibly at the expense of far more solution time -- set the Tolerance option to a value of zero. For more information on how the Tolerance option is used, consult Precision and Tolerance.

Limitations on Nonlinear Problems

Nonlinear problems are intrinsically more difficult to solve than linear problems, and there are fewer guarantees about what the Solver (or any optimization method) can do. The Solver uses the GRG (Generalized Reduced Gradient) algorithm -- one of the most robust nonlinear programming methods -- to solve problems whenever the Assume Linear Model box in the Solver Options dialog is unchecked. (When the box is checked, the Solver uses the Simplex method for linear programming problems.)

Bear in mind that -- since the Assume Linear Model box is unchecked by default -- the Solver will try to solve your model using the GRG method, even if it is actually a linear model that could be solved by the (faster and more reliable) Simplex method. The GRG method, while it is always slower, will usually find the optimal solution to a linear problem -- but occasionally you will receive a Solver Completion Message indicating some uncertainty about the status of the solution -- especially if the model is poorly scaled, as discussed above. So you should make sure that the Assume Linear Model box is checked for linear problems.

A nonlinear problem may have more than one "feasible region", or set of similar values for the decision variables, where all of the constraints are satisfied. Within each feasible region, there may be more than one "peak" (if maximizing) or "valley" (if minimizing) -- and there is no general way to determine which peak is tallest, or which valley is deepest. There may also be false peaks or valleys known as "saddle points." Because of these possibilities, nonlinear optimization methods can make few guarantees about finding the "true" optimal solution.

When dealing with a nonlinear problem, it is a good idea to run the Solver starting from several different sets of initial values for the decision variables. Since the Solver follows a path from the starting values (guided by the direction and curvature of the objective function and constraints) to the final solution values, it will normally stop at a peak or valley closest to the starting values you supply. By starting from more than one point -- ideally chosen based on your own knowledge of the problem -- you can increase the chances that you have found the best possible "optimal solution."

If your model has certain mathematical properties, such as convexity, it is possible to make stronger guarantees about the Solver's ability to find the true optimal solution. For more information on this topic consult the Recommended Books, particularly the titles by Wayne Winston.

GRG Solver Stopping Conditions

It is important to understand what the nonlinear GRG Solver can and cannot do, and what each of the possible Solver Completion Messages means. At best, the GRG Solver -- like virtually all nonlinear optimization algorithms -- can find a locally optimal solution to a reasonably well-scaled model. At times, the Solver will stop before finding a locally optimal solution, when it is making very slow progress (the objective function is changing very little from one trial solution to another) or for other reasons. For a more complete explanation of the GRG Solver's stopping conditions, click on the highlighted topic above.

Problems with Discontinuous Functions

Where the graph of a continuous function is an unbroken line or curve, the graph of a discontinuous function contains one or more "breaks." The most common example is the IF function. For example:

IF(A1>10,B1,2*B1)

is discontinuous around A1=10 because its value "jumps" from whatever value B1 has to twice that value. A nonlinear solver relies on (partial) derivative values to guide it towards a feasible and optimal solution; since it is unable to compute the derivatives of a function at points where that function is discontinuous, it has trouble determining how to proceed. In practice, the nonlinear GRG Solver used in Excel can sometimes deal with discontinuities which are "incidental" to the problem, but as a general statement, the nonlinear Solver cannot be expected to find optimal solutions to such problems. However, the Evolutionary Solver included in Frontline's enhanced Solver products can make progress on these problems.

If you try to solve a problem with discontinuous functions with the Assume Linear Model box checked, it is possible -- though unlikely -- that the linearity test performed by the Solver will not detect the discontinuities and will proceed to try to solve the problem. (This probably means that the functions were linear over the range considered by the linearity test -- but there are no guarantees at all that the solution found is optimal!)

You can use discontinuous functions such as IF and CHOOSE in calculations on the worksheet which are not dependent on the decision variables, and are therefore constant in the optimization problem. But any discontinuous functions that do depend on the variables will likely cause problems for the Solver. Users sometimes fail to realize that certain functions, such as ABS and ROUND, are discontinuous at certain points. For more information on this subject, consult Discontinuous and Non-Smooth Functions and Linear and Nonlinear Functions.