Taryn Nefdt
The solver tool is used to find an optimum value for a formula in one cell, by changing decision variables. While solving linear programming problems the solver tool is essential. By reading this article you will get to know how to use solver in Excel.
To deliver a solution, Solver needs a group of cells called the variable cells whose values are adjusted, to meet the target in the objective cell. These variable cells are subject to constraint criteria.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
How to Add Solver in Excel 2016 | 2019 | 2021 | 365
The Solver is a fantastic tool for Excel. It has analysis skills that can solve complex problems with given constraints. Depending on the problem type it finds the maximum or the minimum value of the given problem. Excel Solver is available from Excel 2007 to Excel 365 versions.
Solver is an Add-in of Excel. It does not usually exist in Excel. We need to add this tool manually. Have a look at the following process.
📌 Steps:
- Go to File>> Options first.
- The Excel Options window appears now.
- Select the Add-ins from the left side.
- Now, select Solver Add-in from the Add-ins on the right side.
- Then, click on the Go button.
- The Add-ins window appears.
- Check the Solver Add-in from the available add-ins.
- Finally, press the OKÂ button.
- We return to the worksheet now.
- Click on the Data tab.
We can see the Solver option on the right-most side.
Algorithm of Excel Solver
Excel Solver has many different algorithms for finding the solution to different complex linear and non-linear problems. We can choose any of the following methods in the Select a Solving Method box.
- GRG Nonlinear: For the smooth nonlinear problems Generalized Reduced Gradient Nonlinear algorithm is used where the data will contain a minimum of one constraint which will be a smooth nonlinear function.
- LP Simplex: The LP Simplex Solving method is used for solving a problem of Linear Programming which is characterized by linear relationships. With the linear relationship among the data, linear programming tends to achieve the desired goal such as minimum loss, maximum profit, etc.
- Evolutionary: It is the most difficult optimization problem where you can not determine the characteristics of the function. The direction of this kind of function can be increasing or decreasing. This is because the functions are either discontinuous or nonsmooth.
Components of Excel Solver Model
In this section, we will discuss the details of a solver model. A solver model has 3 parts.
- Objective Cell,
- Variable Cells,
- Constraints.
Objective Cell: The objective cell denotes a cell that contains a formula. This may be minimum, maximum, or a certain value.
Variable Cells: This is the range of cells that acts as the variables to solve a problem. We get the solution using the variable.
Constraints: Constraints are the conditions set by the user to solve a problem. Those restrictions are fixed by realizing the real-world situations.
Steps to Use Excel Solver Add-In (e.g. Maximizing Profit)
Now, we will show the use of Excel Solver. We will apply this solver to get the maximum profit within some constraints. Here, we have taken a dataset of a clothing store. Assume, that shop sells only 5 specific kinds of products. In the dataset, the cost, selling price, profit, and available units of each item are given. Also, we calculated the total profit by a simple sum function. But here, we want to get the maximum profit with some conditions. The first one is they will have a maximum of 90% and a minimum of 10 units of each item. There is a special case for Blazer that has a maximum sell unit of 20. And lastly, they will sell a maximum of 400 units of total items. Following those conditions, we want to calculate the maximum profit that will indicate the selling unit of each item specifically.
📌 Step 1: Access Solver Parameters Window
- First, click on the Data tab. Then, select the Solver option from the Analyze group.
- The Solver Parameters window appears.
📌 Step 2: Set Objective and Choose Variable Cells
- Choose the Cell where we want to show the calculated value.
- Then, choose the Max option.
- Now, click on the arrow of the By Changing Variable Cells option.
- Choose the range of the Unit column.
This range is the variable of this problem.
- Then, we will add the constraints. So, click on the Add option.
- The Add Constraint window appears.
📌 Step 3: Add Constraints and Choose Solving Method
- Put the Cell Reference, comparing operator and Constraint
- Then, press Add if we want to add a new constraint or OK if there are no more constraints.
- Here, we added all the constraints.
- Also, choose GRG Nonlinear as the solving method.
- Finally, press the Solve button.
- Solver Results window appears.
📌 Step 4: Choose Suitable Solver Results Format
- Choose the Keep Solver Solution option, it makes changes in the present worksheet.
- Click the Answer from the report section that will print a report of the Answer.
- Then, check the Return to Solver Parameters Dialog, it will return to the previous dialog box. If any changes are needed we do that from the dialog box.
- Lastly, press the OKÂ button.
- Go back to the worksheet now.
We can see some changes in the unit and total profit cells.
- There is an Answer Report tab added in the Excel file.
In a similar way, we can also get the minimum value like cost minimization by choosing Min from the Set Objective section
Conclusion
In this article, we described the details of the Excel Solver. Such as, how to add a solver, solution algorithms, model, and the use with an example. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.
