Excel Solver with mixed-type variables

Excel Solver is a powerful tool that can help you find optimal solutions for various kinds of problems, such as linear programming, nonlinear programming, integer programming, and mixed-integer programming. In this article, we will focus on the last type of problem, where some of the decision variables are continuous (such as real numbers) and some are discrete (such as integers or binary).

A mixed-integer programming problem can be formulated as follows:

Maximize (or minimize) f(x1, x2, ..., xn)
subject to:
g1(x1, x2, ..., xn) <= b1
g2(x1, x2, ..., xn) <= b2
...
gm(x1, x2, ..., xn) <= bm
x1, x2, ..., xk are continuous variables
xk+1, xk+2, ..., xn are integer or binary variables

where f is the objective function, g1, g2, …, gm are the constraint functions, b1, b2, …, bm are the right-hand side constants, and x1, x2, …, xn are the decision variables. The goal is to find the values of the decision variables that maximize (or minimize) the objective function while satisfying all the constraints.

Solving a mixed-integer programming problem is generally more difficult than solving a linear or nonlinear programming problem, because the presence of discrete variables introduces nonlinearity and nonconvexity into the problem. This means that there may be multiple local optima, and finding the global optimum may require exploring a large number of possible solutions.

One of the most common methods for solving mixed-integer programming problems is the branch-and-bound algorithm, which works as follows:

• Start with the original problem and relax the integrality constraints on the discrete variables, allowing them to take fractional values. This results in a linear or nonlinear programming problem that can be solved by standard methods, such as the simplex method or the gradient method. This solution provides an upper bound (for maximization problems) or a lower bound (for minimization problems) on the optimal value of the original problem.
• If the relaxed solution satisfies the integrality constraints, then it is also the optimal solution of the original problem, and the algorithm terminates.
• If the relaxed solution does not satisfy the integrality constraints, then choose one of the fractional variables and create two subproblems by branching on its value. For example, if x3 = 1.5 in the relaxed solution, then create two subproblems: one with x3 <= 1 and one with x3 >= 2. Each subproblem inherits the objective function and the constraints of the original problem, but with an additional constraint on the chosen variable.
• Solve each subproblem by relaxing the integrality constraints and applying the same method as in step 1. This provides a new upper bound or lower bound on the optimal value of the original problem, and also identifies the subproblems that are feasible (satisfy all the constraints) and infeasible (violate at least one constraint).
• If any of the subproblems is feasible and has an integer solution, then compare its objective value with the current best integer solution (if any) and update the best solution accordingly.
• If none of the subproblems has an integer solution, then choose one of the feasible subproblems and repeat step 3, creating new subproblems by branching on another fractional variable. This process continues until either an optimal integer solution is found or all the subproblems are infeasible or have worse objective values than the current best solution.

The branch-and-bound algorithm can be illustrated by a tree diagram, where each node represents a subproblem and each branch represents a constraint on a variable. The root node is the original problem, and the leaf nodes are the final subproblems. The algorithm explores the tree by selecting a node, solving its relaxed problem, and branching on a fractional variable. The algorithm can use different strategies for selecting a node, such as depth-first search, breadth-first search, or best-first search. The algorithm can also use different strategies for selecting a variable to branch on, such as the most fractional variable, the least fractional variable, or the variable with the largest impact on the objective function.

Procedures

To use Excel Solver to solve a mixed-integer programming problem, you need to follow these steps:

• Define the decision variables and their types (continuous, integer, or binary) in a range of cells. For example, you can use cells B2:B6 to store the values of x1, x2, …, x5, and use cells C2:C6 to indicate their types (0 for continuous, 1 for integer, or 2 for binary).
• Define the objective function in another cell, using the decision variables and any other parameters. For example, you can use cell B8 to store the formula =3B2+5B3-4B4+2B5-B6, which represents the objective function f(x1, x2, x3, x4, x5) = 3×1 + 5×2 – 4×3 + 2×4 – x5.
• Define the constraint functions and their right-hand side constants in another range of cells, using the decision variables and any other parameters. For example, you can use cells B10:B12 to store the formulas =B2+B3+B4, =2B2+3B3+4*B4, and =B5+B6, which represent the constraint functions g1(x1, x2, x3, x4, x5) = x1 + x2 + x3, g2(x1, x2, x3, x4, x5) = 2×1 + 3×2 + 4×3, and g3(x1, x2, x3, x4, x5) = x4 + x5, respectively. You can use cells C10:C12 to store the right-hand side constants 7, 12, and 5, respectively.
• Click the Data tab, and then click Solver in the Analysis group. This opens the Solver Parameters dialog box, where you can specify the problem settings.
• In the Set Objective box, enter the cell reference or name of the cell that contains the objective function. For example, enter B8 or name the cell Objective.
• In the To group, choose whether you want to maximize or minimize the objective function. For example, choose Max.
• In the By Changing Variable Cells box, enter the cell references or names of the cells that contain the decision variables. For example, enter B2:B6 or name the range Variables.
• In the Subject to the Constraints box, enter the cell references or names of the cells that contain the constraint functions and the right-hand side constants, using the <=, =, or >= operators to indicate the type of inequality. For example, enter B10<=C10, B11<=C11, and B12<=C12, or name the ranges LHS and RHS and enter LHS<=RHS.
• Click the Options button to open the Solver Options dialog box, where you can choose the solving method and other options.
• On the All Methods tab, in the Solving Method list, choose Simplex LP, GRG Nonlinear, or Evolutionary, depending on the type of your problem. For mixed-integer programming problems, the Evolutionary method is usually the most suitable, as it can handle nonlinear and non-smooth problems. However, you can also try the other methods if your problem is linear or smooth.
• On the All Methods tab, in the Assume Linear Model box, check or uncheck the option depending on whether your problem is linear or nonlinear. For mixed-integer programming problems, this option is usually unchecked, as most of them are nonlinear.
• On the All Methods tab, in the Assume Non-Negative box, check or uncheck the option depending on whether you want to impose non-negativity constraints on the decision variables. For mixed-integer programming problems, this option is usually checked, as most of them require non-negative variables.
• On the All Methods tab, in the Show Iteration Results box, check or uncheck the option depending on whether you want to see the intermediate results of the solution process. For mixed-integer programming problems, this option is usually unchecked, as the solution process can take a long time and produce a large number of iterations.
• On the All Methods tab, under Solving Limits, you can adjust the maximum time, iterations, and precision for the solution process. For mixed-integer programming problems, you may want to increase the time and iterations limits, as the solution process can be more complex and require more trials. You may also want to decrease the precision, as the solution may not be exact due to the discrete variables.
• On the GRG Nonlinear or Evolutionary tab, in the Convergence box, you can adjust the amount of relative change that you want to allow in the last five iterations before Solver stops with a solution. For mixed-integer programming problems, you may want to decrease the convergence, as the solution may not be smooth due to the discrete variables.
• On the GRG Nonlinear or Evolutionary tab, under Integer Constraints, you can specify the cells that contain the integer or binary variables and their types. For example, enter B2:B6 in the Cell Reference box, and choose Int or Bin in the Constraint box, depending on whether the variables are integer or binary. You can also use the Add and Delete buttons to add or delete integer constraints.
• Click OK to close the Solver Options dialog box and return to the Solver Parameters dialog box.
• Click Solve to start the solution process. Solver will try to find the optimal solution for the problem, or report that no feasible solution exists. If Solver finds a solution, it will display the Solver Results dialog box, where you can see the optimal value of the objective function and the optimal values of the decision variables. You can also see the status of the solution, such as optimal, feasible, infeasible, unbounded, or stopped by limits. You can choose to keep the solution, restore the original values, or generate reports on the solution details.
• Click OK to close the Solver Results dialog box and return to the worksheet, where you can see the solution values in the cells that contain the decision variables and the objective function.

To illustrate the use of Excel Solver for mixed-integer programming problems, let us consider the following example:

A company produces two types of products, A and B, using two machines, M1 and M2. Each product requires a certain amount of time on each machine, as shown in the table below:

The company has a total of 40 hours available on M1 and 36 hours available on M2. Each product also requires a certain amount of raw material, as shown in the table below:

The company has a total of 90 kg of raw material available. The company can sell each product at a certain price, as shown in the table below:

The company wants to maximize its total profit by determining how many units of each product to produce and sell. However, the company also faces the following additional constraints:

• The number of units of product A must be an integer multiple of 5.
• The number of units of product B must be either 0 or at least 10.
• The company must produce at least one unit of each product.

This is a mixed-integer programming problem, where the decision variables are the number of units of product A and product B, and they are subject to integrality and binary constraints. We can use Excel Solver to solve this problem by following the steps described above. Here is how the worksheet looks like after defining the decision variables, the objective function, and the constraint functions:

The formulas used in the cells are as follows:

• B2: =5*ROUND(B2/5,0)
• B3: =IF(B3>=10,B3,0)
• B5: =8B2+10B3
• B8: =2B2+4B3
• B9: =3B2+2B3
• B10: =5B2+3B3
• B12: =MOD(B2,5)
• B13: =IF(B3>=10,1,0)
• B14: =IF(B2>=1,1,0)
• B15: =IF(B3>=1,1,0)

The cell B2 uses the ROUND function to ensure that the value of product A is an integer multiple of 5. The cell B3 uses the IF function to ensure that the value of product B is either 0 or at least 10. The cells B12, B13, B14, and B15 use the MOD and IF functions to create additional constraints on the decision variables.

After setting up the Solver Parameters and Solver Options as described above, we click Solve and obtain the following solution:

The Solver Results dialog box shows the following information:

• Solver found a solution.
• All constraints and optimality conditions are satisfied.
• The solution is optimal, with a profit of $140.
• The optimal values of the decision variables are 10 units of product A and 10 units of product B.

We can click OK to keep the solution and return to the worksheet.

Other approaches

There are other methods and software tools that can be used to solve mixed-integer programming problems, such as the following:

• The branch-and-cut algorithm, which is similar to the branch-and-bound algorithm, but also adds cutting planes to the subproblems to reduce the feasible region and eliminate fractional solutions.
• The branch-and-price algorithm, which is similar to the branch-and-bound algorithm, but also generates new variables dynamically to improve the relaxed solution and reduce the branching.
• The Benders decomposition method, which decomposes the original problem into a master problem and a subproblem, and iteratively solves them until convergence.
• The Lagrangian relaxation method, which relaxes some of the constraints by adding penalty terms to the objective function, and solves the resulting problem by subgradient optimization or dual ascent methods.
• The genetic algorithm, which is a metaheuristic method that mimics the natural evolution process to generate and improve solutions by applying operators such as selection, crossover, and mutation.
• The simulated annealing algorithm, which is a metaheuristic method that mimics the physical annealing process to generate and improve solutions by applying random perturbations and accepting or rejecting them based on a temperature parameter.
• The tabu search algorithm, which is a metaheuristic method that generates and improves solutions by applying local search moves and using a memory structure to avoid cycling and diversify the search.
• The Gurobi Optimizer, which is a commercial software tool that can solve various types of optimization problems, including mixed-integer programming problems, using advanced algorithms and techniques.
• The CPLEX Optimizer, which is another commercial software tool that can solve various types of optimization problems, including mixed-integer programming problems, using advanced algorithms and techniques.
• The COIN-OR project, which is an open-source initiative that provides a collection of software tools and libraries for solving various types of optimization problems, including mixed-integer programming problems, using various algorithms and techniques.

Each of these methods and tools has its own advantages and disadvantages, and the choice of the best one depends on the characteristics of the problem, such as the size, the structure, the linearity, the convexity, and the integrality. Some of these methods and tools can also be combined or hybridized to achieve better performance and robustness.