Home → Techniques and Tips → Evolver and RISKOptimizer → Multiple Goals for Optimization
Applies to:
@RISK Industrial Edition 6.x, 7.x
Evolver 4.x–7.x
RISKOptimizer 1.x, 5.x
Evolver Developer Kit (EDK) 4.1
RISKOptimizer Developer Kit (RODK) 4.1
Evolver and RISKOptimizer let me specify just one goal, but I need to maximize or minimize multiple cells. What can I do?
The Evolver and RISKOptimizer settings dialogs let you specify only one cell as a target. But you can still solve for multiple goals by creating a function that combines two or more goals into one goal.
For example, if you want to maximize (minimize) two cells then you would put their sum as a formula in a third cell and maximize (minimize) that cell as a target. To get two cells as close as possible to zero, put the sum of their absolute values as a formula in a third cell, and minimize that.
If the goals don't have equal importance, you can attach weights to them. For example, if getting K72 close to zero is ten times as important as K71, your goal would be to minimize abs(K72)*10+abs(K71).
Sample workbook
The attached workbook shows a goal of getting two numbers (in purple) as close as possible to two target numbers (in green). That means the discrepancies, cells I17:I18, must be as close as possible to zero, so the single goal in I22, the sum of abs(F17) and abs(F18), must be minimized.
The example is set up to run as it is, but you can change things if you wish. The targets (green cells) and the starting values of the adjustable (red) cells are editable, and you can also change any of the Evolver or RISKOptimizer options.
The example is protected so that you don't accidentally overwrite any formulas. You can remove protection in Excel 2003 by clicking Tools » Protection » Unprotect Sheet, or in Excel 2007 and above by right-clicking the tab and selecting Unprotect Sheet.
(In case you're interested in the background for this example, a user had a target mean and standard deviation for a Beta distribution, with fixed min and max, and wanted to find the necessary alpha1 and alpha2 parameters for the distribution. The mean and standard deviation are easy to find from min, max, alpha1, and alpha2, so it was just a matter of working backward by adjusting alpha1 and alpha2 in the optimization.)
Efficient Frontier Analysis
Beginning with release 7.0, @RISK Industrial Edition's and Evolver's efficient frontier analysis can simplify optimization when you have two competing goals. You choose one of them as the target for an optimization and constrain the other to be no worse than a specified limit. Then the software performs a sequence of optimizations, each time changing the limit value for the constraint. There's a full description in the @RISK and Evolver help files, and examples are included when you install @RISK Industrial 7.0 or newer or Evolver 7.0 or newer.
Additional keywords: Multi-objective optimization, Multi-goal optimization, Multi-target optimization, Multiple objectives for optimization, Multiple targets for optimization
Last edited: 2017-04-06