Choose Random Value Based On List

**kblaine** · 04-19-2011, 03:06 PM

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!

**martindwilson** · 04-19-2011, 03:44 PM

something like this maybe

**ChemistB** · 04-19-2011, 03:50 PM

Starting your data in row 2 (A2:B4), in C1, enter 0, in C2 dragged down
=B2/SUM(B:B)+C1

Your pick
=INDEX($A$2:$A$4, MATCH(RAND(),$C$1:$C$3,1))

You can hide column C

**kblaine** · 04-20-2011, 10:36 AM

Thank you both! Both ideas work flawlessly.

I have a question though ChemistB - Forgive my ignorance, but why does column C's formula need the +C1 (and subsequently +C2, +C3, etc.) What's the rationale?

Also, martindwilson - can you explain Column F's formula? Ex. =IF(E3="","",RAND())

Thanks!

**ChemistB** · 04-20-2011, 10:49 AM

In my spreadsheet, the rand function spits out a # between 0.0001 and 0.9999 (more or less) which relates directly to a percentage between 0 and 100. Column C gives each person, based on the # of referrals, a chunk of that 100%. Let's say that each person (4 people) got the same amount of referrals. Then each would have 0.25 or 25%. When the MATCH function looks there, we need those numbers to be different and adding to 100% (or 1). Thus the +C1 adds the % of the previous people to that person so we cover the entire range like so
Tom 0.25
Bob 0.50
Bill 0.75
Larry 1
Hope that makes sense.

**martindwilson** · 04-20-2011, 02:41 PM

mine just sticks a random number ,if cell in that case e3 is not blank ,then picks the largest there is more chance the large number will appear in someone with 11 entries than 2