Random Sample on multiple criteria

[Richard]

I want to create a random sample for employee focus groups. In the
final sample, I want to have a representative number of gender,
city/state, job level and ethnicity (all fields in the spreadsheet).
So for example if there are 50% males and 50% females, I want 50%
males and 50% females in the final random sample. If the process
involves a macro or VBA, I would appreciate it if you would let me
know how to insert it into a file then run it.

Thanks.

 

[Peter Atherton]

Richard

For sex use formula =IF(INT(RAND()*2)=1,"M","F") this will
give you a random mix of sex. Creating random list for
state is problematical. I don't know how many to expect in
any state but you could use a table to list say city and
city population and give each city a number, 1, 2, ...n
then use a similar function to generate a number between 1
and n and use a lookup formula to take a percentage of the
city.

If you are after a normal distribution (as in guessing
peaople heights) then use this
=(Rand()+Rand()+RAnd()) /3 *25 + 70

if you are happy with the results remember to copy and use
Edit, pastespecial, Values o remove the formulas as they
keep calculating.

Regards
peter

 

[Max]

Perhaps something along the lines
of this example set-up ..

Assume you have the "Pop" table
In Sheet1, in A1:C6
col headers in row1, data from row2 down
-----------

Ecode..Gender..JobLevel
Emp1...M........1
Emp2...F........2
Emp3...M........3
Emp4...F........3
Emp5...M........2

Assumptions:
For Gender: Either "M" or "F"
For JobLevel: Either 1, 2 or 3

Put in D2: =RAND()
Copy D2 down to D6

Put in E2: =RANK(D2,$D$2:$D$6,1)
Copy E2 down to E6
---

In Sheet2,
with col headers in row1:
Ecode..Gender..JobLevel

Put in A2:

=OFFSET(Sheet1!$A$1,MATCH(ROW()-1,Sheet1!$E:$E,0)-1,COLUMN
()-1)

Copy A2 across to C2, then copy down to C4

This returns a total of 3 unique random samples
from Sheet1

Now set up in E1:G7
the following 2 comparison tables:
----

Gender....Pop..Sample
M
F
JobLevel..Pop..Sample
1
2
3

Put in:

F2: =COUNTIF(Sheet1!B:B,E2)/(COUNTA(Sheet1!B:B)-1)
G2: =COUNTIF(B:B,E2)/(COUNTA(B:B)-1)

Select F2:G2, format as percentage and copy down to G3

Put in:

F5: =COUNTIF(Sheet1!C:C,E5)/(COUNTA(Sheet1!C:C)-1)
G5: =COUNTIF(C:C,E5)/(COUNTA(C:C)-1)

Select F5:G5, format as percentage and copy down to G7

Cols F and G will compute the percentages of:
a. the population (from Sheet1) and
b. the generated random samples (in A2:C4)
for "Gender" (M or F) and "JobLevel" (1,2 or 3)

Operation:
----------
1. Press F9 to regenerate the random samples in A2:C4
2. Compare the figures under Pop and Sample cols
for "Gender" and "JobLevel"

Are they roughly the same ?

If they are, then the random samples
generated in A2:C4 are quite "representative"

Repeat steps (1) and (2) above
until the figures under the Sample cols
are roughly the same as that under the Pop cols

When you're happy with the results, copy A2:C4 and do a
paste special > check "Values" > ok elsewhere to freeze
the random samples

 

[Max]

If you're interested in the sample book
for the example set-up described,
post a valid "readable" email address here
and I'll send it via private email