# How to Perform Mixed Integer Linear Programming in Excel

Here is an overview of mixed integer linear programming via the Solver.

## 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. It uses:

• Decision Variables: 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.

## Perform Mixed Integer Linear Programming in Excel: Step-by-Step Procedures

We have to set your objective function and the constraints.

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 X3 are integers. On the other hand, Y1, Y2, and Y3 are binary numbers.  We need to find the minimum value of Z.

### Step 1 – Insert Details

• Create three separate tables like below. The tables should include:
• Decision Variables
• Constraints
• Objective

• Click on the File tab.

• Select Options.

• This will open the Excel Options.
• Select Excel Add-ins and click on Go in the Manage.

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

• You will see the Solver feature in the Analysis section of the Data tab.

### Step 3 – Type the Mixed Coefficients of Objective Function and Constraints

• Write down the coefficients of the objective function in the B17:G17 range.
• Input the coefficients of the constraints in the B6:J13 range.
• Keep the Total column empty for now.
• Your worksheet should look like this.

### Step 4 – Calculate the Totals

• Go to H10 and enter the following formula
=SUMPRODUCT(\$B\$6:\$G\$6,B10:G10)
• Press Enter to get the output.

• Use the Fill Handle to AutoFill up to H13.

### Step 5 – Measure the Dependent Variable of Objective Function

• Go to H6 and insert the following formula.
=SUMPRODUCT(B17:G17,B6:G6)
• Hit Enter.

### Step 6 – Apply the Solver Add-in

• Go to the Data tab.
• Select Solver.

• A Solver Parameters window will appear.
• Set the objective H6 to be minimum.
• Set the variables that Excel is going to change.
• Select Add to set the constraints.

• Set the Cell Reference and the condition from the drop-down list.

• Excel has added a constraint.

• Select a solving method.
• Click Solve.

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

### Step 7 – Show Final Output

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

## Related Articles

<< Go Back to Excel Linear Programming | Solver in Excel Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Akib Bin Rashid

AKIB BIN RASHID, a materials and metallurgical engineer, is passionate about delving into Excel and VBA programming. To him, programming is a valuable time-saving tool for managing data, files, and internet-related tasks. Proficient in MS Office, AutoCAD, Excel, and VBA, he goes beyond the fundamentals. Holding a B.Sc in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, MD AKIB has transitioned into a content development role. Specializing in creating technical content centred around Excel and... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF