Validating random numbers
 

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?

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

-----Original Message-----
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?
.

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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"plsauditor" wrote in message
...
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?