To be able to use ‘Solver’ more efficiently, let’s have an orientation first about the different parts and sections of the ‘Solver’ window:
- Set Objective
- By Changing Variable Cells
- Subject to the Constraints
- Select a Solving Method
This is where you refer to the cell which contains the formula of the objective or goal of the set. In earlier Excel versions, the objective cell is also known as the ‘Target’ cell.
You can set the objective into three (3) functions:
By Changing Variable Cells
Variable cells refer to the cells that can be changed to complete the objective. Previously, they’re known as ‘Changing’ or ‘Adjustable’ cells. As of now, Excel allows only up to 200 variable cells.
Subject to the Constraints
Excel ‘Solver’ constraints are the restrictions, limits, or conditions you set that must be met in solving the problem.
Once you click the ‘Add’ button, another window pops up where you can enter the ‘Cell Reference’, the ‘Constraint’, as well as their relationship towards each other.
There are six (6) types of relationship you can set:
- Less than or equal to
- Equal to
- More than or equal to
- Int (integer; the referenced cell should be an integer)
- Bin (binary; the referenced cell should only be 0 or 1)
- Dif (different; the referenced cell or cells should have different values)
Select a Solving Method
Excel lets you choose from three (3) solving methods or algorithms:
1. GRG Nonlinear
The Generalized Reduced Gradient Nonlinear method solves smooth nonlinear problems.
2. Simplex LP
Simplex LP solves linear programming problems.
The ‘Evolutionary’ method solves hard optimization problems. If the problem has non-smooth or discontinuous functions, making it hard to figure out if the direction of the function is increasing or decreasing, use this method.