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
**** 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.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Periz28" <[email protected]> wrote in message
news:[email protected]...
> 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
> **** 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 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" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>> **** 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!
>>
>

>> ... 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
**** 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" <[email protected]> wrote in message
> news:[email protected]...
> >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.

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

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


"Max" <[email protected]> wrote in message
news:[email protected]...
>>> ... 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
> **** 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" <[email protected]> wrote in message
>> news:[email protected]...
>> >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.
>

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.