PDA

View Full Version : Solved: generating random mumbers



austenr
01-31-2006, 12:17 PM
I need to fill a group of cells A1:A10 with random numbers from 0 to 9. A formula is preferable and none of the numbers can be the same. Thanks. A formula is preferable.:dunno

XLGibbs
01-31-2006, 12:23 PM
The only formula would be the RandBetween() function in Analysis toolpak, but I don;t believe it will account for the duplicate issue.....But you may be able to use a RandBetween function in a loop to iterate until the returned value in a cell does not equal any of the others....

austenr
01-31-2006, 12:24 PM
example please...

Aaron Blood
01-31-2006, 12:34 PM
It's easy...

Enter this formula in cells B1:B10...
=RAND()

Enter this formula in cell A1 and copy down...
=RANK(B1,$B$1:$B$10)-1

That's it.

XLGibbs
01-31-2006, 12:37 PM
Nice.....beat me to it...:)

austenr
01-31-2006, 12:45 PM
Thanks. One question how does it turn the decimal numbers into whole integers? Works great.

Bob Phillips
01-31-2006, 12:49 PM
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(0,9)

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.

austenr
01-31-2006, 12:55 PM
Thanks everyone. Solved. :clap:

austenr
01-31-2006, 01:06 PM
Actually if I use Arrons method and put my cursor in a cell like D1 and hit delete, it will send random numbers to the cells until you let go which was what I was after but thanks to everyone for their input. They all work and are solutions I definatly will keep for future use.

matthewspatrick
01-31-2006, 01:11 PM
Implement the Sample function documented in my KB entry:
http://vbaexpress.com/kb/getarticle.php?kb_id=760

To use it:

1) Populate a range with the numbers 0-9
2) Select A1:A10
3) Enter the array formula:
{=TRANSPOSE(Sample(RangeWithNumbers,10,False,False))}

Patrick

Bob Phillips
01-31-2006, 02:00 PM
Actually if I use Arrons method and put my cursor in a cell like D1 and hit delete, it will send random numbers to the cells until you let go which was what I was after but thanks to everyone for their input. They all work and are solutions I definatly will keep for future use.

I actually assumed you wanted the opposite, which is why I locked it down.

You will also find that F9 will recalculate as does delete.

BTW, theoretically, that solution can produce duplicates, although it is unlikely on such a small sample, and I certainly couldn't force one in my limited testing http://vbaexpress.com/forum/images/smilies/001.gif