[SOLVED] Sampling/Random Selection

lillianjbrown · #1 06-28-2009, 07:41 PM

I have a file (see SAMPLE file attached) with over 2000 records. I need to know if there is a way to do a sampling or random selection by ethnicity and gender in order to get a diverse population for only 60 individuals. The selection criteria should be on ethinicity and gender.

Is this possible?

Thanks.

martindwilson · #2 06-28-2009, 08:12 PM

example doesnt show either of those criteria!

lillianjbrown · #3 06-29-2009, 04:08 PM

Sorry about that. I have upload new file to include ethnicity and gender criteria. Would you please take a look?

Thanks.

martindwilson · #4 06-29-2009, 04:43 PM

i must admit i know not a fig about stats!
but so far i have how many required from each to make up 60
but as the data isnt realistic there are very few of some groups(i added a few but got bored!) see sheet 1

shg · #5 06-29-2009, 04:56 PM

Here's an alternative. Enter the sample size in H2; col I shows the lucky selectees.

lillianjbrown · #6 06-29-2009, 04:59 PM

Martin,

Thanks for your assistance. I guess I need to be more specific on what I need to make happen. I need to actually pull the names of the 60 so that I establish an invite list to an event.

Is there anyway to do that?

Thanks.

martindwilson · #7 06-29-2009, 05:31 PM

here's how id approach it i havent opened SHG's coz its probably very clever and simple!
and it would break my heart to see i had wasted so much time lol

lillianjbrown · #8 07-01-2009, 08:40 AM

Quote:

Originally Posted by shg

Here's an alternative. Enter the sample size in H2; col I shows the lucky selectees.

WOW! You took this to the next level! Thanks. I have taken an opportunity to plug in the real data records and it works like a charm. MISSION ACCOMPLISHED.

I would however like to discuss the detail of the formulas and the functionality of the spreadsheet in more detail.

How do you know so much? How did you gain so much knowledge about Excel?

Much appreciation to you and what you do for others!!!!!!

shg · #9 07-01-2009, 07:34 PM

Lillian,

The sheet applies the same method twice: fractional allocation of a number across categories. In the upper section, there is this formula in I4 and down:

=IF(G4=0, 0, ROUND( G4 * ($H$2 - SUM(H$3:H3) ) / ($G$14 - SUM(G$3:G3)), 0) )

Paraphrased, it says "select people in this proportion: (SampleSize - PeopleSelectedSoFar) / (TotalNumberOfPeople - PeopleConsideredSoFar)

The ROUND function acknowledges that you can only select whole people.

The formula in I17 and down is

=IF(RAND() < (INDEX($H$4:$H$13, E84) - SUMPRODUCT( (E$16:E83=E84) * (I$16:I83="x") ) ) / (INDEX($G$4:$G$13, E84) - COUNTIF(E$16:E83, E84) ), "x", "")

This uses a random number to select someone (or not), based on

(DesiredSampleSizeByCategory - PeopleSelectedInCategorySoFar) / (PeopleInCategory - PeopleInCategoryConsideredSoFar)

This fraction is the percentage of remaining people that must be selected to achieve the required sample for the category.

Column I just verifies that the sampling worked as desired.

martindwilson · #10 07-01-2009, 07:35 PM

told you he was clever!

lillianjbrown · #11 07-04-2009, 06:28 AM

I think clever is an understatment...I would say he is a freaking genius!

Welcome to the Excel Forum

Forum Statistics