Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Random function - weighted
hello,
I need to do a drawing for a prize and I have a list of 100 names. they are in column A. Each person has a different number of points. For each point I want the random selection to be weighted. So if Tom has 5 points, Jim has 3, and Tammy has 2 then Tom would have 5 chances to win, Jim 3, and Tammy would have one. Here is a sample: name Grand Total of all Points ralph 16 tom 10 dick 9 harry 8 jim 2 jr 2 mike 2 mark 1 robert 1 tim 1 In this example, how can Ralph have 16 times greater chance at winning than Tim? Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Random function - weighted
I would expand the name list to include duplicates of a name, commensurate
with the number of points. 5 Toms 3 Jims 2 Tammys .... etc. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Periz28" wrote in message ... hello, I need to do a drawing for a prize and I have a list of 100 names. they are in column A. Each person has a different number of points. For each point I want the random selection to be weighted. So if Tom has 5 points, Jim has 3, and Tammy has 2 then Tom would have 5 chances to win, Jim 3, and Tammy would have one. Here is a sample: name Grand Total of all Points ralph 16 tom 10 dick 9 harry 8 jim 2 jr 2 mike 2 mark 1 robert 1 tim 1 In this example, how can Ralph have 16 times greater chance at winning than Tim? Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Random function - weighted
I was hoping to avoid that because there are points for each person for each
month so that would get way up in the thousands... "Ragdyer" wrote in message ... I would expand the name list to include duplicates of a name, commensurate with the number of points. 5 Toms 3 Jims 2 Tammys ... etc. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Periz28" wrote in message ... hello, I need to do a drawing for a prize and I have a list of 100 names. they are in column A. Each person has a different number of points. For each point I want the random selection to be weighted. So if Tom has 5 points, Jim has 3, and Tammy has 2 then Tom would have 5 chances to win, Jim 3, and Tammy would have one. Here is a sample: name Grand Total of all Points ralph 16 tom 10 dick 9 harry 8 jim 2 jr 2 mike 2 mark 1 robert 1 tim 1 In this example, how can Ralph have 16 times greater chance at winning than Tim? Thanks! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Random function - weighted
I think this file will give you what you are looking for. 5145 +-------------------------------------------------------------------+ |Filename: Random Drawing.zip | |Download: http://www.excelforum.com/attachment.php?postid=5145 | +-------------------------------------------------------------------+ -- Excelenator ------------------------------------------------------------------------ Excelenator's Profile: http://www.excelforum.com/member.php...o&userid=36768 View this thread: http://www.excelforum.com/showthread...hreadid=568190 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Random function - weighted
OK, made some modifications but this should do nicely for you. See the instructions in Sheet1 5146 +-------------------------------------------------------------------+ |Filename: Random Drawing.zip | |Download: http://www.excelforum.com/attachment.php?postid=5146 | +-------------------------------------------------------------------+ -- Excelenator ------------------------------------------------------------------------ Excelenator's Profile: http://www.excelforum.com/member.php...o&userid=36768 View this thread: http://www.excelforum.com/showthread...hreadid=568190 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Random function - weighted
... how can Ralph have 16 times greater chance at winning than Tim?
Try this play as well .. Assume the source table is in A1:B11 (headers in row1) First, just data sort the table in ascending order by total points (col B), viz.: name Grand Total of all Points mark 1 robert 1 tim 1 jim 2 jr 2 mike 2 harry 8 dick 9 tom 10 ralph 16 Then enter a zero in C2 place in C3: =SUM($B$2:B2) Copy C3 down to C12 (ie copy down by one cell more than the last row in col B) Leave C1 empty Then, in any empty cell, just put: =INDEX(A:A,MATCH(RANDBETWEEN(0,MAX(C:C)),C:C,1)) to generate the required "weighted" random draw which takes into account the commensurate chances by each name's points in col B. This is achieved via col C which produces the unique "buckets/tiers" corresponding to the sorted points in col B. Press F9 to re-generate / re-draw Note that RANDBETWEEN requires the Analysis Toolpak to be installed and activated. Check the "Analysis Toolpak" box (via Tools Add-Ins). Chip Pearson's page has details on the ATP at:http://www.cpearson.com/excel/ATP.htm -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Peri S" wrote: I was hoping to avoid that because there are points for each person for each month so that would get way up in the thousands... "Ragdyer" wrote in message ... I would expand the name list to include duplicates of a name, commensurate with the number of points. 5 Toms 3 Jims 2 Tammys ... etc. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Random function - weighted
Line
Then, in any empty cell, just put: should have read as: Then, in say, E2: Place the draw formula in a cell to the right of the construct .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Random function - weighted
Awesome. You guys are the best. Thank you very much for the help.
"Max" wrote in message ... ... how can Ralph have 16 times greater chance at winning than Tim? Try this play as well .. Assume the source table is in A1:B11 (headers in row1) First, just data sort the table in ascending order by total points (col B), viz.: name Grand Total of all Points mark 1 robert 1 tim 1 jim 2 jr 2 mike 2 harry 8 dick 9 tom 10 ralph 16 Then enter a zero in C2 place in C3: =SUM($B$2:B2) Copy C3 down to C12 (ie copy down by one cell more than the last row in col B) Leave C1 empty Then, in any empty cell, just put: =INDEX(A:A,MATCH(RANDBETWEEN(0,MAX(C:C)),C:C,1)) to generate the required "weighted" random draw which takes into account the commensurate chances by each name's points in col B. This is achieved via col C which produces the unique "buckets/tiers" corresponding to the sorted points in col B. Press F9 to re-generate / re-draw Note that RANDBETWEEN requires the Analysis Toolpak to be installed and activated. Check the "Analysis Toolpak" box (via Tools Add-Ins). Chip Pearson's page has details on the ATP at:http://www.cpearson.com/excel/ATP.htm -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Peri S" wrote: I was hoping to avoid that because there are points for each person for each month so that would get way up in the thousands... "Ragdyer" wrote in message ... I would expand the name list to include duplicates of a name, commensurate with the number of points. 5 Toms 3 Jims 2 Tammys ... etc. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Random function - weighted
You're welcome!
Thanks for the feedback .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Peri S" wrote: Awesome. You guys are the best. Thank you very much for the help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
numerical integration | Excel Discussion (Misc queries) | |||
Random numbers function | Excel Worksheet Functions | |||
VBA "Rnd" Function: Truly Random? | Excel Discussion (Misc queries) | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions |