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?
--
Thank you,

Colin.

=INT(RAND()*50) + 1

--
met vriendelijke groetjes

"Colin" schreef in bericht
...
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?
--
Thank you,

Colin.

Try

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

--
__________________________________
HTH

Bob

"Colin" wrote in message
...
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?
--
Thank you,

Colin.

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

"Colin" wrote in message
...
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?
--
Thank you,

Colin.

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

"Colin" wrote:

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?
--
Thank you,

Colin.

There are no VB ways of getting unique randoms

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

"Mike H" wrote:

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

"Colin" wrote:

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?
--
Thank you,

Colin.

Thank you for all your responses,

Colin.


"Mike H" wrote:

There are no VB ways of getting unique randoms

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

"Mike H" wrote:

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

"Colin" wrote:

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?
--
Thank you,

Colin.

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




"Colin" wrote:


Thank you for all your responses,

Colin.


"Mike H" wrote:

There are no VB ways of getting unique randoms

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

"Mike H" wrote:

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

"Colin" wrote:

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?
--
Thank you,

Colin.

To Mike H

First, ensure cell A1 is empty and goto ToolsOptions 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(B10,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

"Mike H" wrote in message
...
There are no VB ways of getting unique randoms

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

"Mike H" wrote:

Bob,

Interesting, thank you.


Mike

"Bob Phillips" wrote:

To Mike H

First, ensure cell A1 is empty and goto ToolsOptions 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(B10,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

"Mike H" wrote in message
...
There are no VB ways of getting unique randoms

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

"Mike H" wrote:

Hello Colin,

Select 6 cells and array-enter
UniqRandInt(50)

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

Regards,
Bernd