4 Excel and Solver.
4.1 Spreadsheets.
There are many computer programs to solve optimization problems. The most widely available is
Microsoft Excel. Excel is an all-purpose spreadsheet-based program. Think of a spreadsheet as a
grid of cells where each cell has a pre-defined address. The cells hold data or formulas acting on
the data. A cell displays its content in a variety of formats. If a cell holds a formula, then the
current value of the formula is displayed. As values change in a spreadsheet, affected cells are
continually updated unless this mechanism is suppressed. For instance, if cell A1 holds the value 2
and cell B1 the formula =A1*A1, then B1 displays 4. If the value in A1 is changed to 3, then B1
right away changes its value to 9 without any user-intervention.
4.2 Solver.
As expected, Excel can be programmed to solve a variety of optimization problems. The good
news is that there is a Tools Add-In called Solver, which has several useful optimization
algorithms. The examples given here refer to Microsoft Excel 2000 (Version 9.0). When Excel is
started it creates a new document called Book 1. Sheet 1 of Book 1 is displayed with the current
cell A1 highlighted by a dark black rectangle. To solve the primal problem
5711
max 2 3 when ,0
xy
xy xy
!"
#
$
$
!%
$&
$
'
,
take the following steps.
1. Enter the text !x" in A1, !y" in A2, !2x+3y" in A4, and !5x+7y" in A6.
2. Enter the formula !=2*B1+3*B2" in B4, and !=5*B1+7*B2" in B6.
3. Enter the number 11 in C6.
4. Start Solver.
5. Highlight B4 and observe how the !Set Target Cell" edit control changes.
6. Click in the !By Changing Cells" edit control.
7. Highlight B1:B2 by clicking holding and dragging. Observe the change in the edit control.
8. Click on Add.
9. Highlight B6 and observe the change in the edit control !Cell Reference".
10. Click in the !Constraint" edit control.
11. Highlight C6 and observe the change in the edit control.
12. Click Add.
13. Click Cancel.
14. Click Options.
