multi, nested functions...

[G0dsg1rl]

Hi,
I am working on a homework assignment and am experiencing challenges.
This worked
=IF(COUNTIF(D27,D1),RANDBETWEEN(1,52),D1)
but this is not
=IF(COUNTIF(D1D37,D2),RANDBETWEEN(1,52),D1)
nor is this
=IF(COUNTIF(D1,D37,D2),RANDBETWEEN(1,52),D1)
nor is this
=IF(COUNTIF(D1/D37,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.

 

[joel]

You need to visit Chip Pearson's website

Random Number Pages
'Random Numbers In Excel
(http://www.cpearson.com/Excel/randomNumbers.aspx)
'Shuffle Array' (http://www.cpearson.com/Excel/ShuffleArray.aspx)


Main page with all the topics
'CPearson.com Topic Index' (http://www.cpearson.com/Excel/topic.aspx

 

[Mike H]

Hi,

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

http://www.microsoft.com/office/com...57fb6f-787f-402c-9746-9e0ecdd1b045&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.

 

[T. Valko]

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.

 

[G0dsg1rl]

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

 

[G0dsg1rl]

Thank you,
I am new to Excel and this is a great resource.

 

[G0dsg1rl]

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

 

[T. Valko]

You're welcome. Thanks for the feedback!