Go Back   TechArena Community > Software > Tips & Tweaks
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 25-09-2009
Member
 
Join Date: May 2008
Posts: 176
How to use Solver in Excel 2007

It is sometimes necessary to ask before its spreadsheet "what should be inside the cell xxx when my formula reaches the value yyy?". It is the role of the Excel Solver. Let us all how to take part.

When you use your spreadsheet, you create formulas that exploit the contents of several cells. Can you change the content of these cells and you look at the consequences of your changes. But it is sometimes necessary to take the problem in reverse. In other words, to ask: "what should be inside the cell xxx when my formula reaches the value yyy?". It is the role of the Excel Solver. With this tool, you specify a formula and tell you what value it should achieve. You then choose the cells that can vary, add, if any, constraints, eg "contents of A1 must be less than 50". Excel then looks for solutions of this equation (or inequality) and displays them if they exist.

To illustrate the operation of the solver, we take the example shown and tries to calculate its earnings for the first three months of the year. For each month, the table identifies the number of hours of rental and hourly rates, these data vary each month. In the table, only the cells on an orange background are modifiable, others resulting formulas.

Use the Target Value

In first example, we do not change a single cell. For example, we want the grand total rental (cell E4 in our example) reaches Rs. 1704000. For this, we ask how we should determine the tariff schedule March (cell D3), all other cells retain their current value. We're going to do this, use a version of "light" Solver: the function Value Target. Unlike the solver, this function requires no installation as described in the following paragraph. Select the Data menu, click Analysis of scenarios and choose the target value. In the field cell to define type E4 (this must be the address of a cell containing a formula). In the Value field to reach, type 1704000. Finally, in the field cell to change, type D3 and click OK. (D3 will probably be replaced by $ D $ 3, ignore this change). Target function value informs you when it has found a solution. Click OK. The value sought (3.8383 ... rounded to 272.64) appears D3

Install the add-Solver

For permit multiple cells to vary simultaneously, use the Solver. This is not enabled by default with installation of Excel. To install, click the Office button and choose Excel Options. Click Add-Ins on the Tools menu, and then select the Solver Add-in check box. Click OK, and Excel will install the Solver. Once the add-in is installed, you can run Solver by clicking Solver on the Tools menu.

Add a condition

We now impose an additional constraint: we always obtain a total of Rs. 1704000, but requires that the number of hours of rental, in March, (cell D2) does not exceed 3000 to allow maintenance of bicycles. We look how many hours of rental (cells B2 to D2) we have tabulated for achieving this goal. To start the solver, select the Data menu and click on Solver. Enter the target value set (by E4), select Value and enter 1704000 in the next field. Cells in the field variables, type B2: D2. To add a constraint, click Add. Modify the lists that appear to get D2 <= 3000 and click Add. Click Resolve. If the solver displays he has found a solution, select Keep Solver Solution and click OK. If, however, no solution exists, given your constraints, Excel warns you with a message. Then check Restore original values and click OK.
Reply With Quote
  #2  
Old 18-05-2011
Member
 
Join Date: Mar 2010
Posts: 310
Re: How to use Solver in Excel 2007

Hello its really nice explanation. Microsoft Excel has been a leader in spreadsheet productivity software platform. Almost everyone uses Excel for their roles in basic spreadsheet. However, Microsoft Excel has a complement of extensions that further enhance its power and productivity. One of these supplements is Microsoft Excel Solver. Solver can solve the programming, budgeting and management problems of capital assets.
Reply With Quote
Reply

  TechArena Community > Software > Tips & Tweaks
Tags: , , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "How to use Solver in Excel 2007"
Thread Thread Starter Forum Replies Last Post
Excel Files - Funcres.xla and solver.xla Missing Error Forrest Ranger MS Office Support 2 17-02-2012 02:04 PM
Excel 2007 file fails to get permission to open in Excel 2011 Raju Chacha Windows Software 6 13-01-2012 09:17 PM
How to use excel solver to solve linear equation in Excel Chini mao Windows Software 1 08-01-2012 02:34 PM
What is wrong with Data Analysis Toolpak or Solver into the Excel of Mirosoft Office 2011 of Mac 2011? Borislav Windows Software 8 12-09-2011 10:45 PM
How to check data validation compatibility of excel 2010 on excel 2007 Zoello Windows Software 6 17-05-2011 10:00 PM


All times are GMT +5.5. The time now is 05:37 AM.