Non-smooth problems – where the objective and/or constraints are computed with discontinuous or non-smooth Excel functions – are the most difficult types of optimization problems to solve. There are few, if any, guarantees about what Solver (or any optimization method) can do with these problems.
The most common discontinuous function in Excel is the IF function where the conditional test is dependent on the decision variables. Other common discontinuous functions are CHOOSE, the LOOKUP functions, and COUNT. Common non-smooth functions in Excel are ABS, MIN and MAX, INT and ROUND, and CEILING and FLOOR. Functions such as SUMIF and the database functions are discontinuous if the criterion or conditional argument depends on the decision variables.
A smooth nonlinear solver, such as the GRG Solving method, relies on derivative or gradient information to guide it towards a feasible and optimal solution. Since it is unable to compute the gradient of a function at points where the function is discontinuous, or to compute curvature information at points where the function is non-smooth, it cannot guarantee that any solution it finds to such a problem is truly optimal. In practice, the GRG method can sometimes deal with discontinuous or non-smooth functions that are “incidental” to the problem, but as a general statement, this Solving method requires smooth nonlinear functions for the objective and constraints.
If your model includes discontinuous or non-smooth functions, your simplest course of action is to use the Evolutionary Solving method to find a “good” solution. Be sure to read the topic Evolutionary Solving Method Stopping Conditions to understand the characteristics and limitations of this Solving method – it is very useful for getting solutions, but it is not a panacea.