Random Distribution
 

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 via OfficeKB.com" wrote:

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

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).

--
Message posted via http://www.officekb.com

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


On Fri, 29 Apr 2005 12:44:32 GMT, "Rick via OfficeKB.com"
wrote:

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).