Configuring Adjustable Cell Ranges

Adjustable cell ranges are the Excel cells that RISKOptimizer will adjust in an attempt to meet the optimization objective. These are shown in the Adjustable Cell Ranges table.

Every adjustable range in the table belongs to a “group”. All the ranges in a group share a common solving method and any options associated with that solving method. By default, each adjustable cell range created is automatically added to a single group using the Recipe solving method, which is by far the most common choice. However, there are other options. To add a new group that uses a different solving method, click the Group button. Because the adjustable cells contain the decision variables of the problem, at least one group of adjustable cells must be defined to use RISKOptimizer. Many problems will contain only one group of adjustable cells, but more complex problems may require different blocks of variables to be solved with different solving methods simultaneously. This unique architecture allows for highly complex problems to be built from multiple groups of adjustable cells.

To add an Adjustable Cell Range, click the Add button to the right of the table. Select the cell or range of cells to be adjusted during the optimization process.

To delete an Adjustable Cell Range, highlight the range in the table by selecting the checkbox to the left of the range and click the Delete button.

Figure 1 - Adjustable Cell Ranges

After a range has been added, it can be configured. Each range in the list has a number of fields available (Figure 1, right):

Check boxes - Each adjustable cell range has a check box to its left. This box controls if RISKOptimizer is permitted to change the values in this range. By unchecking this box, that range will be frozen during the optimization process.
Range – The cell range that will be adjusted.
Minimum and Maximum (Recipe and Budget Solving Methods only) - These values are required. Specifies the range of acceptable values for each adjustable cell in the range. RISKOptimizer will not allow any values outside the specified ranges. A number, a cell, or a range of cells can be entered; a range with multiple cells will specify a different minimum (or maximum) for each of the cells in the adjustable range.
In cases where natural limits don’t exist (or aren’t known), try to specify limits wide enough to allow all reasonable solutions while keeping in mind that the tighter these limits, the quicker the optimization process.
Values (Recipe and Budget Solving Methods only) - Specifies the type of adjustable cell values RISKOptimizer will explore. In each of these settings, only values between the specified minimum and the maximum are included in the search. There are three settings available:
- Any - This is the default. All real numbers are included.
- Integer - Only integer values are included. This is usually chosen when non-integer values make no sense in the model.
- Discrete - With this setting, a step size must be specified; only multiples of this step size are included. This setting is useful when “rounded” values of the adjustable cells, such as multiples of $10, are fine for all practical purposes.