"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)

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,502620084967929869890213012695312 5, 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 -----

"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