Home → Techniques and Tips → Evolver and RISKOptimizer → Preventing Duplicates in Adjustable Cells
Applies to:
Evolver 4.x-7.x
RISKOptimizer in @RISK 5.x–7.x Industrial Edition
Evolver Developer Kit 4.1
RISKOptimizer Developer Kit 4.1
I have a set of adjustable cells that must vary as integers, but I need the values to be unique in each trial—there must be no duplicate values. How do I set up the constraint?
TIP: If the number of adjustable cells equals the number of possible values, so that each trial uses all possible values, all you have to do is choose the Order method in your model definition.
Set up a range of "helper" cells to count the duplicates. Then sum the helper cells and constrain the sum to equal zero.
Please download the attached example and use it to follow the technique below. The model is set up for Evolver or RISKOptimizer.
Details:
Let's suppose that the function to be maximized is in D14, and the adjustable cells are A14:A28. Try setting one of those values to match another one, and notice how the count of duplicates is updated automatically in the "helper" cells B14:B28. How is this accomplished?
In cell B14, type the formula
=COUNTIF($A$14:$A$28,A14)-1
The first argument is the range of adjustable cells, as an absolute reference with dollar signs. The second argument is the first adjustable cell, as a relative reference without dollar signs. Why subtract 1? The COUNTIF function counts all occurrences of the value in A14, including A14 itself. But we want the number of duplicates, which is one less than the number of occurrences.
Grab the fill handle at the lower right corner of B14, extend it through cell B28, and release the mouse button. Then click in one of the other cells, such as B21, and look at the formula. Notice that the first argument is the same because of the absolute reference, but the second argument has changed because of the relative reference.
You have now created the "helper" cells in column B. Each helper cell counts how many times the value to the left of it is duplicated. Sum them in B31 with an =SUM formula. (Try creating some duplicates in column A, to see how they are counted in column B. After experimenting, change the values so that there are no duplicates. It's good practice to start off Evolver or RISKOptimizer with a feasible solution, one where all constraints are met.)
Now look at the Evolver or RISKOptimizer settings. You see that A14:A28 are the adjustable cells, constrained to be integers 1 to 800. The total number of duplicates in B31 is set to equal 0 as a hard constraint. This makes it unnecessary to place constraints on cells B14:B28.
Close the Settings window and click the Start Optimization icon. You'll see the worksheet update quite rapidly as Evolver or RISKOptimizer finds the optimal solution while rejecting as invalid any solution with duplicates. When the optimization has converged, it will stop automatically and report the results.
(NOTE: For this small example, "Update Display" is checked in the Evolver and RISKOptimizer options. For greatest speed on larger models, you would not check this setting.)
Last edited: 2018-09-18