RANDBETWEEN() excluding values

[Jon von der Heyden]

Hi,

Does anybody know how to do a RANDBETWEEN(1,100) but have it exclude numbers that already exist within a defined range?

Thanks,
Jon

 

[Nishant94]

You can try this as well

=AGGREGATE(15,6,ROW($1:$100)/NOT(ISNUMBER(MATCH(ROW($1:$100),$A$1:$A$4,0))),RANDBETWEEN(1,100-COUNT($A$1:$A$4)))

Press Ctrl + Shift + Enter after putting the formula in cell as it is an array formula.

Where $A$1:$A$4 contains the numbers that needs to be excluded.

 

[DJJC]

Hi noj,

I don't know of any easy way to do this using RANDBETWEEN, so here is a custom user-defined function to make it easy:

Function RandBetweenInt(Lowest As Long, Highest As Long, Exclude As Range) As Long
Dim R As Long
Dim C As Range
Do
R = Lowest + Int(Rnd() * (Highest + 1 - Lowest))
For Each C In Exclude
If R = C Then Exit For
Next C
Loop Until C Is Nothing

RandBetweenInt = R
Application.Volatile
End Function


To use this function, enter the cell formula

=RandBetweenInt(1,100,C1:C20)

where C1:C20 is the range containing the numbers you want to exclude.

To install this UDF in your workbook, go to the VBE (keyboard Alt-TMV), insert a new macro module (Alt-IM) and paste this code into the Code pane.

Damon

That really helped me. thanks Damon!