Random Numbers
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 |
Random Numbers
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 |
Random Numbers
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 |
All times are GMT +1. The time now is 09:20 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com