Microsoft Office Tutorials and References
In Depth Information
Circular Reference Examples
Figure 16-5: Using circular reference formulas to generate unique random integers in column A.
Each formula in column A contains a circular reference. The formula in cell A1, which was copied
down the column, is
=IF(OR(Dupes<>0,(AND(A1>=Lowest,A1<=Highest))),
RANDBETWEEN(Lowest,Highest),A1)
The formula examines the value of the Dupes cell; if this value does not equal 0 — or, if the value
in the cell is not between Lowest and Highest — a new random integer generates. When Dupes
equals zero, all cells in the RandomNumbers range are different, and they are all within the
specified value range.
Cell D17, which follows, contains a formula that displays the status. If the Dupes cell is not 0, the
formula displays the text CALC AGAIN (press F9 to perform more iterations). When the Dupes
cell is zero, the formula displays SOLUTION FOUND .
=IF(Dupes<>0,”CALC AGAIN”,”SOLUTION FOUND”)
To generate a new set of random integers, press F9. The number of calculations required
depends on
h The Maximum Iterations setting in the Formulas section of the Excel Options dialog box.
If you specify a higher number of iterations, you have a better chance of finding unique
values.
h The number of possible values (specified in the Lowest and Highest cells). Fewer
calculations are required if, for example, you request the 15 unique values from a pool of 1,000,
compared to a pool of 100.

Search JabSto ::

Custom Search