Microsoft Office Tutorials and References

In Depth Information

**RANDOM NUMBERS WITHOUT DUPLICATES**

In ﬁ gure 51, the array formula in cell B9 is:

=SMALL(IF(COUNTIF(B$3:B8,ROW($1:$10))<>1,ROW($1:$10)),1+I

NT(RAND()*(10-ROW()+ROW(B$4))))

Excel starts evaluating this formula with the
COUNTIF
function.
ROW($1:$10)

generates an array of the numbers, 1, 2, 3,…10. The
COUNTIF
function tells

Excel to look through the numbers generated so far in B$3:B8 and count how

many are equal to 1. This answer is either going to be 1 or 0. Because 1

already occurs in the range, the
COUNTIF
will be 1. But, for the number 2, the

answer will be 0 because a 2 has not been chosen yet.

Figure 52 shows what is happening in the
IF(COUNTIF())
portion of the

formula:

Part

I

Figure 52.
Range E4:H14 shows how half the formula calculates cell B9.

●

●

Cells E5:E14 show the results of the ROW function, numbers 1 to 10.

Cells F5:F14 count how many times the number in E already appears in the

previous random numbers in B4:B8.

Cells G5:G14 check to see if the result in F is equal to 1.

The IF function in H says that if the value in G is
TRUE
, bring over the

number from E; otherwise, Excel puts in a FALSE.

At this point, the array action of the formula is complete. PGC01 has succeeded

in building an array of the available numbers. At this point, you simply have to

ask for one of the numbers from that remaining array. Basically, the formula is

then:

●

●