Problems with Discontinuous Functions

A discontinuous function is one whose graph shows sudden "breaks" instead of a smooth (straight linear, or curved nonlinear) line. 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 model with a discontinuous function does not meet the conditions required by either the linear or the nonlinear solver in 1-2-3 97 Edition.

A nonlinear solver relies on partial derivative information to guide it towards a feasible and optimal solution; since it is unable to compute the partial derivatives of a function at points where that function is discontinuous, it cannot guarantee that any solution it finds is truly optimal. In practice, the nonlinear GRG algorithm included with the standard Solver can sometimes deal with discontinuities which are "incidental" to the problem, but as a general statement, the Solver cannot handle problems where the objective function or some of the constraints are discontinuous.

If you try to solve a problem with discontinuous functions with the linear Simplex Solver, it is possible -- though very 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, read the section "Functions to Avoid: Discontinuous Functions" in the topic "Solver Basics."