Scribd
Upload
8 views

ILP problem Excel Solver (formulas and Solver part)

The document outlines the setup of formulas for an optimization problem using Excel's Solver tool, detailing how to create an objective function and constraints. It provides step-by-step instructions for configuring the Solver settings, including setting the objective, choosing maximization or minimization, and adding constraints. Finally, it emphasizes the importance of ensuring non-negativity and selecting the Simplex LP method before solving the problem.

Uploaded by

degentekle
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
8 views

ILP problem Excel Solver (formulas and Solver part)

The document outlines the setup of formulas for an optimization problem using Excel's Solver tool, detailing how to create an objective function and constraints. It provides step-by-step instructions for configuring the Solver settings, including setting the objective, choosing maximization or minimization, and adding constraints. Finally, it emphasizes the importance of ensuring non-negativity and selecting the Simplex LP method before solving the problem.

Uploaded by

degentekle
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 1

(The formulas in the cells, to setup):

1. Formula for the objective function (Cell H2, in our case)-> =SUMPRODUCT(B3:G3;
$B$2:$G$2) =>$ is simply to copy-paste the formula and allow it to change on left
2. Formula for the constraint (Cell H5, in our case)-> =SUMPRODUCT(B5:G5;
$B$2:$G$2) => according to the row but NOT on right (B2:G2, being the
solution values)
3. Formula for the constraint (Cell H6, in our case)-> =SUMPRODUCT(B6:G6;
$B$2:$G$2)
4. Formula for the constraint (Cell H7, in our case)-> =SUMPRODUCT(B7:G7;
$B$2:$G$2)
5. Formula for the constraint (Cell H8, in our case)-> =SUMPRODUCT(B8:G8;
$B$2:$G$2)
6. Formula for the constraint (Cell H9, in our case)-> =SUMPRODUCT(B9:G9;
$B$2:$G$2)

(Prior to that the worksheet's layout must be set up)

Click on "Data" tab, and click "Solver",


1. For the "Set Objective" we're going to click on the cell where there is the
objective formula (Cell H2, in our case),
2. We then choose whether it is a maximization (Max) or a minimization (Min)
problem,
3. Then for "By Changing Variable Cells" we select the solution cells (Cells B2:G2,
in our case),
4. Then to add a constraint we click "Add",
4.1. for constraint 1 we click (in our case on the "Left hand side" value) for
"Cell Reference", we then choose the adequate operator (= in our case), and we
click (in our case on the "Right hand side" value) for "Constraint", we then loop
the process by clicking "Add" until we reach the integer constraints, then we click
again on "Add".
4.2. for the integer constraints, we select the solution values forced to be
integers for "Cell Reference", we then choose in the middle between int (integer
constraint) or bin (binary constraint).
5. Next we make sure that "Make Unconstrained Variables Non-Negative" is checked,
6. We then choose as "Select a Solving Method" the Simplex LP,
7. We finally click "Solve".

You might also like