Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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.. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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.. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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.. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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.. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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.. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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.. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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.. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Seed numbers for random number generation, uniform distribution | Excel Discussion (Misc queries) | |||
Random Sampling | Excel Discussion (Misc queries) | |||
Database functions should use criteria in formula, as 1-2-3 does | Excel Worksheet Functions | |||
VBA "Rnd" Function: Truly Random? | Excel Discussion (Misc queries) | |||
Selecting at random with weighted probability | Excel Worksheet Functions |