The University of North Carolina
at Pembroke
Using SOLVER in MicroSoft Excel
Link to Dr. Frederick's page
To perform a linear programming
problem in Excel, follow these steps:
1. In an Excel spreadsheet,
set up a matrix of coefficients and limits for the constraints and for
the objective function.
|
A |
B |
C |
D |
E |
F |
| 1 |
Constraints |
X1 |
X2 |
X3 |
Formulas |
Limits |
| 2 |
Labor time |
64 |
33 |
14 |
=sumproduct(b2:d2,b$7:d$7) |
400 |
| 3 |
Machine time |
50 |
60 |
9 |
=sumproduct(b3:d3,b$7:d$7) |
400 |
| 4 |
Management time |
3 |
3 |
3 |
=sumproduct(b4:d4,b$7:d$7) |
80 |
| 5 |
|
|
|
|
|
|
| 6 |
Objective Function |
3.5 |
0.45 |
2.2 |
=sumproduct(b6:d6,b$7:d$7) |
|
| 7 |
Decision Variables |
1 |
1 |
1 |
|
|
| 8 |
|
|
|
|
|
|
2. Click on Tools, then Data
Analysis, then Solver.
3. Enter the instructions
in the Solver Parameters box.
-
Set Target Cell: Enter the address
of the cell that contains the formula for the objective function.
-
Equal to: Click on the proper button
to indicate whether the objective function should be minimized or maximized.
-
By Changing Cells: Enter the
addresses that contain the decision variables.
-
Subject to the Constraints: Click
on Add. The Cell Constraint box will appear.
-
Cell Reference: enter the address of
the cell that contains the formula for the left-hand side of the constraint.
That would be the cell that contains an expression such as =sumproduct(b2:d2,b$9:d$9).
-
select <=, >= or = depending on
whether the formula should be less than, greater than, or strictly equal
to the value given on the right-hand side.
-
Constraint: enter the address
of the right-hand side value. The right-hand side value is the number
which is the upper or lower limit that the solution must satisfy.
-
When each constraint is completed,
click Add. When the last constraint is completed, click OK.
(You may click OK instead of Add after the last constraint.)
-
Constraints may be entered one at a
time. When there is a group of constraints that are all less-than
constraints or all greater-than constraints, they may be entered as a group
by entering the block of formula cells in Cell Reference and entering the
block of limits in Constraint.
-
Click on Options. When the Solver
Options box appears, click on Assume Linear Model and Assume Non-Negative
-
Click on the Solve button.
4. Solver will try
to solve your problem. If it is successful, it will present a box
asking what results you want it to show: the answer report, the sensitivity
report, and/or the limits report. If it is not successful, it will
give a message such as "did not converge in time allowed" or "no feasible
region". If this happens check the following:
-
make sure you have told Excel to perform
the right kind of analysis: maximize or minimize.
-
make sure your constraints are greater-than
or less-than when they should be.
-
make sure your cell formulas are correct.
-
make sure there are coefficients in
the cells that the formulas use.