Validating random numbers

[Guest]

I need to generate a list of 10 random numbers, between 3 and 78. These
represent identification numbers of stores. However, some of those numbers
are not valid (i.e., 13, 15, 20-49,55-57). I used "randbetween" to get the
numbers between 3 and 78. I've tried various combinations of "IF" and "AND"
to screen out the numbers I don't want. Anybody have a way to do this?

 

[Jason Morin]

You could put all valid numbers in A1:A41 and use:

=INDEX(A1:A41,RANDBETWEEN(1,41))

Remember, RANDBETWEEN may return the same value more than
once. To produce 10 *unique* random numbers within your
given range is a little more complex.

HTH
Jason
Atlanta, GA

 

[Max]

Another option to try ..

Assuming, as per Jason's response,
the valid numbers are placed in A1:A41

Put in B1: =RAND()
Copy down to B41

Put in C1:
=INDEX(A:A,MATCH(SMALL(B:B,ROWS($A$1:A1)),B:B,0))

Copy C1 down to C10

This will return 10 random, non repeating numbers
from the list in A1:A41

(Or, just copy down to C41 to get the full lot randomly scrambled!)

Tapping F9 will recalc and re-generate afresh

Freeze the results elsewhere
with a copy > paste special > values > OK