how can I use randbetween without repeating numbers in a set
in [Functions]
|
From: Maria on 21 Oct 2009 21:04
From: Gord Dibben on 21 Oct 2009 21:16 See JE McGimpsey's site for a solution to this problem. http://www.mcgimpsey.com/excel/udfs/randint.html Note: requires use of VBA Gord Dibben MS Excel MVP On Wed, 21 Oct 2009 18:04:01 -0700, Maria <Maria(a)discussions.microsoft.com> wrote:
From: Ms-Xl-Learner on 21 Oct 2009 21:24 On Oct 22, 6:04 am, Maria <Ma...(a)discussions.microsoft.com> wrote: > Increase the Maximum Number value in your Randbetween formula. For Example if you want to use the Randbetween for 10 cells, then if you use the formula like this =RANDBETWEEN(1,10) will result duplicate values. If you use the formula like this =RANDBETWEEN(1,50) =RANDBETWEEN(1,100) Then you will not get any duplicates. Like this depends upon your data size increase the Maximum Value In your Randbetween formula to avoid duplicates values. ------------------------- (Ms-Exl-Learner) -------------------------
From: "David Biddulph" groups [at] on 22 Oct 2009 01:25 I'm confused by your reply to Maria. Can you explain to us why you think that =RANDBETWEEN(1,50) =RANDBETWEEN(1,100) would not provide duplicates? The probability of duplicates will obviously reduce progressively as the probability for each independent number is reduced, but the probability of duplicates doesn't become zero until the range of numbers from which you are choosing is infinite. [It is left as an excercise for the interested reader to calculate the probability of duplicates for a selection of 10 samples from RANDBETWEEN(1,100), but I can assure you that the probability is non-zero.] Hence you need a VBA solution if duplcates are to be avoided. -- David Biddulph Ms-Xl-Learner wrote: > On Oct 22, 6:04 am, Maria <Ma...(a)discussions.microsoft.com> wrote: >> > > Increase the Maximum Number value in your Randbetween formula. > > For Example if you want to use the Randbetween for 10 cells, then if > you use the formula like this =RANDBETWEEN(1,10) will result duplicate > values. > > If you use the formula like this > > =RANDBETWEEN(1,50) > =RANDBETWEEN(1,100) > > Then you will not get any duplicates. > > Like this depends upon your data size increase the Maximum Value In > your Randbetween formula to avoid duplicates values. > > ------------------------- > (Ms-Exl-Learner) > -------------------------
From: "David Biddulph" groups [at] on 22 Oct 2009 01:29
I'm confused by your reply to Maria. Can you explain to us why you think that =RANDBETWEEN(1,50) =RANDBETWEEN(1,100) would not provide duplicates? The probability of duplicates will obviously reduce progressively as the probability for each independent number is reduced, but the probability of duplicates doesn't become zero until the range of numbers from which you are choosing is infinite. [It is left as an excercise for the interested reader to calculate the probability of duplicates for a selection of 10 samples from RANDBETWEEN(1,100), but I can assure you that the probability is non-zero.] Hence you need a VBA solution if duplcates are to be avoided. -- David Biddulph Ms-Xl-Learner wrote: > On Oct 22, 6:04 am, Maria <Ma...(a)discussions.microsoft.com> wrote: >> > > Increase the Maximum Number value in your Randbetween formula. > > For Example if you want to use the Randbetween for 10 cells, then if > you use the formula like this =RANDBETWEEN(1,10) will result duplicate > values. > > If you use the formula like this > > =RANDBETWEEN(1,50) > =RANDBETWEEN(1,100) > > Then you will not get any duplicates. > > Like this depends upon your data size increase the Maximum Value In > your Randbetween formula to avoid duplicates values. > > ------------------------- > (Ms-Exl-Learner) > ------------------------- |