Using IF, MIN, MAX and Other Nonsmooth Functions

Users often create Excel models using functions such as IF, and comparison operators such as <= and >=, to express conditions such as "if we use this machine at all, we will incur a fixed setup cost of $x."  But these Excel operators and functions are nonsmooth or even discontinuous -- their values and/or their rates of change "jump" from one level to another as the decision variables change.  Models using these functions are called nonsmooth optimization problems -- they can be optimized with the Evolutionary Solver in the Premium Solver Platform, but this process is inherently slow and less certain to find the best solution, especially if the number of variables and constraints is large.

It's possible to express conditions like "if we use this machine at all, we incur a fixed setup cost of $x" (called a fixed-charge constraint) using binary integer variables and linear constraints, as described in the Premium Solver Platform User Guide.  But if you have less experience in optimization modeling, and less time available to learn these techniques, you may resort to familiar functions such as IF, MIN and MAX.

Automatic Model Transformation to Eliminate Nonsmooth Functions

To help you get better solutions, the Premium Solver Platform's Polymorphic Spreadsheet Interpreter can automatically transform your model containing IF, MIN, MAX, ABS, AND, OR, and NOT functions and comparison operators such as <= and >= into an equivalent model (that has the same optimal solution), with extra binary integer and continuous variables and linear constraints (that don't appear on your worksheet, but are handled internally by the Solver). 

If this transformation eliminates all nonsmooth functions, you'll be able to solve your model with the fastest Solvers for linear mixed-integer (LP/MIP) problems, which may outperform the Evolutionary Solver and find (and know they've found) the true optimal solution.

Below is an example model (click the worksheet to see it full size) that contains 9 variables, 12 linear constraints, and 1 objective function that is a sum of several cells containing IF functions.  If you try to solve this model with the LP/Quadratic Solver in the Premium Solver Platform, it reports that "The linearity conditions required by this Solver Engine are not satisfied."

Example Model Using IF (55392 bytes)

Clicking the Model button to display the Solver Model dialog, and clicking the Check Model button shows that this model is nonsmooth (NSP), because of the IF functions:

To see what the Premium Solver Platform's automatic model transformation can do, click the Transformed tab, and click the Check Model button again. 

The transformed model has 27 variables (including 18 new variables -- 9 continuous and 9 binary integer) and 67 functions (including new linear constraints whose effect is the same as the IF functions), but it is now an all-linear model.

Simply check the box "Solve Transformed Problem," close the Solver Model dialog, and click the Solve button in the main Solver Parameters dialog, still using the LP/Quadratic Solver.  This time, the Solver reports that "Solver found an integer solution within tolerance. All constraints are satisfied."

You can, of course, create and use binary integer variables and linear constraints yourself for problems like this one, and achieve even better results.  But if you're more familiar with Excel than with optimization modeling, and you're under a deadline, the Premium Solver Platform can save you hours of time! 

< Back to Premium Solver Platform Product Overview