Random Numbers

[IanW]

Using "RANDBETWEEN", I want to generate a random list of 10 numbers (ie
10 different cells) but not to have any repeats in the list. Is this
possible ?
Thank you !
Ian

 

[Bob Phillips]

Here is one way

First, ensure cell B1 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 A1

=IF(($B$1="")+(AND(A1>0,COUNTIF(A$1:A$10,A1)=1)),A1,RANDBETWEEN(1,100)

it should show a 0

Copy A1 down to A10.

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

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

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[IanW]

Thanks a lot Bob - that works a dream. Just one thing - I found that t
re-calculate I didn't need to.......

"clear cell B1, edit cell A1, and copy A1 down to A10, and re-inpu
B1"

By leaving the value in B1 and simply copying A1 down to A10 I got m
new list of randon non-repeating numbers. I've also set it up as
macro, so I simply press my keyboard shortcut each time for a ne
list.

Once again - thank you !

Ia