Using Excel's Solver for Linear Programming Problem

  • Thread starter Thread starter fcharn
  • Start date Start date
F

fcharn

I would like to solve a Linear Programming problem using Excel's Solver
as follows:
Min C = 100*X1 + 150*X2 + 120*X3
Subject to constraints:
X1 + X2 + X3 = 6
X1 + 2*X2 + X3 >= 8
X1 + X2 + 2*X3 <= 9
How do I set it up to arrive at the solutions?
 
Hi. One key to using Solver with a constraint that 3 numbers equaling 6 is
the following.
If Solver adjusts 3 cells, the total will never equal 6 due to rounding.
Adjust only 2 cells, with the 3rd cell having the formula 6-X1-X2.
Have 3 blank vertical cells, and give them the range name "Adj"
(Third cell holds formula above)
Have Target Cell with formula: =SUMPRODUCT({100;150;120},Adj)
Have Two worksheet formulas:
=SUMPRODUCT(Adj,{1;2;1})
=SUMPRODUCT(Adj,{1;1;2})
In Solver, Minimize Target, by adjusting the two blank cells within "Adj".
Add constraints that the first one is >=8, second one is <=9.
Add another constraint that "Adj" >=0

This should give you a solution of 700, with X1=4, X2=2, and X3=0
 
Back
Top