Using Solver

Now it is time to select `Solver...' from the `Tools' menu. After you have done it, the following dialog will appear:

Figure 6-2 The empty Solver dialog.

Since we have the objective function in E3 type this into the `Set Target Cell:' entry. We are about to maximize this function, thus the radio button `Max' should be pressed on. By default, the problem is assumed to be maximization problem. The input variables (x, y, and z) were in cells B3:D3 so type the cell range into the `By Changing Cells:' entry.

The model to be optimized is a linear model. Thus, we should check that the check button `Linear (LP/MILP)' is pressed on under the `Model Assumptions'. Also make sure that the assume non-negative button is on, otherwise, the input variables can also take negative values. There is also a check button `Assume Integer (Discrete)' which adds an integer constraint for all the input variables. The integer optimization is described, however, later.

A few additional options can be set too. If you want to limit the number of iterations the optimization algorithm is allowed to take you can set the maximum number in the `Max iterations' entry box. Similarly, you can limit the maximum time the optimization is allowed to take in the `Max time' entry box. If either one of these settings is exceeded during the optimization, the optimization is interrupted and an error dialog is displayed.

Some models can be better solved if the model is scaled into another form before the actual optimization. Gnumeric solver supports automatic scaling which can be checked on by using the check button on the bottom of the dialog. Note that the automatic scaling does not change the model since before checking out the results the model is scaled back to its original form.

Now we can add the constraints. Select the `Constraints' page from the top of the dialog and the following page should appear.

Figure 6-3 Add constraint dialog.

In this page, you can see all constraints that have been defined in the `Subject to the Constraints:' window. Since none has been defined, this window should be empty. Now type in the constraints (#1-#4) one by one.

When adding constraints, the three entry boxes in the bottom of the dialog are used. Put a cell name of the total left hand side (LHS) cell into the `Left Hand Side:' entry box. In our example, this would be E7 for the constraint #1, E8 for constraint #2, and so on. The combo entry in the middle defines the type of the constraint. It can be `<=', `=', `>=' , or `Int'. We will explain the `Int' constraints later. In this example, you should select `<=' for constraints #1-#2, `>=' for #3, and `=' for constraint #4. The last entry on the right takes the right hand side values of the constraints. For constraints #1-#4 they should be G7 (5), G8 (9), G9 (1), and G10 (4) in this order.

After typing a constraint press Add button, and you will be able to define the next one. When you have typed in all the constraints, the Solver dialog should look like this:

Figure 6-4 The solver dialog

The order of the constraints does not matter. If you want to change or delete a constraint click it and then press `Change' or `Delete' button.

Note that you can also type ranges into the LHS and RHS entries. For example, you could have typed D7:D8 and G7:G8 instead of the two separate constraints.

If the constraints have now been typed in correctly, we should check what reports we want to produce.

Select the `Reports' page from the top of the dialog and the a page containing five check buttons should appear. By pressing these check buttons on, you can produce various kinds of reports. You can read more about solver reporting in the following section.

After you have specified the parameters, the constraints and the reporting options it is time to press the `OK' button. If everything went ok, you will see a dialog saying: `Solver found an optimal solution. All constraints and optimality conditions are satisfied.'. This means that the solver found an optimal solution and the optimal values are now stored into the input variables. For all models, this, however, does not happen.

If a feasible solution cannot be found, the solver reports that `A feasible solution could not be found. All specified constraints cannot be met simultaneously.'.

If the model is unbounded, the solver reports that `The Target Cell value specified does not converge! The program is unbounded.'.

If the maximum number of iterations specified in the options was exceeded, the solver reports that `The maximum number of iterations exceeded. The optimal value could not be found.'.

If the maximum time specified in the options was exceeded, the solver reports that `The maximum time exceeded. The optimal value could not be found in given time.'.