Random sampling without replacement

[montreal1775]

Hi,

I'm trying to take a random sample without replacement. The sample is also partially representative, e.g. if a certain proportion of the data are Class A and a certain proportion is Class B, that proportion is maintained in the sample. After the proportions are set, the observations within each class are randomized.

Is there anyway to sample a number of rows, where the number of rows is based on the value in another cell, without having to use a lookup function or index match? Sorry if the question is a little unclear; please let me know if you need any clarification.

Thanks for any help!

[zbor]

Some workbook exampl ewould be helpfull... range, input, desired ouput etc

[JBeaucaire]

Hehe...you give just enough info to totally peak my interest, but not enough to actually help.

Probably would be easiest if you generated a sample sheet (not TOO simple) showing your data. Then show us where you're wanting to do this random averaging and based on what you show, how you arrived at your sample "results".

By letting us see your goal line laid out this way, helping you automate become much simpler.

Click on GO ADVANCED and use the paperclip icon to upload your DATA/RESULTS sample book.

[montreal1775]

I've attached a sample. Essentially, I'm separating the observations by class, and then want to construct a random sample of observations while keeping the proportion of each class the same in the sample as it is in the population. In the sample, the population consists of 11 observations, but the sample will only be 6. The desired number of observations from each class in the random sample is in Row 15. My question is, as best as I can word it, how can I construct a sample of observations where the number of each class represented is listed beside desired sample size, and the sample is random otherwise?

My only other concern is that I don't want a new sample to be taken every time the sheet recalculates, which is why I'm hesitant when I use RAND() to pick out observations.

[shg]

See attached.

[montreal1775]

Thanks! The process definitely works as I needed it to, but I'm a little confused with the syntax. The first thing I don't understand and can't seem to find online is what "<>" means within the IF function. The other question I have is the syntax within a few of the SUM functions. What does SUM($B2:B2) imply in C2 cell, and the others across the row? Isn't B2="Sample" Shouldn't summing that cell result in an error?

Thanks for the help; sorry I can't work through the syntax.

[martindwilson]

<> means not equal to

SUM($B2:B2) when dragged across becomes
SUM($B2:C2),SUM($B2:D2) its just to make it easier to extend across columns ,sum ignores text so SUM($B2:B2) =0

[montreal1775]

Also, I used my own functions for Sample and Population (basically the ones that were in the original sample spreadsheet) because I couldn't translate the functions to my sheet without any hiccups. I get the same numbers you do in the Sample and Population rows. I then used the function you entered in columns 5-9 and rows 5-15 into the corresponding rows and columns on my sheet. When I do that though, the number sampled in each don't sum to the desired sample size, nor are the proportions preserved.

I guess my problem is I don't fully understand how the first argument of the second IF function in those cells works (IF(RAND() < (C$2 - SUM(C$4:C4) ) / (C$3 - COUNTIF($A$4:$A4, C$4) )). This function, as I understand it, reads: if a random number between 0 and 1 is less than the ratio of the difference of sample size and the sum of selected observations (i.e. those with value 1) and the difference between the population of the relevant class and the number of already selected observations within that class, then 1 is returned. I'm confused as to the ratio, and also the way its calculating the ratio. Will there be a higher probability of selecting higher up observations if the count goes from the top down?

[montreal1775]

Thanks for the quick response.

[shg]

Post your workbook that doesn't work.

[shg]

I guess my problem is I don't fully understand how the first argument of the second IF function in those cells works

To answer your question, suppose you want to choose 3 of 5 members.

The probabability of choosing the first is 3/5

The probability of choosing the second depends on whether the first was selected; if it was, then choose the second with probability (3-1)/(5-1) = 1/2. If it wasn't, then (3-0)/(5-1) = 3/4

For choosing the third, it depends on whether 0, 1, or 2 of the prior members were selected, so

If 0, (3-0)/(5-2) = 1 (and RAND() is always less than 1)

If 1, (3-1)/(5-2) = 2/3

If 2, (3-2)/(5-2) = 1/3

... and so forth. That's what the formula does.

You could read it as

(numberToSelect - numberSelectedSoFar) / (candidates - candidatesEvaluatedSoFar)

or

remaindertoSelect / remainingCandidates

The result is, it always converges to the correct sample size.