Randbetween function

[mike_vr]

Hi there

Does anyone know how I can have six cells next to each other, using the
=RANDBETWEEN(1,49) function, where none of the cells equal each other?
I.e. a random lottery function without having two of the same numbers.
I'm trying to do this without creating a huge nested If statement so any
help would be appreciated.

Cheers
Mike

 

[Dave Peterson]

You may want to visit J.E. McGimpsey's site and grab a copy of his =RandInt()
function.
http://www.mcgimpsey.com/excel/udfs/randint.html

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[James Silverton]

mike_vr wrote on Tue, 9 Mar 2010 09:19:01 -0800:

Does anyone know how I can have six cells next to each other,
using the =RANDBETWEEN(1,49) function, where none of the cells
equal each other? I.e. a random lottery function without
having two of the same numbers. I'm trying to do this without
creating a huge nested If statement so any help would be
appreciated.

Technically, if you do not allow duplication you don't truly have random
numbers. However, there are sites that will produce both duplicated and
unduplicated "random" digits, for example,
http://stattrek.com/Tables/Random.aspx These could be put in a column
and selected sequentially.


--

James Silverton
Potomac, Maryland

Email, with obvious alterations: not.jim.silverton.at.verizon.not

 

[Joe User]

Does anyone know how I can have six cells next to each other,
using the =RANDBETWEEN(1,49) function, where none of the
cells equal each other?

Alternative....

Put =RAND() into A1:A49, copy-and-paste-special-value into B1:B49, then put
the following into C1 and copy into C2:C6:

=rank(B1,$B$1:$B$49)

To generate a new set, copy-and-paste-special-value A1:A49 into B1:B49
again.

The copy-and-paste-special-value is needed to work around the fact that RAND
is a volatile function, so it changes every time any cell in the Excel file
is edited.


----- original message -----

 

[T. Valko]

One way...

This requires that there be a cell before the first random number cell and
that cell must not contain one of the random numbers.

So, let's assume you want the 6 random non-repeating numbers from 1 to 49 in
the range B2:G2. Cell A2 must not contain a number from 1 to 49.

Enter this array formula** in B2 and copy across to G2:

=SMALL(IF(ISNA(MATCH(ROW(INDIRECT("1:49")),$A2:A2,0)),ROW(INDIRECT("1:49"))),INT(RAND()*(49-(COLUMNS($A2:B2)-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.

 

[Joe User]

Technically, if you do not allow duplication you don't truly have random
numbers.

So by your definition, it is impossible to choose 6 people "at random" from
a group of 49.

Wrong! There is random selection "with replacement" and "without
replacement".


----- original message -----

 

[T. Valko]

Correction...

For random non-repeating numbers from 1 to 49:

=SMALL(IF(ISNA(MATCH(ROW(INDIRECT("1:50")),$A2:A2,0)),ROW(INDIRECT("1:50"))),INT(RAND()*(50-(COLUMNS($A2:B2)-1)))+1)

Still array entered.

 

[James Silverton]

Joe wrote on Tue, 9 Mar 2010 09:54:20 -0800:

So by your definition, it is impossible to choose 6 people "at
random" from a group of 49.

Wrong! There is random selection "with replacement" and
"without replacement".

----- original message -----

So by your definition, it is impossible to choose 6 people "at random"
from a group of 49.

Wrong! There is random selection "with replacement" and "without
replacement".

My aside was somewhat a matter of math definition and meeting tests for
randomness in a population of numbers. I am not "wrong".

However, for all practical purposes any method will do. Almost all
generated "random" numbers are pseudorandom anyway. I admit that there
are hardware solutions using electronic noise but even they will produce
repeats. It's analogous to the fact that, say, 10 zeroes will occur
sequentially in the digits of PI.

--

James Silverton
Potomac, Maryland

Email, with obvious alterations: not.jim.silverton.at.verizon.not

 

[מיכ×ל (מיקי) ×בידן]

You may consider to download and install the very assistance add-in: MOREFUNC
http://download.cnet.com/Morefunc/3000-2077_4-10423159.html
Now, you should select 6 cells and while selected to put the cursor into the
formula bar and type: {=MRAND(,1,49)}
*** This is an array formula, and is to be confirmed with CTRL+SHIFT+ENTER
rather than with simply ENTER.
The curly brackets {} are not to be typed manually, those are entered by the
“Excelâ€, when the formula is entered as an Array formula.
Micky

 

[Joe User]

=SMALL(IF(ISNA(MATCH(ROW(INDIRECT("1:50")),$A2:A2,0)),
ROW(INDIRECT("1:50"))),INT(RAND()*(50-(COLUMNS($A2:B2)-1)))+1)

Caveat emptor: that formula can return 50 in the first cell (B2).

That can happen when RAND() returns 0.999999999999999 or larger (up to
0.999999999999999 + 8*2^-53, the largest value less than 1).

This is due to a defect (IMHO) in INT whereby INT(49*0.999999999999999)
returns 49(!). This is because that product is represented internally by
exactly 48.9999999999999,5026200849679298698902130126953125, and it appears
that INT first rounds the internal representation to 15 significant digits
before truncating.

If you want a "correct" implementation of INT, use the following UDF
instead:

Function myINT(x As Double) As Double
myINT = Int(x)
End Function


----- original message -----

 

[Mike H]

and another but this time using vb

Sub Sonic()
Dim FillRange As Range, c As Range
Set FillRange = Range("A1:A6")
For Each c In FillRange
Do
c.Value = Int((49 * 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.

 

[Joe User]

Joe wrote on Tue, 9 Mar 2010 09:54:20 -0800:

Technically, if you do not allow duplication you don't truly have random
numbers.

[....]
Wrong! There is random selection "with replacement" and
"without replacement".

My aside was somewhat a matter of math definition and meeting
tests for randomness in a population of numbers. I am not "wrong".

LOL! Okay, more precisely, random selection "without replacement" means
random selection from different populations, each population being the same
as the previous ones less the previously selected things. There is nothing
that is inherently "not truly random" about it.

Of course, whether or not the selection is "truly random" depends on the
selection process. No software pseudorandom generator (AFAIK) is "truly
random". They only try to achieve the appearance of randomness.

But that has nothing to do with whether or not duplication is allowed, which
is all that I was commenting on.


----- original message -----

 

[Joe User]

=SMALL(IF(ISNA(MATCH(ROW(INDIRECT("1:50")),$A2:A2,0)),
ROW(INDIRECT("1:50"))),INT(RAND()*(50-(COLUMNS($A2:B2)-1)))+1)

Some improvements:

=SMALL(IF(ISNA(MATCH(ROW(INDIRECT("1:49")),$A2:A2,0)),
ROW(INDIRECT("1:49"))),RANDBETWEEN(1,50-COLUMNS($B2:B2)))

I tested this by generating all 49 numbers in random order in B2:AX2.

The use of RANDBETWEEN should avoid the anomaly with
INT(49*0.999999999999999), which would cause 50 to appear in the initial
cell (B2).

The use of "1:49" instead of "1:50" should make no functional difference,
but it seems more consistent with expectations, insofar as we expect no more
than 49 selections.

Moreover, I believe that the use of "1:50" exacerbates the undesired result
of the anomaly with INT(x*0.999999999999999), allowing 50 to be appear in
more than just the initial cell.

MATCH(ROW(INDIRECT("1:50")),$A2:A2,0) should always fail for ROW(50).

So ISNA(MATCH(ROW(INDIRECT("1:50")),$A2:A2,0)) should always be TRUE.

Thus, IF(ISNA(MATCH(ROW(INDIRECT("1:50")),$A2:A2,0)),ROW(INDIRECT("1:50"))
should always result in an array that contains 50 at the end. And the size
of the array is 50 initially, then 49, then 48, etc.

But the intention is for INT(RAND()*(50-(COLUMNS($A2:B2)-1)))+1 to return 49
initially, then 48, then 47, etc.

Thus, the intention is to have the expression SMALL(...,49) initially, then
SMALL(...,48), then SMALL(...,47), etc.

Ergo, with "1:50", the size of the array is always one more than it needs
to. But that seems to serve no useful purpose.

Even though the INT expression sometimes initially returns 50 anomalously,
we would not want SMALL(...,50), since that would result in 50, which is not
between 1 and 49.

Moreover, using "1:50" might result in the SMALL expression returning 50 at
other times, not just initially. This is because INT(48*0.999999999999999)
is 49, which would select 50 in the second cell. Similarly for
INT(47*0.999999999999999) etc in subsequent cells [*].


-----
[*] INT(x*0.999999999999999) does work as intended for some values of x less
than 11. But INT(x*(0.999999999999999+y*2^-53)) returns increasingly more
unintended results as y increases, and INT(x*(0.999999999999999+8*2^-53)
returns unintended results for all x less than 11.


----- original message -----