Random Sample with Parameters

**alexandrite** · 10-13-2011, 08:17 PM

I need to come up with a random sample of students to create a survey list. I know how to get a random sample, but am having trouble with the parameters I need to work under. They are:

1) I need (at least) 25 random students for each teacher.
2) No student can take more than two surveys.

Example roster data is attached (with my attempts to randomize and select students). Students are identified using a unique student ID, and should each be assigned to up to two different teachers (identified by "Teacher ID").

I’ve been trying to figure this out for a while, so any guidance provided would be greatly appreciated. I don't know anything about macros, so maybe this can't be done with formulas alone?

Thank you in advance!

**MarvinP** · 10-13-2011, 08:43 PM

Hi Alex and welcome to the forum,

What if a special ed teacher only has 10 students? You would have problems with rule 1.
If you had too many different surveys rule 2 might fail.

We really need more parameters and a sample of what you have to help much on this problem. I'm thinking a Pivot Table that counts the number of students per teacher and also does a count of surveys taken. If there are lots of students and not many surverys a few random tries would give a good random sample.

To attach a sample file, click on "Go Advanced" and then on the Paper Clip Icon above the advanced message area.

**alexandrite** · 10-14-2011, 01:29 PM

Thanks for the welcome, MarvinP! I thought I'd attached my sample file last night, but I guess I closed the box prematurely... :[

To your questions, we only have one survey we need to give with these rules, and we would like to survey all students in a class < 25 students.

Hope this helps! Also, I've tried using a Pivot Table, but haven't been successful in getting the results I want. The file I've uploaded is only a fraction of the list of students I need to use, and my computer always struggles with the amount of data it needs to calculate.

Thanks again for your assistance!