Can Excel pick random numbers from 1-300 and not repeat numbers?

G

Guest

I need a little programme to select randomly numbers from a list of eg. 1-300
and having selected numbers not to repeat them i.e. electronic drawing
numbers from a hat.
Any suggestions?

Julian Marcus
 
B

Bob Phillips

First, ensure cell A1 is empty and goto Tools>Options and on the Calculation
tab check the Iteration checkbox to stop the Circular Reference message.

Next, type this formula into cell B1
=IF(($A$1="")+(AND(B1>0,COUNTIF($B$1:$B$9,B1)=1)),B1,INT(RAND()*300+1))
it should show a 0

Copy B1 down to Bn.

Finally, put some value in A1, say an 'x', and all the random numbers will
be generated, and they won't change.

To force a re-calculation, clear cell A1, edit cell B1, don't change it,
just edit to reset to 0, copy B1 down to Bn, and re-input A1.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top