How to Perform Mixed Integer Linear Programming in Excel

Excel is the most widely used tool for dealing with massive datasets. We can perform myriads of tasks of multiple dimensions in Excel. In this article, I will explain how you can perform mixed integer linear programming in Excel. The entire process is going to be divided into some easy steps for your convenience.

Here is an overview of this article.

mixed integer linear programming excel


Download Practice Workbook

Download this workbook and practice while going through the article.


Introduction to Mixed Integer Linear Programming

Mixed Integer linear programming is a type of mathematical method that consists of integer variables and linear objective functions and equations. The mixed-integer linear programming has both continuous and integer variables. With the help of linear programming, we can determine the minimum or maximum outcome of a given problem with some conditions. It is a tool that can be used to achieve a way to apply limited resources in the best possible manner. All types of linear programming include some main factors. They are given below:

  • Decision Variables: We determine the decision variables that minimize or maximize the objective function.
  • Objective Function: This is a function that helps us to determine the decision variables. It expresses the relation between the result and the variables.
  • Constraints: Constraints are also functions that denote different conditions on possible solutions.

Step-by-Step Procedures to Perform Mixed Integer Linear Programming in Excel

Let’s first understand the problem. I will explain the steps with the help of this problem. First of all, you have to set your objective function and the constraints.

I have assumed one shown below.

Objective Function

Z=2.79X1+2.19X2+2.99X3+400Y1+500Y2+600Y3

Constraints

  • X1+X2+X3=1050
  • X1-420Y1<=0
  • X2-550Y2<=0
  • X3-650Y3<=0

Here, X1, X2, and X3are integers. On the other hand, Y1, Y2, and Y3are binary numbers. Also, we need to find the minimum value of Z.


Step 1: Insert Details

Once you set the equations, you have to input the details in an Excel sheet. You can create three separate tables like below. The tables should include,

  • Decision Variables
  • Constraints
  • Objective

Input

Read More: How to Solve Integer Linear Programming in Excel (With Easy Steps)


Step 2: Load Solver Add-in in Excel

Then, we need to load the Solver Add-in in Excel. To do so,

  • Click on the File tab.

Solver Add-in

  • After that, select Options from the leftbottom corner of the screen.

Options mixed integer linear programming excel

  • It will open the Excel Options.
  • In the Excel Options window, select Add-ins.
  • Then, select Excel Add-ins and click on Go in the Manage.

Add-ins mixed integer linear programming excel

  • An Add-ins message box will pop up.
  • Check Solver Add-in and select OK from the message box.

mixed integer linear programming excel

  • Finally, you will see the Solver feature in the Analysis section of the Data tab.

Solver Add in

Read More: How to Use Excel Solver for Linear Programming (With Easy Steps)


Step 3: Type Mixed Co-Efficients of Objective Function and Constraints

The next step is to write down the coefficients of objective function and constraints.

  • Write down the coefficients of the objective function in the B17:G17 range.
  • Similarly, input the coefficients of the constraints in the B6:J13 range.
  • Keep the Total column empty for now.
  • After this step, your worksheet should look like this.

Coefficients

Read More: How to Do Linear Programming with Sensitivity Analysis in Excel


Step 4: Calculate Totals

Now we will take care of the Total column. To measure this, we will use the SUMPRODUCT function.

  • First of all, go to H10 and write down the following formula
=SUMPRODUCT($B$6:$G$6,B10:G10)
  • Then, press ENTER to get the output.

Total calculation mixed integer linear programming excel

  • Then, use Fill Handle to AutoFill up to H13.

AutoFill

Read More: How to Find Optimal Solution in Linear Programming Excel


Step 5: Measure Dependent Variable of Objective Function

In this step, we will calculate the value of Z. The function to be used is again the SUMPRODUCT function.

  • Go to H6 and write down the following formula.
=SUMPRODUCT(B17:G17,B6:G6)
  • After that, press ENTER to get the output.

Dependent Variable


Step 6: Apply Solver Add-in

Now, you have to apply the Solver Add-in. To do so,

  • To begin with, go to the Data tab.
  • Then, select Solver.

Activation of Solver

  • A Solver Parameters window will appear.
  • Then, set the objective H6 to be minimum.
  • After that, set the variables that Excel is going to change.
  • Then, select Add to set the constraints.

Add Constraints

  • After clicking Add, an Add Constraint box will appear.
  • Then, set the Cell Reference and the condition from the drop-down list.
  • Finally, click Add.

Add Constraints

  • You will see that Excel has added a constraint.

Add Constraints

  • Similarly, add other constraints.
  • Then, select a solving method.
  • Finally, click Solve.

Add Constraints and select methods

  • The Solver Result box will appear then.
  • Click OK.

Add Constraints and select methods

Read More: How to Solve Blending Linear Programming Problem with Excel Solver


Step 7: Show Final Output

After clicking OK, Excel will solve the problem for you. The outputs are shown below.

Output


Conclusion

In this article, I have demonstrated how to perform mixed integer linear programming in Excel. I hope it helps everyone. If you have any suggestions, ideas, or feedback, please feel free to comment below. Please visit Exceldemy for more useful articles like this.


Related Articles 

Akib Bin Rashid

Akib Bin Rashid

Hi, this is MD Akib Bin Rashid. I completed my BSc in Engineering back in 2019. After that, I did an MBA. Then, I joined as an Excel and VBA Content Developer at SOFTEKO Digital. Being passionate about data analytics and finance, I will try to assist you with Excel

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo