Rand between 1 and 50

[Colin]

I have a lottery worksheet to design which will produce 6 unique numbers
between 1 and 50 in six cells.
I use =INT(RAND()*51) at the moment which works well. How do I alter this
function so that 0 does not appear as one of the numbers?

 

[Frederik]

=INT(RAND()*50) + 1

 

[Bob Phillips]

Try

=INT((RAND()*50+1))

 

[David Biddulph]

=RANDBETWEEN(1,50) but neither that nor your formula will ensure that the
numbers are unique.

 

[Mike H]

Colin,

There are no VB ways of getting unique randoms within a range but here's a
VB solution you may consider, right click your sheet tab view code and paste
this in and run it. It will put 6 unique numbers in columnA

Sub Lottry_Randoms()
Dim MyRange As Range
Set MyRange = Range("A1:A6")
For Each c In MyRange
Do
c.Value = Int((50 * Rnd) + 1)
Loop Until WorksheetFunction.CountIf(MyRange, c.Value) < 2
Next
End Sub

Mike

 

[Mike H]

There are no VB ways of getting unique randoms

Which should have been There are non VB ways.......

 

[Colin]

Thank you for all your responses,

Colin.

Which should have been There are non VB ways.......

 

[Dave Curtis]

Hi Colin,

I do this (not for lottery numbers but the concept is the same), by putting
=RAND() in A1 and copying down to A50.
Then in B1, put =MATCH(SMALL($A$1:$A$50,ROW()),$A$1:$A$50,0) and copy down
to B6.
You can then hide column A if you like.

There is a very small possiblity of duplicates, but I've never had that arise.

Dave

 

[Bob Phillips]

To Mike H

First, ensure cell A1 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 B1
=IF(($A$1="")+(AND(B1>0,COUNTIF($B$1:$B$10,B1)=1)),B1,INT(RAND()*10+1))
it should show a 0

Copy B1 down to B10.

Finally, put some value in A1, say an 'x', and all the random numbers will
be generated, and they won't change.

To force a re-calculation, clear cell A1, edit cell B1, don't change it,
just edit to reset to 0, copy B1 down to B10, and re-input A1.

 

[Mike H]

Bob,

Interesting, thank you.


Mike

To Mike H

First, ensure cell A1 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 B1
=IF(($A$1="")+(AND(B1>0,COUNTIF($B$1:$B$10,B1)=1)),B1,INT(RAND()*10+1))
it should show a 0

Copy B1 down to B10.

Finally, put some value in A1, say an 'x', and all the random numbers will
be generated, and they won't change.

To force a re-calculation, clear cell A1, edit cell B1, don't change it,
just edit to reset to 0, copy B1 down to B10, and re-input A1.

--
__________________________________
HTH

Bob

 

[Bernd P]

Hello Colin,

Select 6 cells and array-enter
UniqRandInt(50)

My UDF UniqRandInt you can find here:
http://www.sulprobil.com/html/uniqrandint.html

Regards,
Bernd