random number generator
in [Functions]
|
Prev: Excel Formulas not duplicating to the next Cell, copying the valueinstead.
Next: Aggregating data among multiple workbooks
From: pat67 on 6 May 2010 11:56 On May 5, 4:24 pm, "Steve Dunn" <st...(a)sky.com> wrote: > Pat, > > If you used RANDBETWEEN(1,10) you would be likely to get repeated numbers in > your range. You asked for the numbers 1 through 10 to be sorted in a random > order, which is exactly what Hutch's solution does - RAND() is only used for > sorting the numbers, not producing them. > > "pat67" <pbus...(a)comcast.net> wrote in message > > news:b0967ea1-a1de-4239-95d7-a28098067dbc(a)u7g2000vbq.googlegroups.com... > On May 3, 5:23 pm, Tom Hutchins > > > > > > <TomHutch...(a)discussions.microsoft.com> wrote: > > Use the RAND() function. If 1-10 are in A1:A10, enter =RAND() in cell B1 > > and > > copy it down through B10. Recalc (F9), then sort by column B. > > > Hope this helps, > > > Hutch > > > "pat67" wrote: > > > Hey is there a way to generate random numbers like you would for a > > > draft. In other words, i have numbers 1 through 10 and generate a > > > random order for those? > > > .- Hide quoted text - > > > - Show quoted text - > > Rand() only generates between 0 and 1- Hide quoted text - > > - Show quoted text - I have tried randbetween. the problem is like you said i sometimes get the same number twice. What i was looking for is different. I wanted to know if there was a way for me to get a random list of numbers between 1 and 10 like this 2 4 3 1 6 10 9 7 5 8 So is there a way to do that or not? maybe with code? I don't know Thanks
From: Glenn on 6 May 2010 12:56 pat67 wrote: > On May 5, 4:24 pm, "Steve Dunn" <st...(a)sky.com> wrote: >> Pat, >> >> If you used RANDBETWEEN(1,10) you would be likely to get repeated numbers in >> your range. You asked for the numbers 1 through 10 to be sorted in a random >> order, which is exactly what Hutch's solution does - RAND() is only used for >> sorting the numbers, not producing them. >> >> "pat67" <pbus...(a)comcast.net> wrote in message >> >> news:b0967ea1-a1de-4239-95d7-a28098067dbc(a)u7g2000vbq.googlegroups.com... >> On May 3, 5:23 pm, Tom Hutchins >> >> >> >> >> >> <TomHutch...(a)discussions.microsoft.com> wrote: >>> Use the RAND() function. If 1-10 are in A1:A10, enter =RAND() in cell B1 >>> and >>> copy it down through B10. Recalc (F9), then sort by column B. >>> Hope this helps, >>> Hutch >>> "pat67" wrote: >>>> Hey is there a way to generate random numbers like you would for a >>>> draft. In other words, i have numbers 1 through 10 and generate a >>>> random order for those? >>>> .- Hide quoted text - >>> - Show quoted text - >> Rand() only generates between 0 and 1- Hide quoted text - >> >> - Show quoted text - > > I have tried randbetween. the problem is like you said i sometimes get > the same number twice. What i was looking for is different. I wanted > to know if there was a way for me to get a random list of numbers > between 1 and 10 like this > > 2 > 4 > 3 > 1 > 6 > 10 > 9 > 7 > 5 > 8 > > So is there a way to do that or not? maybe with code? I don't know > > Thanks Go back and read Tom's post again. Try it EXACTLY like he wrote it.
From: Steve Dunn on 7 May 2010 03:16
If you're dead set against trying Hutch's solution, you could use a bit of circular referencing to achieve this. First you will need to turn on "Enable iterative calculation" from options - read up a bit on this so that you understand all the implications. Then in A1: =RANDBETWEEN(1,10) in A2: =IF(($A2=0)+COUNTIF($A$1:$A1,$A2),RANDBETWEEN(1,10),$A2) copied down A3:A10. Hold Shift+F9 to generate a new sequence (this re-calculates the sheet). Be aware that this will generate a new sequence whenever the sheet is re-calculated, unless you fix the number in A1. "pat67" <pbuscio(a)comcast.net> wrote in message news:f70be74f-1910-4490-83a8-d2b84bc5f827(a)q30g2000yqd.googlegroups.com... On May 5, 4:24 pm, "Steve Dunn" <st...(a)sky.com> wrote: > Pat, > > If you used RANDBETWEEN(1,10) you would be likely to get repeated numbers > in > your range. You asked for the numbers 1 through 10 to be sorted in a > random > order, which is exactly what Hutch's solution does - RAND() is only used > for > sorting the numbers, not producing them. > > "pat67" <pbus...(a)comcast.net> wrote in message > > news:b0967ea1-a1de-4239-95d7-a28098067dbc(a)u7g2000vbq.googlegroups.com... > On May 3, 5:23 pm, Tom Hutchins > > > > > > <TomHutch...(a)discussions.microsoft.com> wrote: > > Use the RAND() function. If 1-10 are in A1:A10, enter =RAND() in cell B1 > > and > > copy it down through B10. Recalc (F9), then sort by column B. > > > Hope this helps, > > > Hutch > > > "pat67" wrote: > > > Hey is there a way to generate random numbers like you would for a > > > draft. In other words, i have numbers 1 through 10 and generate a > > > random order for those? > > > .- Hide quoted text - > > > - Show quoted text - > > Rand() only generates between 0 and 1- Hide quoted text - > > - Show quoted text - I have tried randbetween. the problem is like you said i sometimes get the same number twice. What i was looking for is different. I wanted to know if there was a way for me to get a random list of numbers between 1 and 10 like this 2 4 3 1 6 10 9 7 5 8 So is there a way to do that or not? maybe with code? I don't know Thanks |