Names out of a hat in excel

[jworkman7]

I have a worksheet which lists down column A, a number of employee names. In Column B, it lists the total number of inter-department referrals each employee has referred to someone else to generate sales.

I'd like to do a drawing which would randomly select a name, taking into account the number of referrals each person has. I realize I can use the RAND function easy enough to generate random numbers, but how can I make this work so that the probability is enhanced to favor those with more entries in the drawing?

 

[Eric W]

Here's one option:

Excel 2010

	A	B	C	D	E
Names	Referrals
Al	Cindy
Bob
Cindy
Diane
Eric
Frank
Gail
Harriet

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

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

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

[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]12[/TD]

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

[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]7[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]2[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

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

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

[TD="align: right"]11[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]4[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]6[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]=SUM($B$2:B2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]=RANDBETWEEN(1,SUM(B2:B9))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]{=INDEX(A1:A9,MIN(IF(D2<=$C$2:$C$9,ROW($C$2:$C$9))))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



The helper column in C shows the sum so far of total referrals, with the value in C9 being the total value. Then the formula in D2 calculates a random number between 1 and the total number of referrals. Finally, the E2 formula converts the D2 value to a name. In this example, 1 and 2 translate to Al, 3, 4, and 5 translate to Bob, 6-12 translate to Cindy, etc. It's not real elegant, but it works. You can do multiple drawings by pressing F9.

Let me know if this helps.

 

[jworkman7]

Hi Eric W, this will work great. Thanks for taking the time to put this together! I'll save it for future use.

 

[Eric W]

Happy to help. Thanks for the feedback.