How to use solver add-in to solve equations in Excel?

image_pdf

Explain Solver add-in in excel:

Have you done reverse calculation? I am sure you must have done it. For example, we all calculate passing mark requirement before final semester exam. What we do is to sum of first and second semester result and then subtract it from passing mark requirement. We do it many times in day to day life. Now EXCEL can helps us to do it in faster way using SOLVER ADD-IN!!

Excel provides tool called solver add-in which is used to find optimal solutions to various problems. It does reverse calculation and provide us best solution. SOLVER Add-in is very useful in solving equations.

First of all it is required to load solver add-in in excel. Following are the steps to be followed.

1. In Excel 2010, Go to File Tab -> click Options

Solver

2.In Excel Options Dialog box, click on Add-Ins option, select Solver Add-In and then click on Go button

solver1

3. In Add-in dialog box, click on SOLVER ADD-IN and click OK.

solver2

4.Solver Add-in has been loaded in your computer. Click on DATA tab and you can see at SOLVER at extreme right.

solver3

Using Solver add-in in excel:

Let us see an example to understand this .

Example:

Suppose we have the data shown below. We want to increase the profit and we want to find out what should be our new selling price in order to increase our profit. To solve this problem solver is used.

1. Formula to calculate profit %=((Selling price-Cost price)/Cost price)*100

solver8

See more: How to Provide proper cell reference in Excel?

2. Here we want to increase the profit of Fridge from 17.65 to 20.It means that our set_objective is $C$4 which is selling price of fridge. Provide set value in next Tab.

Then you need to specify criteria, based on which you would like to increase profit. Here we have two parameters: Cost price and selling price. Either we reduce our cost price or increase our selling price. So we have provided range $C$2:$C$3 in changing variables. You can also provide constraints, for example cost price should not be less than 17000 and selling price should not be greater than 20000.

solver5 

3. Based on our data, Solver Add-in will calculate cost price and selling price keeping profit as 20%. You get the best solution from SOLVER. Click OK to keep Solver Solution or Cancel to try another solution.

solver6

4. By clicking OK, we have Solver solution in Excel sheet.

solver7

Explore more, It's Amazing…

Use nested IF function to evaluate multiple condition

How to use SUM, SUMIF, SUMIFS functions in excel?

 

 

 

2,158 total views, 3 views today

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>