Random Number Within Specific Range (Function)

[dmrcs]

Hello there,

Seen a lot of literature on this but not something as specific (via organic search).

Let's say I have a list of numbers in one column. What function (no macro) would enable me to randomise the appearance of a number not in the first column but within a specific range (from 1 to 100).

In other words, a function that would show a number in cell C2 not currently featured on list in column B, but that number has to be between 1 to 100.

I've seen functions that can show a random number currently featured, but no function that shows a number not currently there.

Could it also be made more complex? As in: "I want the random number to have at least a 6 in it" so you get 16, 26, 36...

Many thanks in advance!

 

[dmrcs]

Hi & welcome to MrExcel.
How about

=LET(s,SEQUENCE(100),f,FILTER(s,(ISNA(MATCH(s,B2:B7,0)))*(ISNUMBER(FIND(6,s)))),INDEX(SORTBY(f,RANDARRAY(ROWS(f))),1))

@Fluff
Sorry to disturb, any chance this could be updated to avoid giving numbers with 6s in it?
The other formula just seems to fail when there's more than 100 rows (I've tried fiddling with it, there are over 1000 on the file, but it returns a REF error).
Many thanks!

 

[Fluff]

How about

=LET(s,SEQUENCE(100),f,FILTER(s,ISNA(MATCH(s,B2:B7,0))),INDEX(SORTBY(f,RANDARRAY(ROWS(f))),1))

But if you have over 1000 values in col B, how do you expect to return a number between 1 & 100?

 

[dmrcs]

How about

=LET(s,SEQUENCE(100),f,FILTER(s,ISNA(MATCH(s,B2:B7,0))),INDEX(SORTBY(f,RANDARRAY(ROWS(f))),1))

But if you have over 1000 values in col B, how do you expect to return a number between 1 & 100?

True, I need it to return a number between 1 and 1500

 

[dmrcs]

Got it!

 

[Fluff]

Glad to help.