Statistical Analysis - Sampling

[Guest]

I wish to run a random sample of 60 numbers from a population of 75 numbers
without repeating any number more than once. The data analysis - sampling
tool generates the 60 random numbers but duplicates several numbers.

 

[Niek Otten]

See

http://www.mcgimpsey.com/excel/udfs/samplenoreplace.html

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I wish to run a random sample of 60 numbers from a population of 75 numbers
| without repeating any number more than once. The data analysis - sampling
| tool generates the 60 random numbers but duplicates several numbers.

 

[sswilcox]

You could also use the RANK() function in connection with RAND(), which
would force Excel to order your sample 1 - 60 (no duplicates).

Say your population is in C1:C75
In B1, enter =RAND() and copy down to B75
In A1, enter =RANK(B1,B$1:B$75) and copy down to A75

Column A now contains all numbers from 1 to 75, albeit in random order.

You can now use a VLOOKUP to call out your sample from the population.
Make a list of numbers 1 - 60 in another column (E1:E60, for example).
Then in F1 type =VLOOKUP(E1,A$1:A$75,3,FALSE) and copy down to F60. You
now have a random sample of 60 values from your population. F9 to
generate a new sample.

S