Random Number Within Specific Range (Function)

dmrcs

New Member
Joined
Dec 5, 2021
Messages
14
Office Version
  1. 365
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).

Screenshot 2021-12-05 at 14.47.13.png


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!
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Hi & welcome to MrExcel.
How about
Excel Formula:
=LET(s,SEQUENCE(100),f,FILTER(s,(ISNA(MATCH(s,B2:B7,0)))*(ISNUMBER(FIND(6,s)))),INDEX(SORTBY(f,RANDARRAY(ROWS(f))),1))
 
Upvote 0
@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?
 
Upvote 0
Are you sure you are using xl 365?
 
Upvote 0
@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!
 
Upvote 0
You can try
Excel Formula:
=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.
 
Upvote 0
@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!
 
Upvote 0
Without the 6 criteria it would be
Excel Formula:
=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
 
Last edited:
Upvote 0
Solution
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,212,934
Messages
6,110,762
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top