How to solve Equations in Excel using Solver Add-in

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

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.

Solver Add-in

Add-ins dialog box shows list of add-ins. Select the Solver Add-in and click “Ok” button.

Solver added to Excel

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.

Solver in Data Tab

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

solve Equations in Excel using Solver Add-in

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”.

Enter 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.

add constraints

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.

Keep solver settings

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.

Final Values

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.

Download this VPN to secure all your Windows devices and browse anonymously
Posted by on , in Category Office with Tags
Sridhar is a Software Engineer who loves to stay updated with developments in the world of technology. He is fond of writing everything related to Internet, Computers and Mobile and Desktop Operating Systems.

One Comment

  1. Shaun McDonough

    Thanks for the tutorial. Worked like a charm

Leave a Reply

Your email address will not be published. Required fields are marked *

6 + 3 =