Applies to:
@RISK Industrial Edition 6.x, 7.x, 8.x
Evolver 4.x–8.x
RISKOptimizer 1.x, 5.x
Evolver Developer Kit (EDK) 4.1
RISKOptimizer Developer Kit (RODK) 4.1

Solving for Multiple Goals in Evolver and RISKOptimizer

The Evolver and RISKOptimizer dialogs allow only one target cell at a time, but you can solve for multiple goals by combining them into a single formula.

Example – Maximizing/Minimizing Two Cells:

• Put the sum of the two cells in a third cell and maximize or minimize that cell.

• To get two cells as close as possible to zero, use the sum of their absolute values in a third cell and minimize it.

Weighted Goals:

• If some goals are more important than others, apply weights.

• Example: Minimize abs(K72)*10 + abs(K71) if K72 is ten times more important than K71.

Sample Workbook:

• Shows a goal of getting two numbers (purple) close to target numbers (green).

• Discrepancies are in I17:I18; the single goal cell in I22 (sum of abs(F17) and abs(F18)) is minimized.

• Targets (green) and adjustable cells (red) are editable.

• Protection prevents accidental formula changes. Remove protection via:

  • Excel 2003: Tools » Protection » Unprotect Sheet

  • Excel 2007+: Right-click tab » Unprotect Sheet

Background Use Case:

• A user wanted a Beta distribution with a target mean and standard deviation, fixed min and max, and needed alpha1 and alpha2 parameters.

• By adjusting alpha1 and alpha2, the sum of discrepancies can be minimized to achieve the targets.

Efficient Frontier Analysis (Evolver/@RISK Industrial 7.0 and Up):

• Simplifies optimization with two competing goals.

• Choose one as the target and constrain the other to a limit.

• Software runs a sequence of optimizations, adjusting the constraint each time.

• Full examples and help documentation are included with @RISK Industrial or Evolver 7.0 and Up

 

Related Article:

Efficient Frontier Analysis

Attachments:
MultipleGoals.xls