Calc Guide

Using the Solver
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, less than, or equal to the figure you enter. If you want the argument to remain
unchanged, you must enter a rule that specifically states that the cell 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 Figure 253).
• In cell B4, enter the formula B2+B3 as the total interest earned.
256 OpenOffice.org 3.3 Calc Guide