A formula to create the # of names you need for a "drawing"

[export9]

Say Bob Smith earned 13 raffle tickets
Tim Harding earned 30, etc etc.

Is there a formula that you can input that will create 13 versions of Bob Smiths name and 30 of Tim Hardings?

 

[Eric W]

Welcome to the board.

If you're looking for a way to pick a winner based on the number of tickets each person has, consider:

	A	B	C	D	E
Name	Tickets	Sum	Random #	Winner
Bob Smith	Ann Smith
Tim Harding
Ann Smith
Mary Jones
Alice Cooper

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]13[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]48[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]30[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]25[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]18[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"]86[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet6


[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]C2[/TH]
[TD="align: left"]=IF(A2="","",SUM($B$1:$B1))[/TD]
[/TR]
[TR]
[TH]D2[/TH]
[TD="align: left"]=RANDBETWEEN(<fon
1,SUM(B:B))-1</fon
[/TD]
[/TR]
[TR]
[TH]E2[/TH]
[TD="align: left"]=INDEX(A2:A100,MATCH(D2,C2:C100))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]




The names are in A, the number of tickets are in B, column C finds the sum of the tickets so far. D2 picks a random number from 1 to the total number of tickets, less 1. So if the random number is 0-12, Bob wins, if it's from 13-42, Tim wins, etc.

Let us know if this works for you.

 

[markmzz]

Say Bob Smith earned 13 raffle tickets
Tim Harding earned 30, etc etc.

Is there a formula that you can input that will create 13 versions of Bob Smiths name and 30 of Tim Hardings?

Hi!

If I understand correctly what you want, maybe the suggestion below can helps.

Try this:

1) Create a table with the name Table with the columns Name, Tickets and Total of Tickets. Look at the table below.

[TABLE="class: grid, width: 410"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]Tickets[/TD]
[TD]Total of Tickets[/TD]
[TD][/TD]
[TD]List of Names[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Bob Smith[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]13[/TD]
[TD][/TD]
[TD]Bob Smith[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Tim Harding[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]31[/TD]
[TD][/TD]
[TD]Bob Smith[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Paul Gold[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]43[/TD]
[TD][/TD]
[TD]Bob Smith[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Bob Smith[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Bob Smith[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Tim Harding[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Tim Harding[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]40[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Paul Gold[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]44[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Paul Gold[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]45[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]***********[/TD]
[TD]*******[/TD]
[TD]**************[/TD]
[TD]**[/TD]
[TD]*************[/TD]
[TD]**[/TD]
[/TR]
</tbody>[/TABLE]


2) After that, put the formula below in C2 (Total of Tickets column):

=SUM(B$2:B2)

3) Finally, put the formula below in E2 and copy down

=IF(ROWS(E$2:E2)>MAX(Table[Total of Tickets]),"",INDEX(Table[Name],MATCH(1,INDEX(--(Table[Total of Tickets]>=ROWS(E$2:E2)),),0)))

Markmzz