Random number

New Contributor

Hello experts,

 

I'm looking for a formula that gives me a "random" number that isn't in the range. Furthermore, this table has numbers between 1 to 8; I'm looking for a formula that gives me a random number between 1 to 8 and HAVEN'T been used in the range E5:E11 (it can be 4,6 or 7).

 

Excel final.png

 

If anyone here in the community has any idea, I'd highly appreciate your knowledge,

 

Thanks,

Santiago

 

1 Reply

@santiaaaago 

If you have Microsoft 365 or Office 2021:

=LET(
    a,SEQUENCE(8),
    b,FILTER(a,ISERROR(MATCH(a,A1:A7,0))),
    n,COUNT(b),
    r,RANDBETWEEN(1,n),
    INDEX(b,r)
)