How to Restrict Random Numbers?

[Guest]

How can I exclude certain numbers from the RANDBETWEEN() function? For
example, I might want to generate a random integer between 1 and 10 that
isn't 2, 5 or 7.

 

[JE McGimpsey]

One way:

=CHOOSE(INT(RAND()*7)+1,1,3,4,6,8,9,10)

 

[Guest]

Thanks, but this won't quite do what I need. I should have explained better.
You see, someone who uses this workbook will, ideally, be able to able to
select with check box form controls which numbers will be excluded durning
any given calculation. So they could select 2 and 9 to be excluded,
calculate, then select 7, 8 and 10, calculate, and so on.

 

[T. Valko]

Maybe something like this:

A2:A11 = 1,2,3,4,5,6,7,8,9,10

B2:B11 = checkboxes linked to the same cells as each checkbox is "in"

C2:C11: =RAND()

To get the random number try this array formula** :

=INDEX(A2:A11,MATCH(MAX(IF(B2:B11=FALSE,C2:C11)),C2:C11,0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Note that when you check/uncheck different checkboxes this will cause the
random number to change since RAND is a volatile function. You may want to
set calculation to manual and then hit F9 after you make your selections.