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.