Using Excel's Solver for Linear Programming Problem

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?
 
M

Mike Middleton

fcharn -

Browse to www.solver.com, click "Solver Tutorial," and click "Can you show
me step by step?"

Or, search for and look at the SolvSamp.xls workbook, which is installed on
your hard drive when Solver is installed.

- Mike
www.mikemiddleton.com
 
D

Dana DeLouis

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
 
Top