Random Distribution

[Rick via OfficeKB.com]

I need to randomly order numbers from a list or range of consecutive whole
numbers, such that each number is listed only once. Can this be done in
excel using functions, formulas or whatever? I want to incorporate it into
a spreadsheet with a macro to automatically generate the list and use it to
randomly designate selected entries.

 

[Guest]

I need to randomly order numbers from a list or range of consecutive whole
numbers, such that each number is listed only once. Can this be done in
excel using functions, formulas or whatever? I want to incorporate it into
a spreadsheet with a macro to automatically generate the list and use it to
randomly designate selected entries.

Rick

If your numbers are from 1 to n try the Rank function with rand(). Say F2:F5
have the function Rand(). In G2 enter =RANK(F2,$F$2:$F$5,0) and copy down.

Regards
Peter

 

[Rick via OfficeKB.com]

This solution doesn't seem to work - I can get it to produce a list of
random whole numbers ranging from 1 to 50 using the formula =rand()*(50-1)
+1, then rounding that to 0 decimals (if I just use rand() as suggested it
produces a list ranging between 0 and 1) but any given number may be
repeated multiple times and all numbers are not represented. Then
everytime I apply the next step it recalculates the random list. For
example, when I then enter the suggested formula in the adjacent column it
recalculates the random list. When I then copy the formula in the first
cell to the remaining cells in that column it again recalculateds the
random list again. When I then highilght the adjacent column and
copy/paste values (to remove the formula so I can sort in order) it again
recalculates the ramdom list. When I sort the adjacent column, which also
recalculates the list, it becomes apparent that all numbers in the range
are not represented and some are represented multiple times. Short version
- it doesn't work (unless I'm just not doing it right).

 

[Bob Tarburton]

Rick
To get random whole numbers 1 to 50 use
a1> =rand()
b1> =rank(a1,A$1:A$50,0)+countif(A$1:A1,A1)
c1> =rank(B1,B$1:B$50),0)

copy A1:C1 to A1:C50
Column C will give you distinct numbers from 1 to 50.

To avoid recalculation you have to copy C1:C50 and paste as values
somewhere.
Bob