Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to enter names in a column specific # of times
Im using the following to produce a random picking of names in the B column.
=INDEX($B$1:$B$100,RAND()*100+1) Each name is entered a different number of times in the B column, so that the names entered the most have a higher probability if hitting. What Id like to have is another cells/columns that will auto-enter the names in the B column the proper # of times. E.g. If I have the following: R S 1 Steve 4 2 Tom 7 3 Don 9 So that Steve is entered in the B column 4 times, Tom 7 times, Don 9 times, etc. Is this possible ? Thanks, Steve |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to enter names in a column specific # of times
No need to create the repeated name list.
Try this: R S 1 Steve 4 2 Tom 7 3 Don 9 In T1 enter 1 Enter this formula in T2 and copy down to T3 =S1+T1 Then: =INDEX(R1:R3,MATCH(RAND()*SUM(S1:S3)+1,T1:T3)) -- Biff Microsoft Excel MVP "Steve" wrote in message ... I'm using the following to produce a random picking of names in the B column. =INDEX($B$1:$B$100,RAND()*100+1) Each name is entered a different number of times in the B column, so that the names entered the most have a higher probability if hitting. What I'd like to have is another cells/columns that will auto-enter the names in the B column the proper # of times. E.g. If I have the following: R S 1 Steve 4 2 Tom 7 3 Don 9 So that Steve is entered in the B column 4 times, Tom 7 times, Don 9 times, etc. Is this possible ? Thanks, Steve |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to enter names in a column specific # of times
Thanks,
But I'm getting a circular reference, because I don't think I quite explained what I have very well. This formula that I'm using - =INDEX($B$1:$B$100,RAND()*100+1) Is in a totally different cell (G18), that is showing the result. If I have the # 4 in the cell next to Steve [S1]( and that # will change every week), and a # 7 in the cell next to Tom [S2] ( and that # will also change every week), how can I get 4 Steve's in that B column, 7 Tom's in that B column, etc. And to have the differing amounts of Steve & Tom the next week ? Thanks again, Steve "T. Valko" wrote: No need to create the repeated name list. Try this: R S 1 Steve 4 2 Tom 7 3 Don 9 In T1 enter 1 Enter this formula in T2 and copy down to T3 =S1+T1 Then: =INDEX(R1:R3,MATCH(RAND()*SUM(S1:S3)+1,T1:T3)) -- Biff Microsoft Excel MVP "Steve" wrote in message ... I'm using the following to produce a random picking of names in the B column. =INDEX($B$1:$B$100,RAND()*100+1) Each name is entered a different number of times in the B column, so that the names entered the most have a higher probability if hitting. What I'd like to have is another cells/columns that will auto-enter the names in the B column the proper # of times. E.g. If I have the following: R S 1 Steve 4 2 Tom 7 3 Don 9 So that Steve is entered in the B column 4 times, Tom 7 times, Don 9 times, etc. Is this possible ? Thanks, Steve |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to enter names in a column specific # of times
You don't need the column of repeated names but if you insist, see this:
http://tinyurl.com/2hajwo -- Biff Microsoft Excel MVP "Steve" wrote in message ... Thanks, But I'm getting a circular reference, because I don't think I quite explained what I have very well. This formula that I'm using - =INDEX($B$1:$B$100,RAND()*100+1) Is in a totally different cell (G18), that is showing the result. If I have the # 4 in the cell next to Steve [S1]( and that # will change every week), and a # 7 in the cell next to Tom [S2] ( and that # will also change every week), how can I get 4 Steve's in that B column, 7 Tom's in that B column, etc. And to have the differing amounts of Steve & Tom the next week ? Thanks again, Steve "T. Valko" wrote: No need to create the repeated name list. Try this: R S 1 Steve 4 2 Tom 7 3 Don 9 In T1 enter 1 Enter this formula in T2 and copy down to T3 =S1+T1 Then: =INDEX(R1:R3,MATCH(RAND()*SUM(S1:S3)+1,T1:T3)) -- Biff Microsoft Excel MVP "Steve" wrote in message ... I'm using the following to produce a random picking of names in the B column. =INDEX($B$1:$B$100,RAND()*100+1) Each name is entered a different number of times in the B column, so that the names entered the most have a higher probability if hitting. What I'd like to have is another cells/columns that will auto-enter the names in the B column the proper # of times. E.g. If I have the following: R S 1 Steve 4 2 Tom 7 3 Don 9 So that Steve is entered in the B column 4 times, Tom 7 times, Don 9 times, etc. Is this possible ? Thanks, Steve |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to enter names in a column specific # of times
Improvement:
=INDEX(R1:R3,MATCH(RAND()*SUM(S1:S3)+1,T1:T3)) If you're using Excel 2003 or earlier and have the analysis ToolPak add-in installed or, if you're using Excel 2007: =INDEX(R1:R3,MATCH(RANDBETWEEN(1,SUM(S1:S3)),T1:T3 )) For any version: =INDEX(R1:R3,MATCH(ROUND(RAND()*SUM(S1:S3),0)+1,T1 :T3)) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... No need to create the repeated name list. Try this: R S 1 Steve 4 2 Tom 7 3 Don 9 In T1 enter 1 Enter this formula in T2 and copy down to T3 =S1+T1 Then: =INDEX(R1:R3,MATCH(RAND()*SUM(S1:S3)+1,T1:T3)) -- Biff Microsoft Excel MVP "Steve" wrote in message ... I'm using the following to produce a random picking of names in the B column. =INDEX($B$1:$B$100,RAND()*100+1) Each name is entered a different number of times in the B column, so that the names entered the most have a higher probability if hitting. What I'd like to have is another cells/columns that will auto-enter the names in the B column the proper # of times. E.g. If I have the following: R S 1 Steve 4 2 Tom 7 3 Don 9 So that Steve is entered in the B column 4 times, Tom 7 times, Don 9 times, etc. Is this possible ? Thanks, Steve |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to enter names in a column specific # of times
Thanks for all your patience. I surely didn't mean to insist that I wanted
that list, I was just adding that to the original random solution that was provied to me by this group a few years ago. With the Excel 2207 solution below, it works like a charm, and like you stated, a much cleaner and improved solution. Thanks much, Steve "T. Valko" wrote: Improvement: =INDEX(R1:R3,MATCH(RAND()*SUM(S1:S3)+1,T1:T3)) If you're using Excel 2003 or earlier and have the analysis ToolPak add-in installed or, if you're using Excel 2007: =INDEX(R1:R3,MATCH(RANDBETWEEN(1,SUM(S1:S3)),T1:T3 )) For any version: =INDEX(R1:R3,MATCH(ROUND(RAND()*SUM(S1:S3),0)+1,T1 :T3)) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... No need to create the repeated name list. Try this: R S 1 Steve 4 2 Tom 7 3 Don 9 In T1 enter 1 Enter this formula in T2 and copy down to T3 =S1+T1 Then: =INDEX(R1:R3,MATCH(RAND()*SUM(S1:S3)+1,T1:T3)) -- Biff Microsoft Excel MVP "Steve" wrote in message ... I'm using the following to produce a random picking of names in the B column. =INDEX($B$1:$B$100,RAND()*100+1) Each name is entered a different number of times in the B column, so that the names entered the most have a higher probability if hitting. What I'd like to have is another cells/columns that will auto-enter the names in the B column the proper # of times. E.g. If I have the following: R S 1 Steve 4 2 Tom 7 3 Don 9 So that Steve is entered in the B column 4 times, Tom 7 times, Don 9 times, etc. Is this possible ? Thanks, Steve |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to enter names in a column specific # of times
I should have explained this in my original reply.
In essence the formula works as though each name was repeated n number of times. From reading your post it seemed that you wanted the list with repeats just for this single specific purpose. I didn't know whether the list was used for other things. Thanks for the feedback! -- Biff Microsoft Excel MVP "Steve" wrote in message ... Thanks for all your patience. I surely didn't mean to insist that I wanted that list, I was just adding that to the original random solution that was provied to me by this group a few years ago. With the Excel 2207 solution below, it works like a charm, and like you stated, a much cleaner and improved solution. Thanks much, Steve "T. Valko" wrote: Improvement: =INDEX(R1:R3,MATCH(RAND()*SUM(S1:S3)+1,T1:T3)) If you're using Excel 2003 or earlier and have the analysis ToolPak add-in installed or, if you're using Excel 2007: =INDEX(R1:R3,MATCH(RANDBETWEEN(1,SUM(S1:S3)),T1:T3 )) For any version: =INDEX(R1:R3,MATCH(ROUND(RAND()*SUM(S1:S3),0)+1,T1 :T3)) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... No need to create the repeated name list. Try this: R S 1 Steve 4 2 Tom 7 3 Don 9 In T1 enter 1 Enter this formula in T2 and copy down to T3 =S1+T1 Then: =INDEX(R1:R3,MATCH(RAND()*SUM(S1:S3)+1,T1:T3)) -- Biff Microsoft Excel MVP "Steve" wrote in message ... I'm using the following to produce a random picking of names in the B column. =INDEX($B$1:$B$100,RAND()*100+1) Each name is entered a different number of times in the B column, so that the names entered the most have a higher probability if hitting. What I'd like to have is another cells/columns that will auto-enter the names in the B column the proper # of times. E.g. If I have the following: R S 1 Steve 4 2 Tom 7 3 Don 9 So that Steve is entered in the B column 4 times, Tom 7 times, Don 9 times, etc. Is this possible ? Thanks, Steve |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to enter names in a column specific # of times
One last thing:
=INDEX(R1:R3,MATCH(RANDBETWEEN(1,SUM(S1:S3)),T1:T3 )) Here is R, S & T columns. R S T Steve 19 1 Tom 17 20 Don 12 37 With the T1 being 1 ( 1) T2 being 1+19 and (20) S1+T1 T3 being 17 + 20 (37) S2_T2 I guess I'm confusing over the T column. Is Don having the chance to come up 37 times vs. Tom 20 times & Steve only once ? I wanted to use the #'s in the S colum for the random probability such as Steve 19, Tom 17 & Don 12. Is the n number of times the S column or the T column ? Thanks, Steve "T. Valko" wrote: I should have explained this in my original reply. In essence the formula works as though each name was repeated n number of times. From reading your post it seemed that you wanted the list with repeats just for this single specific purpose. I didn't know whether the list was used for other things. Thanks for the feedback! -- Biff Microsoft Excel MVP "Steve" wrote in message ... Thanks for all your patience. I surely didn't mean to insist that I wanted that list, I was just adding that to the original random solution that was provied to me by this group a few years ago. With the Excel 2207 solution below, it works like a charm, and like you stated, a much cleaner and improved solution. Thanks much, Steve "T. Valko" wrote: Improvement: =INDEX(R1:R3,MATCH(RAND()*SUM(S1:S3)+1,T1:T3)) If you're using Excel 2003 or earlier and have the analysis ToolPak add-in installed or, if you're using Excel 2007: =INDEX(R1:R3,MATCH(RANDBETWEEN(1,SUM(S1:S3)),T1:T3 )) For any version: =INDEX(R1:R3,MATCH(ROUND(RAND()*SUM(S1:S3),0)+1,T1 :T3)) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... No need to create the repeated name list. Try this: R S 1 Steve 4 2 Tom 7 3 Don 9 In T1 enter 1 Enter this formula in T2 and copy down to T3 =S1+T1 Then: =INDEX(R1:R3,MATCH(RAND()*SUM(S1:S3)+1,T1:T3)) -- Biff Microsoft Excel MVP "Steve" wrote in message ... I'm using the following to produce a random picking of names in the B column. =INDEX($B$1:$B$100,RAND()*100+1) Each name is entered a different number of times in the B column, so that the names entered the most have a higher probability if hitting. What I'd like to have is another cells/columns that will auto-enter the names in the B column the proper # of times. E.g. If I have the following: R S 1 Steve 4 2 Tom 7 3 Don 9 So that Steve is entered in the B column 4 times, Tom 7 times, Don 9 times, etc. Is this possible ? Thanks, Steve |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to enter names in a column specific # of times
This is how it works...
........ R.........S.....T 1..Steve.....19.....1 2..Tom......17.....20 3..Don......12.....37 =INDEX(R1:R3,MATCH(RANDBETWEEN(1,SUM(S1:S3)),T1:T3 )) SUM(S1:S3) = 48 so: RANDBETWEEN(1,SUM(S1:S3)) returns a random number from 1 to 48 Let's assume that the random number is 27. Using the MATCH function and omitting the the match_type argument which then defaults to match_type 1 meaning the lookup_array is in ascending (which is what I have done), if an exact match of the lookup_value (our random number) is not found it will match the greatest value that is less than the lookup_value. Using our random number of 27 and the lookup_array in T1:T3 (1;20;37) there is no exact match so the greatest value that is less than 27 is 20 which is in position 2. So: RANDBETWEEN(1,SUM(S1:S3)) = 27 MATCH(27,T1:T3) = 2 INDEX(R1:R3,2) = Tom This is how it breaks out for each of the names: Random numbers from 1 to 19 = Steve Random numbers from 20 to 36 = Tom Random numbers from 37 to 48 = Don So: Random numbers from 1 to 19 means Steve can be selected up 19 times Random numbers from 20 to 36 means Tom can be selected up to 17 times Random numbers from 37 to 48 means Don can be selected up to 12 times Like I stated in my other reply, the formula is working as though the names have been repeated n number of times. This is actually pretty slick if think you about it! <g It's like these folks have bought raffle tickets and they're being picked out of a hat. Steve has 19 tickets, Tom has 17 tickets and Don has 12 tickets. So Steve has the best chance of being the winner. Hope that makes sense. -- Biff Microsoft Excel MVP "Steve" wrote in message ... One last thing: =INDEX(R1:R3,MATCH(RANDBETWEEN(1,SUM(S1:S3)),T1:T3 )) Here is R, S & T columns. R S T Steve 19 1 Tom 17 20 Don 12 37 With the T1 being 1 ( 1) T2 being 1+19 and (20) S1+T1 T3 being 17 + 20 (37) S2_T2 I guess I'm confusing over the T column. Is Don having the chance to come up 37 times vs. Tom 20 times & Steve only once ? I wanted to use the #'s in the S colum for the random probability such as Steve 19, Tom 17 & Don 12. Is the n number of times the S column or the T column ? Thanks, Steve "T. Valko" wrote: I should have explained this in my original reply. In essence the formula works as though each name was repeated n number of times. From reading your post it seemed that you wanted the list with repeats just for this single specific purpose. I didn't know whether the list was used for other things. Thanks for the feedback! -- Biff Microsoft Excel MVP "Steve" wrote in message ... Thanks for all your patience. I surely didn't mean to insist that I wanted that list, I was just adding that to the original random solution that was provied to me by this group a few years ago. With the Excel 2207 solution below, it works like a charm, and like you stated, a much cleaner and improved solution. Thanks much, Steve "T. Valko" wrote: Improvement: =INDEX(R1:R3,MATCH(RAND()*SUM(S1:S3)+1,T1:T3)) If you're using Excel 2003 or earlier and have the analysis ToolPak add-in installed or, if you're using Excel 2007: =INDEX(R1:R3,MATCH(RANDBETWEEN(1,SUM(S1:S3)),T1:T3 )) For any version: =INDEX(R1:R3,MATCH(ROUND(RAND()*SUM(S1:S3),0)+1,T1 :T3)) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... No need to create the repeated name list. Try this: R S 1 Steve 4 2 Tom 7 3 Don 9 In T1 enter 1 Enter this formula in T2 and copy down to T3 =S1+T1 Then: =INDEX(R1:R3,MATCH(RAND()*SUM(S1:S3)+1,T1:T3)) -- Biff Microsoft Excel MVP "Steve" wrote in message ... I'm using the following to produce a random picking of names in the B column. =INDEX($B$1:$B$100,RAND()*100+1) Each name is entered a different number of times in the B column, so that the names entered the most have a higher probability if hitting. What I'd like to have is another cells/columns that will auto-enter the names in the B column the proper # of times. E.g. If I have the following: R S 1 Steve 4 2 Tom 7 3 Don 9 So that Steve is entered in the B column 4 times, Tom 7 times, Don 9 times, etc. Is this possible ? Thanks, Steve |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to enter names in a column specific # of times
Again, thank you for your patience.
And yes, it's very slick, and exactly what I was trying to achieve. I couldn't follow some of the technical explanation, but the bottom line explanation using raffle tix made it very easy to understand. In case you're interested, the random process was to get a free agent draft order each week in our Fantasy Football league based on various criterea such as overall won-loss percent, points for, and # of prior free agent aquisions. Your help in getting the random generator to work as I hoped has been a perfect and fair solution. Thanks again for all your patience, creativity and skill. Steve "T. Valko" wrote: This is how it works... ........ R.........S.....T 1..Steve.....19.....1 2..Tom......17.....20 3..Don......12.....37 =INDEX(R1:R3,MATCH(RANDBETWEEN(1,SUM(S1:S3)),T1:T3 )) SUM(S1:S3) = 48 so: RANDBETWEEN(1,SUM(S1:S3)) returns a random number from 1 to 48 Let's assume that the random number is 27. Using the MATCH function and omitting the the match_type argument which then defaults to match_type 1 meaning the lookup_array is in ascending (which is what I have done), if an exact match of the lookup_value (our random number) is not found it will match the greatest value that is less than the lookup_value. Using our random number of 27 and the lookup_array in T1:T3 (1;20;37) there is no exact match so the greatest value that is less than 27 is 20 which is in position 2. So: RANDBETWEEN(1,SUM(S1:S3)) = 27 MATCH(27,T1:T3) = 2 INDEX(R1:R3,2) = Tom This is how it breaks out for each of the names: Random numbers from 1 to 19 = Steve Random numbers from 20 to 36 = Tom Random numbers from 37 to 48 = Don So: Random numbers from 1 to 19 means Steve can be selected up 19 times Random numbers from 20 to 36 means Tom can be selected up to 17 times Random numbers from 37 to 48 means Don can be selected up to 12 times Like I stated in my other reply, the formula is working as though the names have been repeated n number of times. This is actually pretty slick if think you about it! <g It's like these folks have bought raffle tickets and they're being picked out of a hat. Steve has 19 tickets, Tom has 17 tickets and Don has 12 tickets. So Steve has the best chance of being the winner. Hope that makes sense. -- Biff Microsoft Excel MVP "Steve" wrote in message ... One last thing: =INDEX(R1:R3,MATCH(RANDBETWEEN(1,SUM(S1:S3)),T1:T3 )) Here is R, S & T columns. R S T Steve 19 1 Tom 17 20 Don 12 37 With the T1 being 1 ( 1) T2 being 1+19 and (20) S1+T1 T3 being 17 + 20 (37) S2_T2 I guess I'm confusing over the T column. Is Don having the chance to come up 37 times vs. Tom 20 times & Steve only once ? I wanted to use the #'s in the S colum for the random probability such as Steve 19, Tom 17 & Don 12. Is the n number of times the S column or the T column ? Thanks, Steve "T. Valko" wrote: I should have explained this in my original reply. In essence the formula works as though each name was repeated n number of times. From reading your post it seemed that you wanted the list with repeats just for this single specific purpose. I didn't know whether the list was used for other things. Thanks for the feedback! -- Biff Microsoft Excel MVP "Steve" wrote in message ... Thanks for all your patience. I surely didn't mean to insist that I wanted that list, I was just adding that to the original random solution that was provied to me by this group a few years ago. With the Excel 2207 solution below, it works like a charm, and like you stated, a much cleaner and improved solution. Thanks much, Steve "T. Valko" wrote: Improvement: =INDEX(R1:R3,MATCH(RAND()*SUM(S1:S3)+1,T1:T3)) If you're using Excel 2003 or earlier and have the analysis ToolPak add-in installed or, if you're using Excel 2007: =INDEX(R1:R3,MATCH(RANDBETWEEN(1,SUM(S1:S3)),T1:T3 )) For any version: =INDEX(R1:R3,MATCH(ROUND(RAND()*SUM(S1:S3),0)+1,T1 :T3)) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... No need to create the repeated name list. Try this: R S 1 Steve 4 2 Tom 7 3 Don 9 In T1 enter 1 Enter this formula in T2 and copy down to T3 =S1+T1 Then: =INDEX(R1:R3,MATCH(RAND()*SUM(S1:S3)+1,T1:T3)) -- Biff Microsoft Excel MVP "Steve" wrote in message ... I'm using the following to produce a random picking of names in the B column. =INDEX($B$1:$B$100,RAND()*100+1) Each name is entered a different number of times in the B column, so that the names entered the most have a higher probability if hitting. What I'd like to have is another cells/columns that will auto-enter the names in the B column the proper # of times. E.g. If I have the following: R S 1 Steve 4 2 Tom 7 3 Don 9 So that Steve is entered in the B column 4 times, Tom 7 times, Don 9 times, etc. Is this possible ? Thanks, Steve |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to enter names in a column specific # of times
You're welcome!
-- Biff Microsoft Excel MVP "Steve" wrote in message ... Again, thank you for your patience. And yes, it's very slick, and exactly what I was trying to achieve. I couldn't follow some of the technical explanation, but the bottom line explanation using raffle tix made it very easy to understand. In case you're interested, the random process was to get a free agent draft order each week in our Fantasy Football league based on various criterea such as overall won-loss percent, points for, and # of prior free agent aquisions. Your help in getting the random generator to work as I hoped has been a perfect and fair solution. Thanks again for all your patience, creativity and skill. Steve "T. Valko" wrote: This is how it works... ........ R.........S.....T 1..Steve.....19.....1 2..Tom......17.....20 3..Don......12.....37 =INDEX(R1:R3,MATCH(RANDBETWEEN(1,SUM(S1:S3)),T1:T3 )) SUM(S1:S3) = 48 so: RANDBETWEEN(1,SUM(S1:S3)) returns a random number from 1 to 48 Let's assume that the random number is 27. Using the MATCH function and omitting the the match_type argument which then defaults to match_type 1 meaning the lookup_array is in ascending (which is what I have done), if an exact match of the lookup_value (our random number) is not found it will match the greatest value that is less than the lookup_value. Using our random number of 27 and the lookup_array in T1:T3 (1;20;37) there is no exact match so the greatest value that is less than 27 is 20 which is in position 2. So: RANDBETWEEN(1,SUM(S1:S3)) = 27 MATCH(27,T1:T3) = 2 INDEX(R1:R3,2) = Tom This is how it breaks out for each of the names: Random numbers from 1 to 19 = Steve Random numbers from 20 to 36 = Tom Random numbers from 37 to 48 = Don So: Random numbers from 1 to 19 means Steve can be selected up 19 times Random numbers from 20 to 36 means Tom can be selected up to 17 times Random numbers from 37 to 48 means Don can be selected up to 12 times Like I stated in my other reply, the formula is working as though the names have been repeated n number of times. This is actually pretty slick if think you about it! <g It's like these folks have bought raffle tickets and they're being picked out of a hat. Steve has 19 tickets, Tom has 17 tickets and Don has 12 tickets. So Steve has the best chance of being the winner. Hope that makes sense. -- Biff Microsoft Excel MVP "Steve" wrote in message ... One last thing: =INDEX(R1:R3,MATCH(RANDBETWEEN(1,SUM(S1:S3)),T1:T3 )) Here is R, S & T columns. R S T Steve 19 1 Tom 17 20 Don 12 37 With the T1 being 1 ( 1) T2 being 1+19 and (20) S1+T1 T3 being 17 + 20 (37) S2_T2 I guess I'm confusing over the T column. Is Don having the chance to come up 37 times vs. Tom 20 times & Steve only once ? I wanted to use the #'s in the S colum for the random probability such as Steve 19, Tom 17 & Don 12. Is the n number of times the S column or the T column ? Thanks, Steve "T. Valko" wrote: I should have explained this in my original reply. In essence the formula works as though each name was repeated n number of times. From reading your post it seemed that you wanted the list with repeats just for this single specific purpose. I didn't know whether the list was used for other things. Thanks for the feedback! -- Biff Microsoft Excel MVP "Steve" wrote in message ... Thanks for all your patience. I surely didn't mean to insist that I wanted that list, I was just adding that to the original random solution that was provied to me by this group a few years ago. With the Excel 2207 solution below, it works like a charm, and like you stated, a much cleaner and improved solution. Thanks much, Steve "T. Valko" wrote: Improvement: =INDEX(R1:R3,MATCH(RAND()*SUM(S1:S3)+1,T1:T3)) If you're using Excel 2003 or earlier and have the analysis ToolPak add-in installed or, if you're using Excel 2007: =INDEX(R1:R3,MATCH(RANDBETWEEN(1,SUM(S1:S3)),T1:T3 )) For any version: =INDEX(R1:R3,MATCH(ROUND(RAND()*SUM(S1:S3),0)+1,T1 :T3)) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... No need to create the repeated name list. Try this: R S 1 Steve 4 2 Tom 7 3 Don 9 In T1 enter 1 Enter this formula in T2 and copy down to T3 =S1+T1 Then: =INDEX(R1:R3,MATCH(RAND()*SUM(S1:S3)+1,T1:T3)) -- Biff Microsoft Excel MVP "Steve" wrote in message ... I'm using the following to produce a random picking of names in the B column. =INDEX($B$1:$B$100,RAND()*100+1) Each name is entered a different number of times in the B column, so that the names entered the most have a higher probability if hitting. What I'd like to have is another cells/columns that will auto-enter the names in the B column the proper # of times. E.g. If I have the following: R S 1 Steve 4 2 Tom 7 3 Don 9 So that Steve is entered in the B column 4 times, Tom 7 times, Don 9 times, etc. Is this possible ? Thanks, Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I enter a Formula for entire column? | Excel Worksheet Functions | |||
enter formula once for entire column | Excel Worksheet Functions | |||
i don't want to enter dublicate data in a specific column | Excel Discussion (Misc queries) | |||
count the number of times a specific word appears in a column | Excel Worksheet Functions | |||
how to enter a formula using column() function for a range | Excel Worksheet Functions |