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