Difference between revisions of "Documentation/OOo3 User Guides/Calc Guide/Solver"

Latest revision as of 21:00, 6 July 2018

Tools > Solver amounts to a more elaborate form of Goal Seek. The difference is that the Solver deals with equations with multiple unknown variables. It is specifically designed to minimize or maximize the result according to a set of rules that you define.

Each of these rules defines whether an argument in the formula should be greater than, lesser than, or equal to the figure you enter. If you want the argument to remain unchanged, you enter a rule that the cell that contains it should be equal to its current entry. For arguments that you would like to change, you need to add two rules to define a range of possible values: the limiting conditions. For example, you can set the constraint that one of the variables or cells must not be bigger than another variable, or not bigger than a given value. You can also define the constraint that one or more variables must be integers (values without decimals), or binary values (where only 0 and 1 are allowed).

Once you have finished setting up the rules, click the Solve button to begin the automatic process of adjusting values and calculating results. Depending on the complexity of the task, this may take some time..

Solver example

Let's say you have $10,000 that you want to invest in two mutual funds for one year. Fund X is a low risk fund with 8% interest rate and Fund Y is a higher risk fund with 12% interest rate. How much money should be invested in each fund to earn a total interest of $1000?

To find the answer using Solver:

1. Enter labels and data:
   • Row labels: Fund X, Fund Y, and total, in cells A2 thru A4.
   • Column labels: interest earned, amount invested, interest rate, and time period, in cells B1 thru E1.
   • Interest rates: 8 and 12, in cells D2 and D3.
   • Time period: 1, in cells E2 and E3.
   • Total amount invested: 10000, in cell C4.
2. Enter an arbitrary value (0 or leave blank) in cell C2 as amount invested in Fund X.
3. Enter formulas:
   • In cell C3, enter the formula C4-C2 (total amount - amount invested in Fund X) as the amount invested in Fund Y.
   • In cells B2 and B3, enter the formula for calculating the interest earned (see below).
   • In cell B4, enter the formula B2+B3 as the total interest earned.
   

   

   Example setup for solver
4. Choose Tools > Solver. The solver dialog opens.
   

   

   The Solver dialog
5. Click in the Target cell field. In the sheet, click in the cell that contains the target value. In this example it is cell B4 containing total interest value.
6. Select Value of and enter 1000 in the field next to it. In this example, the target cell value is 1000 because your target is a total interest earned of $1000. Select Maximum or Minimum if the target cell value needs to be one of those extremes.
7. Click in the By changing cells field and click on cell C2 in the sheet. In this example, you need to find the amount invested in Fund X (cell C2).
8. Enter limiting conditions for the variables by selecting the Cell reference, Operator and Value fields. In this example, the amount invested in Fund X (cell C2) should not be greater than the total amount available (cell C4) and should not be less than 0.
9. Click OK. A dialog appears informing you that the Solving successfully finished. Click Keep Result to enter the result in the cell with the variable value. The result is shown below.
   

   

   Result of Solver operation

   The default solver supports only linear equations. For nonlinear programming requirements, try the EuroOffice Solver or Sun’s Solver for Nonlinear Programming [Beta]. Both are available from the OpenOffice.org extensions repository. (For more about extensions, see Setting up and Customizing Calc

   
   

   Content on this page is licensed under the Creative Common Attribution 3.0 license (CC-BY).