Hi,
I am working on a homework assignment and am experiencing challenges.
This worked
=IF(COUNTIF(D2:D7,D1),RANDBETWEEN(1,52),D1)
but this is not
=IF(COUNTIF(D1D3:D7,D2),RANDBETWEEN(1,52),D1)
nor is this
=IF(COUNTIF(D1,D3:D7,D2),RANDBETWEEN(1,52),D1)
nor is this
=IF(COUNTIF(D1/D3:D7,D2),RANDBETWEEN(1,52),D1)
I am attempting to have it check for repeat numbers. I am generating 7
random numbers between 1-52. I want to check and make sure that none of them
are repeating. If there is a repeat, I want to do a new randbetween.
Does anyone know how to generate random numbers without replacement?
Yes it is a poker game. Fun but challenging. Thank you for your time and
assistance.

Hi,

It can be done with worksheets formula using a method posted by Bob Philips
and discussed here.

http://www.microsoft.com/office/comm...045&sloc=en-us

Another method is to simply put the numbers 1 to 52 in column D and then in
an adjacent column enter the formula
=RAND()
drag down and sort the 2 columns and the top 7 numbers will be unique random
numbers in your range.

Lastly a VB solution. Right click your sheet tab, view code and paste this
code in an run it

Sub Liminal_Advertising()
Dim FillRange As Range
Set FillRange = Range("D1:d7")
For Each c In FillRange
Do
c.Value = Int((52 * Rnd) + 1)
Loop Until WorksheetFunction.CountIf(FillRange, c.Value) < 2
Next
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"G0dsg1rl" wrote:

Hi,
I am working on a homework assignment and am experiencing challenges.
This worked
=IF(COUNTIF(D2:D7,D1),RANDBETWEEN(1,52),D1)
but this is not
=IF(COUNTIF(D1D3:D7,D2),RANDBETWEEN(1,52),D1)
nor is this
=IF(COUNTIF(D1,D3:D7,D2),RANDBETWEEN(1,52),D1)
nor is this
=IF(COUNTIF(D1/D3:D7,D2),RANDBETWEEN(1,52),D1)
I am attempting to have it check for repeat numbers. I am generating 7
random numbers between 1-52. I want to check and make sure that none of them
are repeating. If there is a repeat, I want to do a new randbetween.
Does anyone know how to generate random numbers without replacement?
Yes it is a poker game. Fun but challenging. Thank you for your time and
assistance.

Thank you Mike.
I am new to Excel. I sent this to my teacher also. So you have helped our
whole class.

"Mike H" wrote:

Hi,

It can be done with worksheets formula using a method posted by Bob Philips
and discussed here.

http://www.microsoft.com/office/comm...045&sloc=en-us

Another method is to simply put the numbers 1 to 52 in column D and then in
an adjacent column enter the formula
=RAND()
drag down and sort the 2 columns and the top 7 numbers will be unique random
numbers in your range.

Lastly a VB solution. Right click your sheet tab, view code and paste this
code in an run it

Sub Liminal_Advertising()
Dim FillRange As Range
Set FillRange = Range("D1:d7")
For Each c In FillRange
Do
c.Value = Int((52 * Rnd) + 1)
Loop Until WorksheetFunction.CountIf(FillRange, c.Value) < 2
Next
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"G0dsg1rl" wrote:

Hi,
I am working on a homework assignment and am experiencing challenges.
This worked
=IF(COUNTIF(D2:D7,D1),RANDBETWEEN(1,52),D1)
but this is not
=IF(COUNTIF(D1D3:D7,D2),RANDBETWEEN(1,52),D1)
nor is this
=IF(COUNTIF(D1,D3:D7,D2),RANDBETWEEN(1,52),D1)
nor is this
=IF(COUNTIF(D1/D3:D7,D2),RANDBETWEEN(1,52),D1)
I am attempting to have it check for repeat numbers. I am generating 7
random numbers between 1-52. I want to check and make sure that none of them
are repeating. If there is a repeat, I want to do a new randbetween.
Does anyone know how to generate random numbers without replacement?
Yes it is a poker game. Fun but challenging. Thank you for your time and
assistance.

I am generating 7 random numbers between 1-52.
I want to check and make sure that none of them
are repeating.

To generate 7 non-repeating random numbers from 1 to 52 in the range
A2:A8...

Enter this array formula** in A2 and copy down to A8:

=SMALL(IF(ISNA(MATCH(ROW(INDIRECT("1:52")),A$1:A1, 0)),ROW(INDIRECT("1:52"))),INT(RAND()*(52-(ROWS(A$1:A2)-1)))+1)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

This requires that there be a cell above the first formula cell. In this
case the first formula cell is A2. **Cell A1 must not contain a number from
1 to 52**.

Just like in your formula that uses RANDBETWEEN, this formula will also
return new numbers every time a calculation happens.

--
Biff
Microsoft Excel MVP


"G0dsg1rl" wrote in message
...
Hi,
I am working on a homework assignment and am experiencing challenges.
This worked
=IF(COUNTIF(D2:D7,D1),RANDBETWEEN(1,52),D1)
but this is not
=IF(COUNTIF(D1D3:D7,D2),RANDBETWEEN(1,52),D1)
nor is this
=IF(COUNTIF(D1,D3:D7,D2),RANDBETWEEN(1,52),D1)
nor is this
=IF(COUNTIF(D1/D3:D7,D2),RANDBETWEEN(1,52),D1)
I am attempting to have it check for repeat numbers. I am generating 7
random numbers between 1-52. I want to check and make sure that none of
them
are repeating. If there is a repeat, I want to do a new randbetween.
Does anyone know how to generate random numbers without replacement?
Yes it is a poker game. Fun but challenging. Thank you for your time and
assistance.

Thank you so much. That worked great. I sent this to my teacher too. I am new
to Excel and this is great.

"T. Valko" wrote:

I am generating 7 random numbers between 1-52.
I want to check and make sure that none of them
are repeating.

To generate 7 non-repeating random numbers from 1 to 52 in the range
A2:A8...

Enter this array formula** in A2 and copy down to A8:

=SMALL(IF(ISNA(MATCH(ROW(INDIRECT("1:52")),A$1:A1, 0)),ROW(INDIRECT("1:52"))),INT(RAND()*(52-(ROWS(A$1:A2)-1)))+1)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

This requires that there be a cell above the first formula cell. In this
case the first formula cell is A2. **Cell A1 must not contain a number from
1 to 52**.

Just like in your formula that uses RANDBETWEEN, this formula will also
return new numbers every time a calculation happens.

--
Biff
Microsoft Excel MVP


"G0dsg1rl" wrote in message
...
Hi,
I am working on a homework assignment and am experiencing challenges.
This worked
=IF(COUNTIF(D2:D7,D1),RANDBETWEEN(1,52),D1)
but this is not
=IF(COUNTIF(D1D3:D7,D2),RANDBETWEEN(1,52),D1)
nor is this
=IF(COUNTIF(D1,D3:D7,D2),RANDBETWEEN(1,52),D1)
nor is this
=IF(COUNTIF(D1/D3:D7,D2),RANDBETWEEN(1,52),D1)
I am attempting to have it check for repeat numbers. I am generating 7
random numbers between 1-52. I want to check and make sure that none of
them
are repeating. If there is a repeat, I want to do a new randbetween.
Does anyone know how to generate random numbers without replacement?
Yes it is a poker game. Fun but challenging. Thank you for your time and
assistance.



.

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"G0dsg1rl" wrote in message
...
Thank you so much. That worked great. I sent this to my teacher too. I am
new
to Excel and this is great.

"T. Valko" wrote:

I am generating 7 random numbers between 1-52.
I want to check and make sure that none of them
are repeating.

To generate 7 non-repeating random numbers from 1 to 52 in the range
A2:A8...

Enter this array formula** in A2 and copy down to A8:

=SMALL(IF(ISNA(MATCH(ROW(INDIRECT("1:52")),A$1:A1, 0)),ROW(INDIRECT("1:52"))),INT(RAND()*(52-(ROWS(A$1:A2)-1)))+1)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

This requires that there be a cell above the first formula cell. In this
case the first formula cell is A2. **Cell A1 must not contain a number
from
1 to 52**.

Just like in your formula that uses RANDBETWEEN, this formula will also
return new numbers every time a calculation happens.

--
Biff
Microsoft Excel MVP


"G0dsg1rl" wrote in message
...
Hi,
I am working on a homework assignment and am experiencing challenges.
This worked
=IF(COUNTIF(D2:D7,D1),RANDBETWEEN(1,52),D1)
but this is not
=IF(COUNTIF(D1D3:D7,D2),RANDBETWEEN(1,52),D1)
nor is this
=IF(COUNTIF(D1,D3:D7,D2),RANDBETWEEN(1,52),D1)
nor is this
=IF(COUNTIF(D1/D3:D7,D2),RANDBETWEEN(1,52),D1)
I am attempting to have it check for repeat numbers. I am generating 7
random numbers between 1-52. I want to check and make sure that none of
them
are repeating. If there is a repeat, I want to do a new randbetween.
Does anyone know how to generate random numbers without replacement?
Yes it is a poker game. Fun but challenging. Thank you for your time
and
assistance.



.