The Solver add-in is launched, as previously mentioned, by the corresponding command in the Analysis group on the Data tab of the ribbon.
The appearance of the Solver Parameters window and its options are shown in Figures and in Table.
By clicking the Options button (in the Solver Parameters window), the Options dialog box opens, whose characteristics are described in Table.
Table. Options of the Solver Parameters window
| Name | Description |
| Set Objective | Specifies the cell containing the objective function (optimization criterion) of the task under consideration. |
| To | Select one of three options (Max, Min, Value Of) to determine the type of relationship between the solution and the target cell. |
| By Changing Variable Cells | Specifies the cells that should change during the solving process (i.e., the decision variables). |
| Subject to the Constraints | Displays the constraints imposed on the variables. Constraints may take the form of equalities, inequalities, integer requirements, or binary values (0 or 1). To add, edit, or delete a constraint, use the respective Add, Change, or Delete buttons. Constraints are added one at a time and displayed in the Add Constraint window (Fig. 9.3), opened by clicking Add. In the Cell Reference field, the left-hand side of the constraint is entered; in the Constraint field, the right-hand side. The dropdown menu specifies the relationship type: >=, <=, =, int, bin, or dif (different). – int requires integer values only; bin requires binary values (0 or 1); dif requires all values to be distinct. Pressing Add allows entry of the next constraint. Pressing OK completes constraint entry and returns to the Solver Parameters window with all data filled in. |
| Make Unconstrained Variables Non-Negative | Sets the requirement that variables must be non-negative. |
| Select a Solving Method | Allows the selection of an optimization algorithm used by Solver: GRG Nonlinear, Simplex LP, or Evolutionary. A note under the list provides guidance on the use of each method. |
| Reset All | Restores Solver Parameters to their initial state, clearing all settings. |
| Load/Save | Allows saving or loading of Solver configurations. |
| Options | Allows modification of solving conditions and options. Default values are suitable for most problems. |
| Solve | Starts the solution process with the current parameters. Upon completion, the Solver Results dialog box will appear. |
Table. Options of the Options window
Tab: All Methods
| Name | Description |
| Constraint Precision | Sets the required precision with which the solution must satisfy constraints. A constraint is considered satisfied if the difference between the cell value and the constraint value does not exceed the specified number. The smaller the number, the higher the precision. |
| Use Automatic Scaling | Enables automatic normalization of input and output values that differ greatly in magnitude (e.g., maximizing return percentage relative to investments measured in millions). |
| Show Iteration Results | Pauses the solution process to display intermediate iterations for inspection. |
Group: Integer Constraints
- Ignore Integer Constraints and Integer Optimality (%).
- The default value of Integer Optimality (%) is 1%. Set to 0% for the most accurate solution to integer or binary problems.
- Note: For integer constraint problems, it is recommended to re-run Solver after finding an initial solution with default settings, using greater precision and smaller tolerance, then compare with the original solution.
Group: Solution Limits
- Max Time (seconds): limits the time allocated for solving.
- Iterations: limits the number of intermediate computations.
- Evolutionary and Integer Limits (for evolutionary and integer-constrained problems):
- Max Subproblems: sets the maximum number of subproblems to process.
- Max Feasible Solutions: sets the maximum number of feasible solutions to consider.
Note: If the solution process reaches any maximum (time, iterations, subproblems, or feasible solutions), Solver will return the best solution found and display it in the Solver Results window.
Tab: GRG Nonlinear
| Name | Description |
| Convergence | Sets the tolerance for acceptable deviation from the optimal solution. |
| Derivatives Group | Allows selection of the numerical differentiation method. Choosing Central Derivatives produces more accurate results but significantly increases computation time. |
| Multi-Start Group | Used for sequential searches of optimal solutions. Enabling Use Multi-Start processes several starting points. – Population Size: sets the number of solution attempts (minimum 10, maximum 200). – Random Seed: specifies an initial positive value for the random number generator. Different seeds yield different final results. Leaving the field empty uses a new random number each run. – Require Bounds on Variables: upper and lower bounds for variables must be defined. |
Tab: Evolutionary
| Name | Description |
| Convergence | Sets the tolerance for acceptable deviation from the optimal solution. |
| Mutation Rate | A number between 0 and 1 indicating the relative frequency of changes in the population of solutions. |
| Population Size | Sets the number of solutions (minimum 10, maximum 200). |
| Random Seed | Specifies a starting random number seed. |
| Maximum Time Without Improvement | Sets the maximum seconds allowed without significant improvement in the solution. |
| Require Bounds on Variables | Requires definition of upper and lower bounds for variables in the constraints list. |