how can I use randbetween without repeating numbers in a set
in [Functions]
|
From: Joe User on 29 Oct 2009 10:41 "Bernd P" <bplumhoff(a)gmail.com> wrote: > A faster and more flexible RandInt you can find here: > http://sulprobil.com/html/randint.html Test the error paths. They all return #VALUE instead of the intended error. Hint: compare the results of the following. Function test1() As Long() test1 = CVErr(xlErrNum) End Function Function test2() test2 = CVErr(xlErrNum) End Function
From: Bernd P on 29 Oct 2009 11:28 > Test the error paths. They all return #VALUE instead of the intended error. Hello, Thank you. Corrected. Regards, Bernd
From: Tushar Mehta on 29 Oct 2009 11:56 For a variety of options see Select elements at random without repetition http://www.tushar-mehta.com/excel/newsgroups/rand_selection/index.html On Wed, 21 Oct 2009 18:04:01 -0700, Maria <Maria(a)discussions.microsoft.com> wrote: Regards, Tushar Mehta Microsoft MVP Excel 2000-present www.tushar-mehta.com Excel and PowerPoint tutorials and add-ins
From: Joe User on 29 Oct 2009 18:45 "Bernd P" <bplumhoff(a)gmail.com> wrote: > A faster and more flexible RandInt you can find here: > http://sulprobil.com/html/randint.html I don't think the "late initialization" algorithm works correctly when the lMin-to-lMax range includes zero. I am not taking the time to prove it by testing. But consider the following mental experiment with lMin < 0, lMax = 0, and lRept = 1. Assume the first random position is lT(x) such that 1 <= x < lRange; thus, the first random integer is x-1+lMin. Then lT(x) is replaced with lT(lRange). Since lT(lRange) is zero (uninitialized), lT(x) is replaced with lRange-i+lMin, which is zero. So far, so good. Now assume the second random position is the same lT(x) coincidentally. In that case, the second random integer should be zero; that is, it should be lT(lRange) that was copied down. But since lT(x) is zero, it will be interpreted as uninitialized. So the second random integer will be x-1+lMin again. Not only is that not the intended random integer, but also it violates the lRept requirement of one. If you agree, I think the simplest solution is to disable the "late initialization" algorithm when the lMin-to-Lmax range includes zero. To wit: If lRange < CLateInit Or (lMin <= 0 And lMax >= 0) Then '[sic] For i = 1 To lRange lT(i) = Int((i - 1) / lRept) + lMin Next i i = 1 For lRow = 1 To UBound(lR, 1) For lCol = 1 To UBound(lR, 2) lRnd = Int(((lRange - i + 1) * Rnd) + 1) lR(lRow, lCol) = lT(lRnd) lT(lRnd) = lT(lRange - i + 1) i = i + 1 Next lCol Next lRow Else 'If we have a huge range of possible random integers and a 'comparably small number of draws, i.e. if '(lMax - lMin) * lRept >> lCount 'then we can save some runtime with late initialisation. i = 1 [...etc...] PS: I do not agree that "lRange < CLateInit" is sufficient to test the condition (lMax-lMin)*lRept >> lCount. But that's a matter of opinion.
From: Bernd P on 30 Oct 2009 02:19
Hello again, On 29 Okt., 23:45, "Joe User" <joeu2004> wrote: > ... > I don't think the "late initialization" algorithm works correctly when the > lMin-to-lMax range includes zero. > ... Thanks for spotting. I corrected that. > ... > If you agree, I think the simplest solution is to disable the "late > initialization" algorithm when the lMin-to-Lmax range includes zero. > ... I do not agree. A runtime check revealed that there is enough time to shift the results. > ... > PS: I do not agree that "lRange < CLateInit" is sufficient to test the > condition (lMax-lMin)*lRept >> lCount. But that's a matter of opinion. I agree and I changed it - now it's a border for a ratio (lRange / lCount). But it's still a matter of opinion... Thanks again for your help. Regards, Bernd |