Using Constraints

There are two types of constraints exist. Those that limit the range of values that GASolver can place into input cells and those that GASolver can allow to be produced into output cells. It is important to learn the difference between these two types as the right choice can speed up, or slow down, the processing speed dramatically.

GASolver works by creating, usually, large numbers of trial solutions that place values into your input cells. After each trial placing of numbers GASolver then recalculates your worksheet which places the output numbers into your output or result cells, along with any intermediate values cell you may use in calculation.

The entire process for a single solution depends to a large extent upon the time taken to recalculate the sheet. A large simulation will take a long time and a small problem will be far faster. GASolver creates its trial input values very quickly but Excel calculates complex sheets more slowly.

Constraining input cells usually means that GASolver has to create a few sets of trial input cell data to find one that meets all your input cell range requirements. This is a fast process compared with calculating the sheet itself. Having found an input set that meets all the required input cell range conditions GASolver will then calculate the sheet and when finished will check each cell in your list of output cell constraints. If a single cell is found that breaches the constraint you place upon that cell then GASolver will discard the entire solution, input cell values, calculated sheet values and all results or target cell values. Obviously if the sheet took several minutes to calculate then all that time is wasted.

Performance Point: Output cell constraints or Input cell limits? It is useful to appreciate the difference as limiting the input cell values is far faster than constraining the output cell values. Although there is no technical reason why you should not use 'output' constraints to limit input cell values we strongly recommend that, where possible, you use constraints only on output cells and limit input cell values by the method provided. See also Input Cell Conditioning

Choose your constraints carefully and ensure that a valid value can exist. GASolver checks that values conform to constraints only after the entire sheet has been recalculated. If any of the values in the new solution are found to have breached any of the constraints that you have set for them then the entire solution is discarded and a new one started. Thus, if your sheet is complex and recalculation takes a long time then setting too tight or too many output cell constraints will dramatically slow down the search for a solution. The worst case example would be if you wanted a particular cell to contain only a positive value but your sheet only ever placed a negative value in that cell whereupon GASolver would create an infinite number of possible solutions, recalculate the entire sheet for each one and then discard them all because the constrained cell would never contain a positive value.

See Adding Constraints for the mechanical details of including constraints into your model.

Output Cell Constraint Examples

If your model uses both positive and negative input cell values then it may be that the model math will produce both positive and negative output cell value results. If this is coupled with the goal of setting the target cell to the maximum available value then GASolver will look for the largest number available irrespective of whether its sign is positive or negative.

To ensure that the target cell value is positive:

Add a new constraint.

Select the cell reference you require to be positive.
Select the to be type to be Greater than constraint type.
Click the Value option.
Enter 0 as the value.

Now cell B12 must be greater than 0 for a solution to be kept.

To ensure that one input / changeable cell value is less than a specified value:

Add a new constraint.

Select the cell reference you require to be positive.
Select the Less than constraint type.
Click the Value option.
Enter 123 as the value.

Now cell B14 must be less than 123 for a solution to be kept.

Note that Excel treats -123 as less than 123, i.e. negative values are less than positive values.

To ensure that one input / changeable cell value is not equal to than another input / changeable cell value:

Add a new constraint.

Select the cell reference whose value you wish to constrain.
Select the Not equal to constraint type.
Click the Cell Reference option.
Select the cell reference of the constraint value.

Now cell B3 value must be dissimilar to the cell B4 value for a solution to be valid.

Combining Constraints

Several separate constraints may be applied to target one cell. This will have the effect of combining constraints to achieve a composite constraint on the same cell. Take care not to apply constraints such that they leave no room for valid values though!