Solver Tool in Excel (Table of Contents)
Solver in Excel
Have you ever heard of the word SOLVER in Excel?
If your answer is NO then this is the perfect article for you to learn the concept of SOLVER in excel.
A solver is nothing but the tool that solves the problem for you. SOLVER can perform complex modeling that we struggle to manually.
For example, if you have an equation X + Y = 50. We need to identify what is X & what is Y.
In this case it X could be 25 and Y could be 25. This is straightforward. However, what if X should be a positive integer, X should be greater than equal to 30, Y should be a positive integer, and Y should be greater than or equal to 25.
Can we solve this equation manually? I think it will take a lot of time to do permutation and combinations.
However, excel SOLVER tool can solve this equation in a matter of seconds.
Where to find SOLVER in Excel?
Excel SOLVER tool is located under Data Tab > Analysis Pack > Solver.
If you are not able to see the SOLVER tool in your excel follow below steps to enable this option in your excel.
Step 1: Firstly go to File and Options at the left-hand side of the excel.
Step 2: Select the Add-Ins after Options
Step 3: At the bottom, you will see Excel Add-ins, select that, and click on Go…
Step 4: Select Solver Add-in and click OK.
Step 5: This will enable the SOLVER Add-in Option for you.
How to use Solver in Excel?
A solver tool is very simple to use. Let us now see how to use the Solver tool in Excel with the help of some examples.
Example #1
As I have explained at the start we will do the X + Y = 50 calculation to start our SOLVER journey in Excel.
Goal: X + Y = 50
Conditions:
- X should be a positive integer value
- X should be >= 30
- Y should be a positive integer value
- Y should be >= 25
Step 1: Write a simple equation in excel sheet.
I have mention X & Y as variables. As a dummy data, I have mentioned 1 for both X & Y variables. SUM function adds those two cell values and gives the sum.
Step 2: Go to Data tab > Solver
Step 3: Once you click on Solver, it will open the below dialogue box. Here we need to set our objective, give many criteria’s and solve the problem.
Step 4: In the Set Objective give a link to the cell that we want to change. In this example, the cell we want to change is the cell B3
Step 5: In the To: section select Value of: and type 50 as the value. In this case, X + Y should be equal to 50.
Step 6: Now in By Changing Variable Cells: select the cells you want to change the values to get the sum of 50. In this example, we need to change the variables X & Y and these cell values are in B1:B2.
Step 7: Now comes the criteria part. Remember our criteria’s initially we stated. Click on ADD option in the Solver dialogue box.
Step 8: Once you click on the ADD item it will open the below dialogue box. In this box, we need to mention our first criteria.
Our first criterion is X should be greater than equal to 30.
Once you set, the criteria, click on Add. It will add the criterion to the solver box, the current values will be stored, and the same box will once again show up with no values.
Step 9: In the same box give the second criteria. The second criterion is X should be an integer value. Click on Add button.
Step 10: Similarly give criteria’s to second variable Y. For this variable criteria is it should be greater than equal to 25 and should be an integer. click on Add button.
Step 11: Give the second criteria for variable Y.
Step 12: Click on OK button.
You will see all the variables in the SOLVER box.
Step 13: Now click on the SOLVE button, which is located at the bottom of the box.
Step 14: Once the SOLVE button has clicked excel will solve the problem, based on the criterion you have given. (Excel will take some 15 seconds to run it).
Step 15: Click on OK. This dialogue box will be removed.
Therefore, X value is 30 and Y value is 25 to get the total of 55.
In this way, we use SOLVER to solve our problems.
Example #2
I will demonstrate one more example to understand better.
I have Units produced, basic unit per price, total cost involved, and the profit value.
By selling 1500 units at a basic rate of 15 per unit, I will earn 2150 as profit. However, I want to earn a minimum profit of 7500 by increasing the unit price.
Problem: How much should I increase the unit price to earn the profit of 7500?
Step 1: Open Excel SOLVER tool.
Step 2: Set the objective cell as B8 and the value of 7500 and by changing the cell to B3.
Step 3: I do not have such criteria to be satisfied to increase the unit price. So, I am not giving any kind of criteria’s. Click on the SOLVE button.
Step 4: In order to earn a profit of 7500 I must sell at 19 per unit instead of 15 per unit.
Things to Remember About Solver in Excel
- SOLVER is the tool to solve your problem.
- It works similar to Goal Seek tool in excel.
- You can give 6 kinds of criteria’s. >=, <=, =, integer, binary, difference
- First, you need to identify the problem and criteria’s associated with it.
Recommended Articles
This has been a guide to Excel Solver tool. Here we discuss how to use Solver tool in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
