Functions to Avoid: Discontinuities

There are many formulas and built-in functions which you can use in a 1-2-3 spreadsheet, but which cause difficulties for both linear and nonlinear solvers. These functions share the property that they are non-smooth or discontinuous at some point. 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 information from partial derivatives 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.

A partial list of the most common 1-2-3 functions which are discontinuous at certain points would include the ones listed below.

@ABS
@MIN
@MAX
@INT
@ROUND
@IF
@CHOOSE
@CEILING
@FLOOR
@COUNT

If you aren't sure about a particular function, try graphing it (by hand or in 1-2-3) over the expected range of the decision variables; this will usually reveal whether the function is smooth or discontinuous.