Phenquestions
Microsoft Excel is a great Office application from Microsoft and it does not need any introduction. It helps every one of us, in many ways by making our tasks simpler. In this post we will see how to solve Equations in Excel, using Solver Add-in.
Some or the other day, you might have come across the need to carry out reverse calculations. For example, you might need to calculate values of two variables which satisfy the given two equations. You will try to figure out the values of variables satisfying the equations. Another example would be - the exact marks needed in the last semester to complete your graduation. So, we have total marks needed to complete the graduation and the sum of all marks of the previous semesters. We use these inputs and perform some mathematical calculations to figure out the exact marks needed in the last semester. This entire process and calculations can be simple and easily made with the help of Excel using Solver Add-in.
Solve Equations in Excel
Solver Add-in powerful and useful tool of Excel which performs calculations to give the optimal solutions meeting the specified criteria. So, let us see how to use Solver Add-in for Excel. Solver Add-in is not loaded in to excel by default and we need to load it as follows,
Open Excel and click on File or Office Button, then click on Excel Options.
Excel Options dialog box opens up and click on Add-ins on the left side. Then, select Solver Add-in from the list and Click on “Go” button.
Add-ins dialog box shows list of add-ins. Select the Solver Add-in and click “Ok” button.
Now, Solver Add-in got added to the Excel sheet. Tap on the “Data” tab and on the extreme right, you can see the added Solver Add-in.
How to use Solver Add-in
We added Solver Add-in to Excel and now we will see how to use it. To understand it better, let us take an example of calculating the profit of a product. See the Excel sheet below with some sample data in it. To find the profit %, we use the formula profit %=(( Selling price-Cost price)/Cost price)*100
We can see that there are three products as Product A, Product B and Product C with Cost Price, Selling Price and Profit (%) of respective products. Now, our target is to take the profit (%) of Product A to 20%. We need to find out the Cost Price and Selling Price values of Product A needed to make the profit as 20%. Here, we also have the constraint that Cost Price should be greater than or equal to 16,000 and Selling Price should be less than or equal top 22,000. So, first we need to list down the below information based on the example we took.
Target Cell: B5 (Profit %)
Variable Cells for Product A: B3 (Cost Price) and B4 (Selling Price)
Constraints: B3 >= 16,000 and B4 <= 22,000
Formula used to calculate profit %: ((Selling price-Cost price)/Cost price)*100
Target Value: 20
Place the formula in the target cell (B5) to calculate the profit %.
This is the required information we need to solve any sort of equation using Solver Add-in in Excel.
Now, launch the Solver Add-in by clicking on the Data tab and click on Solver.
STEP 1: Specify the “Target Cell” as B5, “Value of” as the targeted profit % as 20 and specify the cells which need to be changed to meet the required profit %. In our case, B3 (C.P) and B4 (S.P) need to be specified as $B$3:$B$4 in “By changing variable cells”.
STEP 2: Now, it's time to add constraints. In our case, Cost Price (B3) >=16,000 and Selling Price (B4) <=22,000. Click on the “Add” button and add constraints as follows.
STEP 3: Once you entered all the required data, click on the “Solve” button. It asks, whether you want to keep the solver solution along with some options. Select based on your requirement and click on “Ok” button.
Now, you will see that the latest Cost Price and Selling Price has been changed to 17, 708 and 21, 250 respectively to get the 20% Profit.
This is the way to use Solver Add-in to solve equations in Excel. Explore it and you can get more out of it. Share with us how best you made use of Solver Add-in.
