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


--
IanW
------------------------------------------------------------------------
IanW's Profile: http://www.excelforum.com/member.php...o&userid=31366
View this thread: http://www.excelforum.com/showthread...hreadid=510576

Here is one way

First, ensure cell B1 is empty and goto ToolsOptions 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(A10,COUNTIF(A$1:A$10,A1)=1)),A 1,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" wrote in message
...

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


--
IanW
------------------------------------------------------------------------
IanW's Profile:
http://www.excelforum.com/member.php...o&userid=31366
View this thread: http://www.excelforum.com/showthread...hreadid=510576

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

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

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

Once again - thank you !

Ian


--
IanW
------------------------------------------------------------------------
IanW's Profile: http://www.excelforum.com/member.php...o&userid=31366
View this thread: http://www.excelforum.com/showthread...hreadid=510576