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
1	Name	Tickets	Sum	Random #	Winner
2	Bob Smith	13	0	48	Ann Smith
3	Tim Harding	30	13
4	Ann Smith	25	43
5	Mary Jones	18	68
6	Alice Cooper	20	86

<tbody>
</tbody>

Sheet6

Worksheet Formulas Cell Formula C2 =IF(A2="","",SUM($B$1:$B1)) D2 =RANDBETWEEN(<fon 1,SUM(B:B))-1</fon E2 =INDEX(A2:A100,MATCH(D2,C2:C100)) <tbody> </tbody>

<tbody>
</tbody>

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.

	A	B	C	D	E	F
1	Name	Tickets	Total of Tickets		List of Names
2	Bob Smith	13	13		Bob Smith
3	Tim Harding	18	31		Bob Smith
4	Paul Gold	12	43		Bob Smith
13					Bob Smith
14					Bob Smith
15					Tim Harding
31					Tim Harding
40					Paul Gold
44					Paul Gold
45
***	***********	*******	**************	**	*************	**

<tbody>
</tbody>

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