Welcome!
In this video, I’ll be solving this mixed integer problem in Excel.
X1, X2, and X3 are integers, and Y1, Y2, and Y3 are 0-1 or binary variables.
I’m going set up my variables at the top here.
And next to them, I will place the objective function value.
I’m going to place the solution to the problem on the second row here.
I will highlight these cells yellow; that will be the values of the decision variables
at optimal solution.
And for the objective function, I will use green.
I will place the objective function coefficients in the 3rd row here.
For constraint 1, the coefficients are 1, 1, and 1 for X1, X2, and X3 respectively.
I’m going to click on constraint 1 and drag down the handle up to constraint 4.
For constraint 2, we have 1 for X1 and -400 for Y1.
For constraint 3, they are 1 for X2 and -550 for Y2 .
For constraint 4, we have 1 for X3 and -600 for Y3.
Now, I’m going to highlight these cells that will contain the constraint formulas
and fill them with light blue.
For visual purposes, I’m going to place the constraint signs and the right-hand sides
here.
Apostrophe and equal sign to display the equality sign for the first constraint.
Then INSERT SYMBOL to select the ≤ sign for the second.
Then drag down since the last 2 have the same sign.
I’m going to place the constraint right hand sizes here.
I’m going to place the objective function formula in this green cell by using
=SUMPRODUCT( Highlight the coefficients, comma, then the
variable values in yellow here.
That multiplies each coefficient with the adjacent variable value and adds the products.
Then Enter.
We do the same for the constraints: =SUMPRODUCT, select the constraint coefficients,
comma, then the variable cells but this time, I’m going to press the F4
button after selecting the yellow cells to make them absolute cells so I can drag to
fill the other cells with the formula while keeping the yellow cells locked down.
Double clicking the formula for the last constraint here shows that it worked.
Next, I’m going to click Data then Solver to solve the problem.
If you don’t see solver here, please see description on how to add it.
For, Set Objective, we will click the green cell that contains the objective function
formula here.
Since this is a Minimization problem, we will select Min here.
The changing cells will be the yellow cells that will contain the variable values at optimal
solution.
Next click Add to add the constraints.
Click on the formula for the first constraint, select the equal sign, then click on the right-hand
side.
Click Add.
Since the remaining constraints all have “less than or equal signs", highlight them all at
once, select the less than or equal sign, and then the right-hand sides.
Click Add.
Since the X’s are integers, highlight their values here, and select “int” for integer.
Click Add.
The Y’s are 0-1 variables.
So, select them, and choose binary.
Click OK since we’ve finished adding constraints Now, make sure this nonnegative box is checked
to enforce nonnegativity.
Then select Simplex LP here since were doing linear programming.
Click Solve.
We can already see the solution and the objective function value here
But we can also generate an answer report if we want.
And we can view the answer report here.
And that’s it.
Thanks for watching.