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
1	Names	Referrals
2	Al	2	2	12	Cindy
3	Bob	3	5
4	Cindy	7	12
5	Diane	2	14
6	Eric	4	18
7	Frank	11	29
8	Gail	4	33
9	Harriet	6	39
10

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula C2 =SUM($B$2:B2) D2 =RANDBETWEEN(1,SUM(B2:B9)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula E2 {=INDEX(A1:A9,MIN(IF(D2<=$C$2:$C$9,ROW($C$2:$C$9))))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

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.