Microsoft Office Tutorials and References
In Depth Information
RANDOM NUMBERS WITHOUT DUPLICATES
In fi 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:
 
Search JabSto ::




Custom Search