The Excel solver is a powerful add-in that allows you to do things like perform nonlinear regressions, solve systems of linear equations and non-linear equations, and optimize designs.
The Solver is an add-in that is not enabled in Excel by default. To turn it on, go to the Developer tab, then choose Excel Add-ins. In the window that opens, select the checkbox next to Solver Add-in. It will take a moment to install.
The Solver will now be added to the Data tab’s Analysis section on the far right. Click it to see the Solver window. The solver window has four main parts:
- The objective
- The variables
- The constraints
- The solving method
In the Objective section, you’ll choose a cell containing a formula that you would like to maximize, minimize, or set to a specific value (like the set cell in Goal Seek).
The variable cells are cells containing values only that are changed in order to reach the objective. You can have anywhere from one to two hundred cells specified here.
You can add constraints to ensure the variables are held within certain bounds. They can be constrained with =, ≥, or ≤ operators and either a value or another cell. You can also specify that a variable be an integer or binary value, or force some variables to be different values.
The final part of the Solver window is the solving method. There are three algorithms to choose from:
- GRG Nonlinear – for smooth, nonlinear problems
- LP Simplex – for linear problems
- Evolutionary – for discontinuous or non-smooth problems.
Problems that contain logic or absolute value functions are examples of non-smooth problems. For nonlinear problems, you’ll reach an optimum solution most quickly with the GRG nonlinear solver. However, this algorithm is known for finding solutions that differ depending on the initial values used. The Evolutionary algorithm is much better at finding a global minimum or maximum, but the trade-off is that it runs much more slowly. You’ll learn more about the best algorithm to choose in a later section.
