"T. Valko" wrote:
=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 -----

"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP


"mike_vr" wrote in message
...
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