Hi everyone,

I've got a toughy question for you, but it bears some explaining.

I have a growing list of people that have given me referrals to our agency. At the end of every month, we conduct a lottery to see who of the referral-senders will win a gift card. Each time someone gives me a referral, I enter their name once into a column in Excel. However, I can receive multiple referrals from one person, and that is fine. In fact, the more referrals they send me, the greater chance of them being chosen for the lottery.

Here is what I would like to do:

Column A: Name of Person
Column B: # of total referrals they have sent.

I would like to have a cell that selects the name of one person in that list, taking into account the total number of referrals he/she has sent in.

For example:
- Bob has sent in 10 referrals
- Sally has sent in 2 referrals
- Justin has sent in 8 referrals.

Column A only has 3 names, and I don't want the probability of choosing any given person to be a 1/3 chance. Rather, I want Excel to know that Bob has a 50% chance of winning the lottery, while Sally has a 10% chance, and Justin has a 40% chance. Consequently, based on that information, I want Excel to choose a name for me.

How could I set this up?

Thanks!