Raffle Ticket Odds on Winning

[FreakyGirl]

Hello

I'm trying to calculate the odds a person has on winning an upcoming raffle.

Number of Raffle Tickets: 1638
Number of Prizes Awarded: 19
Number of Tickets a person has: F column

I have tried 2 formulas but the results don't look right in either so I thought i would ask here.

The first formula in column B simply divides the players number of tickets by the total tickets.

=F11/$F$7

The result says that the person has only a 4% chance to win. This person has the most tickets (63) of anyone else & is only given a 4% chance? That answer cant be right.

The formula is column C considers the number of tickets a person has, the number of total tickets & the number of prizes.

1-HYPGEOMDIST(0,F11,19,$F$7)

but this formula doesn't seem to be right either looking at the results.

When the value is changed to a percentage the result seems to be (in most cases) very close to the same as the number of tickets a player has.

Can someone tell me if im using the formula correctly or maybe there's another solution.


Thanks,
FreakyGirl

 

[Chihiro]

You got it right (in HYPGEOMDIST calculation).

To give alternate method.
Assuming tickets are replenished (i.e. it's always going to be drawn from pool of 1638 tickets).

If player has total of 63 tickets... above assumption will make each draw independent events.

So probability that you will miss out on a prize is...
((1638-63)/1638)^19 ≈ 0.474642

Therefore odds of winning a prize is...
1 - 0.474642 ≈ 0.525358

Now, in actuality you will need binomial coefficient calculation. Since tickets are not being replenished as it's drawn. You will have higher chance of winning a prize than above calculation.

It's bit too much to explain here. But, if you search for the term, you should find a good explanation out there.

 

[Chihiro]

You can use COMBIN function to calculate more accurate result than my previous post (which is pretty much same as HYPGEOMDIST method).

With out going into too much detail...
Binomial coefficient is expressed as.

Which is the number of ways of picking r unordered outcomes from n possibilities. Also known as Combination/combinatorial number.

In Excel, you can use COMBIN(n, r) to calculate this.

Therefore for cumulative possibility of winning prize, using my previous post's scenario you will calculate...

= 1 - COMBIN(1638-63,19)/COMBIN(1638,19)
≈ 0.527351

 

[KenU]

@FreakyGirl,

I have attached an approximate solution and some explanation in the attached.

The area with blue shading is not needed (only added to explain/validate approach). I did add some names to assist with the actual calculations.

Hope it helps.

Regards,
Ken

 

[FreakyGirl]

Hello

Chihiro, ty for letting me know i was doing it right & for providing an alternative formula.

I did do a search as suggested and there are many examples & auto calculators available to do the calculations.

Ken, thanks for much for providing the table.

You can actually see their chance goes up by a little bit each time because there is 1 less ticket to choose from.

This is an excellent tool for anyone & easily adjustable for their needs.

Very much appreciated,
FreakyGirl