Standard Excel Solver - Problems with Nonsmooth and Discontinuous Functions
Problems with Nonsmooth
and 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 nonsmooth at certain points.
For more information on this subject, consult our Solver Tutorial, paying special attention to Optimization Problem Types.
< Back to Standard Excel Solver: Diagnosing Solver Results
< Back to Standard Excel Solver Support Information
