Randbetween with the list of exception

[thilag]

Hello Friends

Please find the attached 2010 version file <randbetween except 170715.xlsx>

In Cell C1 formula to be entered which can capable of =RANDBETWEEN(1,99) and also can omit / except the values from the list of A1:A20.

thanks in advance

thilag

[NeedForExcel]

Maybe a UDF like -

Please Login or Register  to view this content.

A slightly more customizable version, where you can select the Min, Max Number & Range. Should be entered like this - =RandNumbers(1,99,A1:A20)

Please Login or Register  to view this content.

[daddylonglegs]

Assuming all the A1:A20 values will also be integers in the range 1 to 99 you could use this "array formula" in C1

=SMALL(IF(COUNTIF(A1:A20,ROW(INDIRECT("1:99")))=0,ROW(INDIRECT("1:99"))),INT(1+RAND()*(99-SUM(IF(FREQUENCY(A1:A20,A1:A20),1)))))

Confirm with CTRL+SHIFT+ENTER