Random function - weighted

  • Thread starter Thread starter Guest
  • Start date Start date
G

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

---
 
Back
Top