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 day, you might have encountered the need to carry out reverse calculations. For example, you might need to calculate the values of two variables that 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 the total marks needed to complete the graduation and the sum of all marks of the previous semesters. We use these inputs and perform mathematical calculations to determine the 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 using Solver Add-in
Solver Add-in is a 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 into 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, Product A, Product B, and Product C, with Cost Price, Selling Price, and Profit (%) of respective products. Now, our target is to take Product A’s profit (%) to 20%. We need to find out the Cost Price and Selling Price values of Product A needed to make a profit of 20%. Here, we also have the constraint that the Cost Price should be greater than or equal to 16,000, and the Selling Price should be less than or equal to 22,000. So, first, we need to list the information below 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.
How do you solve an equation using Excel Solver?
To solve an equation using Excel Solver, you can follow the above-mentioned steps. However, to use this add-in, you need to install it first. Following that, you can use and apply it to your Excel spreadsheet.
Read: Microsoft Power Query for Excel helps with data discovery
How do you use Excel Solver add-in?
To use the Solver add-in in Excel, you need to install it first. To do so, open the Options panel and go to the Add-ins section. Following that, find the Solver add-in and start the installation process. Once done, you can use his-in by following the aforementioned steps.
Random read: How to open a second instance of an application in Windows PC.
