Microsoft Excel provides a very rich formula language, including many built-in functions that are discontinuous or non-smooth. These functions cannot be used with the Simplex LP Solving method. Discontinuous functions cause considerable difficulty, and non-smooth functions cause some difficulty for the GRG Nonlinear Solving method. The Evolutionary Solving method can handle these functions, but you’ll “pay a price” in solution time and quality. Some models can only be expressed with the aid of these functions; in other cases, you have a degree of choice in how you model the real-world problem, and which functions you use.
By far the most common discontinuous function in Excel is the IF function where the conditional test depends on the decision variables, as in the example =IF(C1>10,D1,2*D1) where C1 is a variable. Here is a short list of common discontinuous Excel functions:
IF, CHOOSE
LOOKUP, HLOOKUP, VLOOKUP
COUNT
INT, ROUND
CEILING, FLOOR
Here is a short list of common non-smooth Excel functions:
ABS
MIN, MAX
Formulas involving relations such as <=, = and >= (on the worksheet, not in the Solver Parameters outlined list) and logical functions such as AND, OR and NOT are discontinuous at their points of transition from FALSE to TRUE values. Functions such as SUMIF and the database functions are discontinuous if the criterion or conditional argument depends on the decision variables.
If you aren’t sure about a particular function, try graphing it (by hand or in Microsoft Excel) over the expected range of the variables; this will usually reveal whether the function is discontinuous or non-smooth