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.