Help with Random Functions
 

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..

Help with Random Functions
 

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.

"CIL" wrote:

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..

Help with Random Functions
 

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.

"CIL" wrote:

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..

Help with Random Functions
 

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.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------





"CIL" wrote in message
news:sq5Eg.15900$PO.15671@dukeread03...
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..

Help with Random Functions
 

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".
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"RagDyer" wrote in message
...
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.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------





"CIL" wrote in message
news:sq5Eg.15900$PO.15671@dukeread03...
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..

Help with Random Functions
 

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" wrote in message
...
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.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------





"CIL" wrote in message
news:sq5Eg.15900$PO.15671@dukeread03...
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..

Help with Random Functions
 

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

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"CIL" wrote in message
news:jr9Eg.15976$PO.4648@dukeread03...
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" wrote in message
...
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.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------





"CIL" wrote in message
news:sq5Eg.15900$PO.15671@dukeread03...
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..