Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random Name Generation
Hi everyone. I am trying to figure out a way to make a spreadsheet version of
pulling a name out of a hat. I have a list of names, let's say Adam, Bob, and Charlie. Adam has one slip of paper with his name on it, Bob has two, and Charlie has three. Adam 1 Bob 2 Charlie 3 I want to be able to maybe select a command button, or hit F9 to refresh the page and have a random selection from the list appear based on the number of entries that each person has. If my thinking is correct Charlie would be more likely to have his name drawn than either Adam or Bob although the drawing itself is random. Thanks a bunch for any help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random Name Generation
In a column, enter the names with the proper number of duplicates:
Adam Bob Bob Charlie Charlie Charlie .. .. .. In the next column enter: =RAND() and copy down Sort both columns by the second column. This will shuffle the names in the first column. Pick the top name. To pick another, just re-sort first. Entering Charlie three times will triple the chances that he will be picked. -- Gary's Student "Fleone" wrote: Hi everyone. I am trying to figure out a way to make a spreadsheet version of pulling a name out of a hat. I have a list of names, let's say Adam, Bob, and Charlie. Adam has one slip of paper with his name on it, Bob has two, and Charlie has three. Adam 1 Bob 2 Charlie 3 I want to be able to maybe select a command button, or hit F9 to refresh the page and have a random selection from the list appear based on the number of entries that each person has. If my thinking is correct Charlie would be more likely to have his name drawn than either Adam or Bob although the drawing itself is random. Thanks a bunch for any help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random Name Generation
In A1:
=CHOOSE(RANDBETWEEN(1,6),"Adam","Bob","Bob","Charl ie","Charlie","Charlie") Copy down to A6 Use F9 to generate a new list. -- Kind regards, Niek Otten "Fleone" wrote in message ... | Hi everyone. I am trying to figure out a way to make a spreadsheet version of | pulling a name out of a hat. | I have a list of names, let's say Adam, Bob, and Charlie. | Adam has one slip of paper with his name on it, Bob has two, and Charlie has | three. | Adam 1 | Bob 2 | Charlie 3 | I want to be able to maybe select a command button, or hit F9 to refresh the | page and have a random selection from the list appear based on the number of | entries that each person has. If my thinking is correct Charlie would be more | likely to have his name drawn than either Adam or Bob although the drawing | itself is random. | Thanks a bunch for any help. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random Name Generation
Gary's Student, Niek,
Thanks for your very quick responses. Both of these suggestions would work, but aren't exactly what I am looking for. I am hoping to not have to do any continual editing of the data. If at all possible, I would like to be able to generate the number of instances that a particular name might appear in the list automatically. For example, last week Adam had only one entry, but this week because he did so well, he got 3. Instead of having to change the formula, or repopulating a list by hand, I would like to have it automated. Here are some more details. ---- Adam Y Y N N 2 Bob Y Y Y Y 4 Charlie Y N Y Y 3 The cells containing Y or N will change on a weekly basis, that change will then alter the numbers appearing the last column. The numbers in the last column will directly indicate the number of entries that each person would have and will update automatically when the Y/N cells are changed. I would like to be able to give each person a "chance" of being picked based on the number of Y's that they have against the total number of Y's available. So something like RANDBETWEEN(1,9) but giving Adam two chances of being picked, Bob 4, and Charlie 3. Does this help at all? "Fleone" wrote: Hi everyone. I am trying to figure out a way to make a spreadsheet version of pulling a name out of a hat. I have a list of names, let's say Adam, Bob, and Charlie. Adam has one slip of paper with his name on it, Bob has two, and Charlie has three. Adam 1 Bob 2 Charlie 3 I want to be able to maybe select a command button, or hit F9 to refresh the page and have a random selection from the list appear based on the number of entries that each person has. If my thinking is correct Charlie would be more likely to have his name drawn than either Adam or Bob although the drawing itself is random. Thanks a bunch for any help. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random Name Generation
If the list of names starts in cell A2 and if the Y's and N's are in columns B:E enter this formula in cell F2: =SUMPRODUCT(--(B2:E2="Y")) in cell F3 copy the following formula and copy this cell down as far as needed: =SUMPRODUCT(--(B3:E3="Y"))+F2 in cell H2 enter the following formula: =ROUNDUP(RAND()*(ROUNDUP(MAX(F:F),0)),0) In G1 enter: =G2 in cell G2 copy the following formula and copy this cell down as far as needed: =IF($H$2<=F2,A2,G3) G1 will return the winner everytime the random generator runs. Hope that makes sense. -Ikaabod. Fleone Wrote: Gary's Student, Niek, Thanks for your very quick responses. Both of these suggestions would work, but aren't exactly what I am looking for. I am hoping to not have to do any continual editing of the data. If at all possible, I would like to be able to generate the number of instances that a particular name might appear in the list automatically. For example, last week Adam had only one entry, but this week because he did so well, he got 3. Instead of having to change the formula, or repopulating a list by hand, I would like to have it automated. Here are some more details. ---- Adam Y Y N N 2 Bob Y Y Y Y 4 Charlie Y N Y Y 3 The cells containing Y or N will change on a weekly basis, that change will then alter the numbers appearing the last column. The numbers in the last column will directly indicate the number of entries that each person would have and will update automatically when the Y/N cells are changed. I would like to be able to give each person a "chance" of being picked based on the number of Y's that they have against the total number of Y's available. So something like RANDBETWEEN(1,9) but giving Adam two chances of being picked, Bob 4, and Charlie 3. Does this help at all? "Fleone" wrote: Hi everyone. I am trying to figure out a way to make a spreadsheet version of pulling a name out of a hat. I have a list of names, let's say Adam, Bob, and Charlie. Adam has one slip of paper with his name on it, Bob has two, and Charlie has three. Adam 1 Bob 2 Charlie 3 I want to be able to maybe select a command button, or hit F9 to refresh the page and have a random selection from the list appear based on the number of entries that each person has. If my thinking is correct Charlie would be more likely to have his name drawn than either Adam or Bob although the drawing itself is random. Thanks a bunch for any help. -- Ikaabod ------------------------------------------------------------------------ Ikaabod's Profile: http://www.excelforum.com/member.php...o&userid=33371 View this thread: http://www.excelforum.com/showthread...hreadid=536131 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random Name Generation
Ikaabod,
Thanks for the information, it does work as intended. I went in a slightly different direction ultimately, just to add drama <G. I ended up creating a random generator for each number valued cell across three columns and then match the numbers generated against a single cell. When there is a match, the matching cell gets highlighted. =IF(I3=1,RANDBETWEEN(1,200),IF(I3=2,RANDBETWEEN(2, 200),IF(I3=3,RANDBETWEEN(1,200),""))), then subsequent columns disallow a random number if I3 is not 2, or 3. So I have a bank of random numbers being created. Now all I have to do is figure out how to have a command button continue to run the "calculate" function on the page until a match occurs. Thanks again for the great solution! "Ikaabod" wrote: If the list of names starts in cell A2 and if the Y's and N's are in columns B:E enter this formula in cell F2: =SUMPRODUCT(--(B2:E2="Y")) in cell F3 copy the following formula and copy this cell down as far as needed: =SUMPRODUCT(--(B3:E3="Y"))+F2 in cell H2 enter the following formula: =ROUNDUP(RAND()*(ROUNDUP(MAX(F:F),0)),0) In G1 enter: =G2 in cell G2 copy the following formula and copy this cell down as far as needed: =IF($H$2<=F2,A2,G3) G1 will return the winner everytime the random generator runs. Hope that makes sense. -Ikaabod. Fleone Wrote: Gary's Student, Niek, Thanks for your very quick responses. Both of these suggestions would work, but aren't exactly what I am looking for. I am hoping to not have to do any continual editing of the data. If at all possible, I would like to be able to generate the number of instances that a particular name might appear in the list automatically. For example, last week Adam had only one entry, but this week because he did so well, he got 3. Instead of having to change the formula, or repopulating a list by hand, I would like to have it automated. Here are some more details. ---- Adam Y Y N N 2 Bob Y Y Y Y 4 Charlie Y N Y Y 3 The cells containing Y or N will change on a weekly basis, that change will then alter the numbers appearing the last column. The numbers in the last column will directly indicate the number of entries that each person would have and will update automatically when the Y/N cells are changed. I would like to be able to give each person a "chance" of being picked based on the number of Y's that they have against the total number of Y's available. So something like RANDBETWEEN(1,9) but giving Adam two chances of being picked, Bob 4, and Charlie 3. Does this help at all? "Fleone" wrote: Hi everyone. I am trying to figure out a way to make a spreadsheet version of pulling a name out of a hat. I have a list of names, let's say Adam, Bob, and Charlie. Adam has one slip of paper with his name on it, Bob has two, and Charlie has three. Adam 1 Bob 2 Charlie 3 I want to be able to maybe select a command button, or hit F9 to refresh the page and have a random selection from the list appear based on the number of entries that each person has. If my thinking is correct Charlie would be more likely to have his name drawn than either Adam or Bob although the drawing itself is random. Thanks a bunch for any help. -- Ikaabod ------------------------------------------------------------------------ Ikaabod's Profile: http://www.excelforum.com/member.php...o&userid=33371 View this thread: http://www.excelforum.com/showthread...hreadid=536131 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
RANDOM NUMBER GENERATION. | Excel Worksheet Functions | |||
random number generation | Excel Discussion (Misc queries) | |||
Random Name Generation | Excel Worksheet Functions | |||
Random Generation | Excel Programming | |||
I need help with random number generation | Excel Worksheet Functions |