Finding optimum solutions for linear and nonlinear problems
You can use Quattro Pro to find optimum solutions for linear and nonlinear problems. For example, you can
|
•
|
|
evaluate formulas with one or more variables
|
|
•
|
|
solve sets of linear and nonlinear equations and inequalities
|
|
•
|
|
find a minimum or maximum solution, or meet an exact goal
|
|
•
|
|
find values that satisfy problem limits, or constraints
|
If you are solving a set of expressions, you can add them as constraints. Once you define a problem you can save the settings for future use.
Producing an Optimizer report
With Quattro Pro, you can produce an Optimizer report that shows how well the constraints were met and indicates starting and final values for the solution cell; starting and final values for the variable cells; and value, binding, slack, and dual value for the constraints.
Slack is the difference between the constraint constant value, or Right Value, and the value actually used in the solution.
Variable dual values are sometimes called reduced gradients. They show how a one-unit increase in the variable affects the solution cell.
Constraint dual values, or Lagrange Multipliers, measure the amount by which the solution could be improved if the constraint were relaxed by one unit.
Increment for variable cells is the amount of increase in the gradient value before a different value is calculated for that variable cell in the solution. For constraint cells, increment is the amount of increase in the right value before the binding status changes.
Decrement for variable cells is the amount of decrease in the gradient value before a different value is calculated for that variable cell in the solution. For constraint cells, decrement is the amount of decrease in the right value before the binding status changes.
To solve a problem |  |
|
1.
|
|
Type a single formula into a cell.
|
|
The formula must reference any variable cells you have assigned.
|
|
2.
|
|
Click Tools
 Numeric tools Optimizer. |
|
If you have a goal-seeking problem with one formula, click the Range picker
 in the Solution cell box, and select the cells that contain the formula. |
|
3.
|
|
Enable one of the following options:
|
|
•
|
None — chooses a default solution cell
|
|
•
|
Max — maximizes the formula
|
|
•
|
Min — minimizes the formula
|
|
•
|
Target value — makes the solution cell formula evaluate to an exact value
|
|
4.
|
|
Click the Range picker
in the Variable cell(s) box, and select the variable cells. |
|
•
|
|
You can reference up to 200 variable cells.
|
|
•
|
|
Variable cells must not contain dates, formulas, or text, and they must not be protected.
|
To specify the Optimizer options | |
|
1.
|
|
Click Tools
Numeric tools Optimizer. |
|
2.
|
|
Click Options.
|
|
3.
|
|
In the Optimizer options dialog box, modify any options.
|
|
•
|
|
For more information on Optimizer options, see “Specifying the Optimizer tool options.”
|
To add an Optimizer constraint | |
|
1.
|
|
Click Tools
Numeric tools Optimizer. |
|
2.
|
|
Click Add.
|
|
4.
|
|
Enable the operator option that connects both terms of the constraint and describes their relationship.
|
|
5.
|
|
Click the Range picker
in the Constant box, and select a cell that contains a value, or type a value for the target or bound. |
|
•
|
|
To edit a constraint, select it in the Optimizer dialog box, and click Change.
|
|
•
|
|
To delete a constraint, select it in the Optimizer dialog box, and click Delete.
|
To save an Optimizer model | |
|
1.
|
|
Click Tools
Numeric tools Optimizer. |
|
2.
|
|
Click Options.
|
|
3.
|
|
In the Optimizer options dialog box, click Save model.
|
|
4.
|
|
In the Save optimizer model dialog box, click the Range picker
in the Model cells box, and select the cells to hold the model. |
|
•
|
|
The cells must be three columns wide, with enough rows to hold the model. You can select the cell in the upper left corner of the cells.
|
|
•
|
|
The last Optimizer problem you defined is always saved with the note.
|
To reuse an Optimizer model | |
|
1.
|
|
Click Tools
Numeric tools Optimizer. |
|
2.
|
|
Click Options.
|
|
3.
|
|
In the Optimizer options dialog box, click Load model.
|
|
4.
|
|
In the Load optimizer model dialog box, click the Range picker
in the Model cells box, and select the cells that contain the model. |
To produce an Optimizer report | |
|
1.
|
|
Click Tools
Numeric tools Optimizer. |
|
2.
|
|
Click Options.
|
|
3.
|
|
In the Optimizer options dialog box, click Reporting.
|
|
4.
|
|
In the Report output cells dialog box, click the Range picker
in the Answer report cells box, and select the cells. |
|
5.
|
|
Click the Range picker
in the Detail report cells box, and select the cells. |
|
6.
|
|
Click OK.
|
|
7.
|
|
Click OK.
|
|
8.
|
|
Click Solve.
|