Help with Random Functions

[CIL]

Good evening,
Well it happens every year when school starts; the Kids aren't happy with
the teachers or the parents aren't happy with the kids teacher or what ever
happens in between. There is a big brouhaha again this year.

What recommendations does the group have to make the student selection or
the teacher selection totally random? 43 kids and 3 teachers, there are no
twins to contend with.

How would you do this one?

thanks in advance..

 

[Guest]

I would list all the kids name in a column. I would then assign a teachers
name to a range. Teacher 1, 1-14, teacher 2 15-29, teacher 3 30-43 (Do not
put the teachers in the spreadsheet yet.)
put this formula in the column next to the kids names.
=RAND()
Copy the formula down to so that there is a rtandom number next to each kid.
Sort according to the random number. This will give you a random sorting of
the kids. Then stick the teachers names in another column as described above.

 

[Guest]

1. List the names in A1:A43

2. In B1 enter the following formula and copy down
=CHOOSE(1+3*RAND(),"teacher1","teacher2","teacher3")

3. In another set of cells enter the formulas
=COUNTIF($B$1:$B$43,"teacher1")
=COUNTIF($B$1:$B$43,"teacher2")
=COUNTIF($B$1:$B$43,"teacher3")

4. Hit F9 until the numbers are close enough (some combination of 14, 14, 15)

5. Copy B1:B43 and paste special (right click, selection paste special) in
column C to "set in" the teachers. Then delete Column B.

 

[RagDyer]

Set up 3 columns and simply label them; teacher1,teacher2, and teacher3.

Randomly have the 43 names entered into these 3 columns.

Couldn't be more simple and unprejudiced then that ... could it?

To accomplish this random selection, enter all 43 names in a column, say E1
to E43.
In the next column, in F1, enter this formula:
=RAND()
And copy it down to F43.

Enter this formula in A2:

=INDEX($E$1:$E$43,RANK(INDEX($F$1:$F$43,(3*ROWS($1:1))-3+COLUMNS($A:A)),$F$1:$F$43))

Then copy across to C2,
And then copy down to C16.
B16 and C16 will display a #REF! error, since there are not enough students
to fill those 2 cells.

Each time you hit <F9>, you'll get a new random set of names in each of the
3 columns.

Since there will be a class with an extra student, you could also set up A1
to C1 to randomly select which teacher gets which column.

Assign the teachers a number, from 1 to 3.
To randomly select which teacher gets which group, enter this formula in A1:

=INDEX(ROW($A$1:$A$3),RANK(INDEX($F:$F,COLUMNS($A:A)),$F$1:$F$3))

And copy across to C1.

NOW, each time you hit <F9>, you'll get a completely new random set-up, with
a random selection of *both* teachers and students.

 

[RagDyer]

Forgot to mention that you should set the sheet calc to *manual*, so that
you'll be able to copy or print a list without it constantly changing.

<Tools> <Options> <Calculation> tab,
And click on "Manual".

 

[CIL]

RD,

Works like a charm.

Thanks and I appreciate all that you and the other contributors do for this
group.

Thanks again and have a great evening..

 

[RagDyer]

You're welcome, and thank you for the feed-back.