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
wrote:

Hello,

See JE McGimpsey's site for a solution to this problem.

http://www.mcgimpsey.com/excel/udfs/randint.html

A faster and more flexible RandInt you can find he
http://sulprobil.com/html/randint.html

Regards,
Bernd

"Bernd P" wrote:
A faster and more flexible RandInt you can find he
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

Test the error paths. *They all return #VALUE instead of the intended error.

Hello,

Thank you. Corrected.

Regards,
Bernd

For a variety of options see
Select elements at random without repetition
http://www.tushar-mehta.com/excel/ne...ion/index.html

On Wed, 21 Oct 2009 18:04:01 -0700, Maria
wrote:

Regards,

Tushar Mehta
Microsoft MVP Excel 2000-present
www.tushar-mehta.com
Excel and PowerPoint tutorials and add-ins

"Bernd P" wrote:
A faster and more flexible RandInt you can find he
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.

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