What is Solver in Excel?
The solver in excel is an analysis tool that helps find solutions to complex business problems requiring crucial decisions to be made. For every problem, the goal (objective), variables, and constraints are identified. The solver returns an optimal solution which sets accurate values of the variables, satisfies all constraints, and meets the goal.
For example, the solver helps create the most appropriate project schedule, minimize an organization’s expenses on transportation of its employees, maximize the profits generated by a given marketing plan, and so on.
The solver can solve the following types of problems:
- Optimization problems where profits need to be maximized and costs need to be minimized
- Arithmetic equations
- Linear and nonlinear problems
In optimization problems, the solver in excel helps to minimize or maximize the objective based on certain variables. Being known as a “what-if analysisWhat-if AnalysisWhat-If Analysis in Excel is a tool for creating various models, scenarios, and data tables. It enables one to examine how a change in values influences the outcomes in the sheet. The three components of What-If analysis are Scenario Manager, Goal Seek in Excel, and Data Table in Excel.read more” tool, the solver is enabled by the “add-insAdd-insAn add-in is an extension that adds more features and options to the existing Microsoft Excel.read more” option or the Developer tab of Excel.
The Functioning of the Excel Solver
For solving a problem, the user has to input certain parameters based on which the solver operates. The tasks of the user followed by the functions of the solver are listed as under:
- Identify the goal (objective) of the problem–This tells the solver what has to be done, thereby lending a direction to the solver.
- Choose the variables of the problem–This tells the solver the areas it has to work upon. The solver in excel returns the most appropriate values of the variables that meet the goal.
- Set the constraints of the problem–This tells the solver to work within the limits defined by the constraints. The solver returns a solution that meets the goal and, at the same time, does not go beyond these limitations.
Hence, the entire solution is focused on the goal of the problem. The solver returns the best possible solution for a problem keeping in mind the set of controls.
How to add “Solver Add-In” in Excel?
The “solver add-in” is not enabled by default in Excel. The two ways to activate the “add-in” are stated as follows:
a. With the “add-ins” option of the File menu
b. With the Developer tabDeveloper TabEnabling the developer tab in excel can help the user perform various functions for VBA, Macros and Add-ins like importing and exporting XML, designing forms, etc. This tab is disabled by default on excel; thus, the user needs to enable it first from the options menu.read more
a. The steps under “add-ins” option of the File menu are listed as follows:
- Click on the File menu.
- Select “options” from the various options listed in the menu.
- Click on “add-ins.” Select “solver add-in” and click “go.”
- Select the checkbox for “solver add-in” and click “Ok.”
b. The steps under the Developer tab option are listed as follows:
- In the Developer tab, click on “Excel add-ins” in the “add-ins” group. Select the checkbox “solver add-in” and click “Ok.”
- Once installed, the “solver” button appears in the “analysis” group of the Data tab.
The Terminologies of the Solver in Excel
The solver model consists of the objective cells, variable cells, constraints, and formulas that interconnect these components. The excel solver finds an optimal solution to the formula mentioned in the objective cell by changing the variable data cells, given the restrictions of the constraint cells.
Let us understand the terminologies used in the “solver parameters” dialog box. This will be helpful when running the solver. The terms are defined as follows:
- Objective cell: This is a single cell which contains a formula. The formula is decision-based and subject to the restrictions of the constraint cells. The value of the objective cell can be minimized, maximized, or set equal to the specified target value.
- Decision variable or variable cells: These are the cells in which the solver returns the values based on the objective of the problem. Since these data cells are variable, they can be changed with respect to the objective. In Excel, up to 200 variable cells can be specified.
- Constraint cells: These are the limitations within which the given problem is solved. In other words, they are the conditions that should be satisfied.
The Types of Solving Methods
The three methods to solve the data in the excel solver are listed as follows:
- GRG nonlinear
The acronym “GRG” stands for “generalized reduced gradient nonlinear.” This method helps solve nonlinear problems. A nonlinear problem may have more than one feasible region. Alternatively, it may have a set of similar values for the variable cells with all the constraints being satisfied.
2. Simplex LP
The acronym “LP” stands for linear problems. This method helps solve linear programming problems and works faster than the GRG nonlinear method. In a linear programming problem, a single objective has to be maximized or minimized subject to certain conditions.
The simplex LP and GRG nonlinear method both are used for smooth problems.
3. Evolutionary
This method helps solve non-smooth problems, which consist of discontinuous functions. The non-smooth problems are the most challenging optimization models to solve.
How to use the Solver in Excel?
Let us consider some examples to understand the solver tool of Excel.
Example #1
We want to find the value of X and Y in the following arithmetic equation with the help of the solver tool.
56=2X-5+Y2
Step 1: Formulate the equation on an Excel sheet, as shown in the following image.
Step 2: Click the “solver” button in the “analysis” group of the Data tab. The “solver parameters” dialog box opens. Enter cell “$D$9” in the objective cell, as shown in the succeeding image. The variable cells are “$C$7:$D$7.”
In the “value of” box, type 56. This is because the right-hand side value of the given equation should be equal to 56.
Step 3: Select GRG nonlinear in the “select a solving method” box. Click “solve.”
Note: The GRG nonlinear is the default solving method.
Step 4: The “solver results” box appears. This box may take some time to display depending on the complexity of the model. Select “keep solver solution” and click “Ok,” as shown in the following image.
Step 5: The results are shown in the following image. The solver has calculated X as 30.5 and Y as 0. The right-hand and left-hand sides both are equal now.
Example #2
The succeeding image provides the details of an organization that manufactures a television set, stereo, and speaker. The raw materials used are chassis, picture tube, speaker cone, power supply, and electronics. The leftover stock of raw materials is given in G10:G14.
We want to maximize the overall profits given the limited availability of the raw materials.
The variable cells, constraint cells, and the objective cell are highlighted.
Step 1: Calculate the “used inventory” in F10:F14. The formula is shown in the following image.
Step 2: Click the “solver” button in the “analysis” group of the Data tab. In the “solver parameters” dialog box, click “add” to the right of the “subject to the constraints” box.
Step 3: The “add constraint” box appears, as shown in the succeeding image. The constraints are defined as follows:
- The leftover stock of raw materials should be greater than or equal to zero. So, enter “$G$10:$G$14” in the “cell referenceCell ReferenceCell reference in excel is referring the other cells to a cell to use its values or properties. For instance, if we have data in cell A2 and want to use that in cell A1, use =A2 in cell A1, and this will copy the A2 value in A1.read more” box. In the box to the right, type the comparison operator “>=.” In the “constraint” box, enter “0.”
- The quantity of the three products (television set, stereo, and speaker) should be greater than or equal to zero. So, enter “$C$6:$E$6>=0” as done for the preceding point.
After entering every constraint, click the “add” button to add it to the “solver parameters” box. Once all constraints have been entered, click “Ok.”
Step 4: In the “solver parameters” box, enter “$C$18” in the “set objective” box. Select the “max” option. In the “by changing variable cells” box, enter “$C$6:$E$6.” Select the solving method as GRG nonlinear. Click “solve.”
Step 5: The output is shown in the following image. The organization must produce 250 television sets, 50 stereos, and 50 speakers to make a profit of $28,000. The product-wise profits are also displayed in C17:E17.
This is an optimal solution given the constraints. With these numbers, the organization will be able to utilize its resources efficiently.
The Applications of the Solver Excel Model
The major applications of the excel solver are listed as follows:
- It helps take decisions at the top level of management. For instance, an organization may want to maximize the profits and minimize the costs given the limited resources of inventory and manpower.
- It helps solve the day-to-day problems of life which can be converted into arithmetic equations. For instance, a housewife may require an optimal monthly budget to be created which can meet the family’s financial needs, given the limitations.
Frequently Asked Questions
The solver can solve optimization problems, arithmetic equations, and linear and nonlinear programming models. It helps minimize or maximize the output based on the associated variables and the given constraints.
The solver tool consists of three components which are stated as follows:
• Objective cell – This contains a formula which is to be solved.
• Variable cells – These cells contain variable data which can be adjusted to attain the objective.
• Constraint cells – These are the conditions to be met by the solution of the problem.
The solver solves the problems with the help of three methods GRG nonlinear, simplex LP, and evolutionary. The first two methods are used for smooth problems, while the last helps solve non-smooth problems.
The objectives of using the solver are listed as follows:
• To find an optimal solution to a problem
• To help minimize or maximize the formula value
• To work on variable data provided the constraints are adhered to
• To perform a comprehensive analysis necessary for making decisions
The “solver parameters” is a dialog box which appears on clicking the “solver” button in the Data tab. It consists of the following components:
• “Set objective” – This is the objective cell categorized into “max,” “min,” and “value of.” The “max” and “min” help maximize or minimize the formula value respectively. The “value of” category helps set the desired target value.
• “By changing variable cells” – This consists of a range of variable data cells that are changed to achieve the goal.
• “Subject to the constraints” – These are the constraints that can be added, modified, and deleted depending on the requirement.
• “Load/save” button – This saves the current model and displays the stored parameters.
• “Select a solving method” – This consists of the method to solve the problem. Any one of the three methods (GRG nonlinear, simplex LP, and evolutionary) can be selected.
• “Solve” button – This is clicked once all the data has been entered in the tool. It takes the user to the “solver results” dialog box.
Note: If the variable cells are non-adjacent, select the range by pressing and holding the “Ctrl” key.
Recommended Articles
This has been a step-by-step guide to the Solver in Excel. Here we discuss how to use Solver in Excel along with examples and downloadable Excel templates. You may also look at these useful functions in Excel –
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
58.6K subscribers