Excel 2003: random (1 to 24) to get 24 results without repeat?

  • Thread starter Thread starter hairysairy2
  • Start date Start date
H

hairysairy2

i need to get 24 results in random order without repeat. "randbetween" wont
entirely work as only once every few hundred calculations will there be no
repeats and i eventually require many thousands of sets of randomised 24
results. please help
 
You dont say in what rang so here's a simple method for 24 numbers in the
range 1 - 65536

A1=1
a2=2
Select both cells and tap F5
in the goto box type
a1:a65536
Edit|Fill|Series and click Ok
Column A should fill with the numbers 1 - 65536
in B1 enter
=rand()
Double click the fill handle to fill down to B65536
Select both columns and sort on column B
Use the the top 24 numbers
Tap F9 and sort again for another 24

Mike
 
Look here:

http://www.mcgimpsey.com/excel/udfs/samplenoreplace.html

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|i need to get 24 results in random order without repeat. "randbetween" wont
| entirely work as only once every few hundred calculations will there be no
| repeats and i eventually require many thousands of sets of randomised 24
| results. please help
 
hairysairy2 said:
i need to get 24 results in random order without repeat. "randbetween" wont
entirely work as only once every few hundred calculations will there be no
repeats and i eventually require many thousands of sets of randomised 24
results. please help
Click to expand...

Your subject line states that you want the results from the range of 1 to
24. One approach:

Put =RAND() into A1 and copy down through A24. Then put the following
formula into B1 and copy down through B24:

=rank(A1, $A$1:$A$24)

Of course, you can use any two 24-row ranges anywhere. They do not have to
start in the same row, as my A1:A24 and B1:B24 do. But if you are generating
24 random numbers in a row (e.g. A2:X2), put the =RAND() formulas into a row
of 24 cells instead of a column.

PS: For future postings, be sure to duplicate all important information in
the subject line into the text body of your posting. As you can see from
some responses, some people do not read, internalize or remember the details
in the subject line when they read a posting. Usually, I am one of them ;-).
 
Back
Top