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!

 

[Fluff]

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

 

[dmrcs]

@Fluff

Thanks! That doesn't seem to be a valid function.

Could you check the syntax as I cannot pinpoint where the error might be?

 

[Fluff]

Are you sure you are using xl 365?

 

[dmrcs]

@Fluff, right!

So that's what I use at work (365) but here at home it's Excel for Mac.Thought the function would be universal (excuse my naivety, learning the ropes).

How different would the entry be on Excel for Mac so I can mark this as solved?

Many thanks in advance!

 

[Fluff]

You can try

=INDEX(AGGREGATE(15,6,ROW(1:100)/(ISNA(MATCH(ROW(1:100),B2:B7,0)))/(ISNUMBER(FIND(6,ROW(1:100)))),ROW(INDIRECT("1:"&19-SUM(--ISNUMBER(SEARCH(6,B2:B7)))))),RANDBETWEEN(1,19-SUM(--ISNUMBER(SEARCH(6,B2:B7)))))

It may need to be array entered.

 

[dmrcs]

@Fluff

Thank you, that worked. Now, that entry is purely to give you random numbers with a 6 in them.

What would be the function without such specification?

Also, can the function point to numbers you want featured on other cells?

That is, the random number or numbers to be manually added into cells D2 and E2 (i.e. D2 has a 9 and E2 has a 5, so the random number has to feature both a 5 and a 9, if blank then any number not currently featured on B2:B7)

Many thanks!

 

[Fluff]

Without the 6 criteria it would be

=INDEX(AGGREGATE(15,6,ROW(1:100)/(ISNA(MATCH(ROW(1:100),B2:B7,0))),ROW(INDIRECT("1:"&100-ROWS(B2:B7)))),RANDBETWEEN(1,100-ROWS(B2:B7)))

As the 2nd part is totally different question from your op, it needs a new thread. Thanks

 

[dmrcs]

Thanks @Fluff

 

[Fluff]

You're welcome & thanks for the feedback.