Random Numbers
Posted by Travis on January 28, 2002 9:59 AM
I'm trying to create a list of 10 random numbers from 1 to 10 without repeating any.
I am using the formula =ROUND(RAND()*10,0) to generate the numbers in each row, but I get several repeated values.
Is there a macro that could be written to check for duplicate values in the 10 cells, and if a duplicate is found then regenerate all the 10 numbers...looping until there are no duplicates.
Its going to be used for a super bowl pool. I realize I could draw out of a hat...but come on...thats no fun.
Thanks
Posted by Mark W. on January 28, 2002 10:02 AM
Just create the series 1 to 10 in cells A1:A10.
In cells B1:B10 enter the formula, =RAND(). Sort
this 2 column list using the random values in
column B. This will randomize the values in
column A.
Posted by Juan Pablo G. on January 28, 2002 11:17 AM
Another option
In A1:A10 put =RAND()
In B1:B10 put =RANK(A1,$A$1:$A$10)
Juan Pablo G.
Posted by Mark W. on January 28, 2002 12:09 PM
Yeah, the main difference between these 2 approaches
is that the 1st won't produce a new list each and
every time the worksheet is recalculated. =RAND()
is a volatile function.
