Where min(LB_x) is the minimum lower bound across all intervals (likewise for UB and for y). If the intervals are non overlapping we need to modily It can often guarantee an optimal solution (which is almost always the case for small problems) Using this formulation, the solver goes through many combinations of variables and tries to pick up the best one.
The constraints are all in expression <= 0 format. The decision variables are x_var, y_yar, cond1_true, cond2_true. If the Add-In is not listed, use Browse to select it, and click Open. If the Add-In is listed, simply check it in the list.
From the top-level Mac menubar, click the Tools menu and select Excel Add-ins to open the Add-ins dialog.
In the mode, the objective function cell is the sum of the binary variables. To install the add-in, follow these simple steps. The other cells have the constraints described above:įor example, for the first expression: J2: =x_var-cond1_true*x_UB1Īll these cells need to be <= 0 in the solver model. The green cells H2, J2 have the two new binary varibles, called cond1_true, cond2_true respectively. The logic is that instead of an IF expression we can impose the constraints: LB_x * z LB_x <= x <= UB_xĪnd because we maximize the sum of the two z variables, the x and y will try to fit i the corresponding ranges so that as many z as possible equal 1. We are going to replace these expressions with two binary variables, which equal one if each expression is satisfied and zero otherwise. The red cells ( B4 and E4) have the conditions you described, and the blue cell ( B5) has their sum.įor example, the condition for B4 reads =IF(AND(x_var=x_LB1,y_var=y_LB1),1,0) Here is a screenshot of the spreadsheet setup:įor convenience, I have used named ranges for the several quantities.Īnd for row 3 I use the same convention, but instead of x_ we have y_. Having said that, there is a way to formulate your problem as a mixed integer program, which, although still non-convex, can be solved with the "Simplex LP" method of Solver, and give a guaranteed maximum. For non-convex problems, the GRG Nonlinear solution method (the default used by solver) does not guarantee an optimal solution, as it can be trapped in locally best solutions which are not optimal. The reason that the Solver does not find the optimal solution in this toy problem is because the use of IF and AND statements make the problem non convex.