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!

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!

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!

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

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

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.

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

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.

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.