Random function - weighted

[Guest]

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!

 

[Ragdyer]

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.

 

[Peri S]

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

 

[Excelenator]

I think this file will give you what you are looking for.

514

+-------------------------------------------------------------------
|Filename: Random Drawing.zip
|Download: http://www.excelforum.com/attachment.php?postid=5145
+-------------------------------------------------------------------

 

[Excelenator]

OK, made some modifications but this should do nicely for you. See th
instructions in Sheet1

514

+-------------------------------------------------------------------
|Filename: Random Drawing.zip
|Download: http://www.excelforum.com/attachment.php?postid=5146
+-------------------------------------------------------------------

 

[Guest]

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

---

 

[Guest]

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

---

 

[Peri S]

Awesome. You guys are the best. Thank you very much for the help.

 

[Guest]

You're welcome!
Thanks for the feedback ..