Creating a spreadsheet to calculate probailities of winning raffles

[Steve]

I need help designing a spreadsheet that can be used to figure the
probability and odds of winning this raffle. Here is the situation:

1) there will only be 500,000 tickets sold.
2) there will be 2 first place prizes of $500,000.00
3) there will be 2 second place prizes of $25,000.00
4) there will be 30 third place prizes of $5,000.00
5) there will be 700 fourth place prizes of $250.00
6) Tickets cost $5.00

If I were to buy 50 tickets spending $250.00 (the amount of the
smallest prize)...

1) what are my odds of winning atleast one of the first place prizes?
2) what are my odds of winning both of the first place prizes?
3) what are my odds of winning atleast one of the second place
prizes?
4) what are my odds of winning both of the second place prizes?
5) what are my odds of winning atleast one of the third place prizes?
6) what are my odds of winning more than one of the third place
prizes?
7) what are my odds of winning atleast one of the fourth place
prizes?
8) what are my odds of winning more than one of the fourth place
prizes?

Similarly...

9) what is the probability that I will win atleast one first place
prizes?
10) what is the probability that I will win both first place prizes?
11) what is the probability that I will win atleast one second place
prizes?
12) what is the probability that I will win both second place prizes?
13) what is the probability that I will win atleast one third place
prize?
14) what is the probability that I will win more than one third place
prize?
15) what is the probability that I will win atleast one fourth place
prize?
16) what is the probability that I will win more than one fourth place
prize?

What I am looking for is the explanation of the formula(s) required to
answer
the questions above.

Can anybody help?

 

[Steve]

Oh yeah, I would like the spreadsheet to be generic enough that it can
be re-used for different raffles (ie. with different number of total
tickets sold, different number of prizes, etc.)

Thanks,
Steve

 

[Jerry W. Lewis]

We won't do your homework for you, but will help you to figure out how to do
it for yourself.

If this is not homework, you should remember that lotteries, etc. are a tax
on the mathematically challenged. In general, they are designed to raise
money, and hence by definition have a negative expected return for players.
The more you play, the more (on average) you will lose. People continue to
play because someone will win, and hope springs eternal ...

Your setup pays out just over half of what it takes in, so if you bought all
of the tickets, you would be certain of winning everything, but you woul also
end up $1,125,000 poorer than when you started.

For probability calculations, see Help on HYPGEOMDIST. There are 500,000
tickets and 734 prizes. The probability of winning nothing with 50 tickets
is then =HYPGEOMDIST(0,50,734,500000), i.e. 93% chance that you are simply
throwing away $250.

For the specific events that you are concerned with, you will adjust the
first and third agruments appropriately, and may have to sum several
probabilities. For instance the probability of winning at least one first
prize is
=HYPGEOMDIST(1,50,2,500000)+HYPGEOMDIST(2,50,2,500000)
or by the logic that winning at least one first prize is the alternative to
not winning a first prize, you could calculate
=1-HYPGEOMDIST(0,50,2,500000)

You may perfer to download
http://members.aol.com/iandjmsmith/Examples.xls
and use its cdf_hypergeometric function instead of summing things.

Good luck with your assignment,
Jerry

 

[Steve]

We won't do your homework for you, but will help you to figure out how to do
it for yourself.

If this is not homework, you should remember that lotteries, etc. are a tax
on the mathematically challenged. In general, they are designed to raise
money, and hence by definition have a negative expected return for players.
The more you play, the more (on average) you will lose. People continue to
play because someone will win, and hope springs eternal ...

Your setup pays out just over half of what it takes in, so if you bought all
of the tickets, you would be certain of winning everything, but you woul also
end up $1,125,000 poorer than when you started.

For probability calculations, see Help on HYPGEOMDIST. There are 500,000
tickets and 734 prizes. The probability of winning nothing with 50 tickets
is then =HYPGEOMDIST(0,50,734,500000), i.e. 93% chance that you are simply
throwing away $250.

For the specific events that you are concerned with, you will adjust the
first and third agruments appropriately, and may have to sum several
probabilities. For instance the probability of winning at least one first
prize is
=HYPGEOMDIST(1,50,2,500000)+HYPGEOMDIST(2,50,2,500000)
or by the logic that winning at least one first prize is the alternative to
not winning a first prize, you could calculate
=1-HYPGEOMDIST(0,50,2,500000)

You may perfer to downloadhttp://members.aol.com/iandjmsmith/Examples.xls
and use its cdf_hypergeometric function instead of summing things.

Good luck with your assignment,
Jerry










- Show quoted text -

Thank you for the help.

For the record this is not a homework assignment (I am a 44 year old
grandfather and have not been in school for many years). I am also
quite familiar with the fact that winning is a long shot. A friend of
mine has proposed that this setup gives him satifactory odds. I
wanted to show him the actual odds/probabilities numericly. I am just
not sure how to calculate the numbers to prove the point to my friend.

BTW, this is an actual lottery raffle in the state of KY (http://
www.kylottery.com/raffle.html)

So how about calculating the odds?

Steve

 

[Jerry W. Lewis]

If that is your objective, then you are working way to hard. With a fixed
number of tickets and a fixed payout, the expected return per ticket is
simply the ratio of the total payout to the total cost of tickets
(1,375,000/2,500,000); that is on average you will win back only 55% of what
you spend on tickets. No buying scheme can alter those odds. The only way
to improve them is to rig the game, i.e. somehow make it physically more
likely that your particular tickets are the ones selected. Hopefully rigging
a state lottery is not possible.

To see that your friend’s scheme does not improve his odds, consider the
possibility that 10,000 people might each use his strategy. Among them, they
would purchase all of the tickets, and (absent a rigged game) would each have
identical odds of winning. Since their expected returns would all be equal
and their total collective return (1,375,000) and total collective
expenditure (2,500,000) are known, they would average winning $137.5 per
player from their $250 per player ticket purchases. Granted a few players
would win more than $137.5, but most would win nothing, and no aspect of the
purchasing scheme can improve the odds of any one person over the others. In
reality, your friend will not be competing against 9,999 other players all
buying the same number of tickets as he, but the actual buying patterns of
the other players can do nothing to help or hurt your friend’s odds in this
game; i.e. his expected return will still only be 55% of his investment.

Again, to calculate the actual probabilities, read help for the HYPGEOMDIST
function and follow the examples in my original reply. Give it a try; you
are never too old to learn. You can post back your attempts for a critique.

Jerry

 

[Steve]

If that is your objective, then you are working way to hard. With a fixed
number of tickets and a fixed payout, the expected return per ticket is
simply the ratio of the total payout to the total cost of tickets
(1,375,000/2,500,000); that is on average you will win back only 55% of what
you spend on tickets. No buying scheme can alter those odds. The only way
to improve them is to rig the game, i.e. somehow make it physically more
likely that your particular tickets are the ones selected. Hopefully rigging
a state lottery is not possible.

That statement is ofcoarse true but who cares what the average will
be? No
one will walk away with the average. As you say most will walk away
with
nothing and few will walk away with something. What Jim (thats my
friends
name) is arguing is that by spending $250.00 he has balanced his risk/
reward
ratio to a level which he is comfortable with. I am not arguing the
validity
of his determination of comfortable...I just think he is incorrectly
calculating the likely hood that he will infact be in the group of
individuals
that walk away with something, although I am beginning to wonder if he
might
not be correct.

To see that your friend's scheme does not improve his odds, consider the
possibility that 10,000 people might each use his strategy. Among them, they
would purchase all of the tickets, and (absent a rigged game) would each have
identical odds of winning. Since their expected returns would all be equal
and their total collective return (1,375,000) and total collective
expenditure (2,500,000) are known, they would average winning $137.5 per
player from their $250 per player ticket purchases. Granted a few players
would win more than $137.5, but most would win nothing, and no aspect of the
purchasing scheme can improve the odds of any one person over the others. In
reality, your friend will not be competing against 9,999 other players all
buying the same number of tickets as he, but the actual buying patterns of
the other players can do nothing to help or hurt your friend's odds in this
game; i.e. his expected return will still only be 55% of his investment.

Again Jim is not trying to improve his odds over any other individual,
although
his odds will be greater than any other individual who bought fewer
tickets.
All he is trying to do is increase his odds of being among the
winners.
Obviously he does indeed increase this likelyhood with each additional
ticket he
purchases. He says that he has a 93% chance of winning nothing.
This, by
definition, means he has a 7% of atleast breaking even. He goes on to
say that
7% probability is the same as 1:15 odds. This is where I have a
problem. I
believe his odds are more like 1:681 (734:500000 = 734/734:500000/734
= 1:681)
but as I stated above I am not sure any more.

Thanks again for your input,
Steve

 

[Rick]

That statement is ofcoarse true but who cares what the average will
be? No
one will walk away with the average. As you say most will walk away
with
nothing and few will walk away with something. What Jim (thats my
friends
name) is arguing is that by spending $250.00 he has balanced his risk/
reward
ratio to a level which he is comfortable with. I am not arguing the
validity
of his determination of comfortable...I just think he is incorrectly
calculating the likely hood that he will infact be in the group of
individuals
that walk away with something, although I am beginning to wonder if he
might
not be correct.


Again Jim is not trying to improve his odds over any other individual,
although
his odds will be greater than any other individual who bought fewer
tickets.
All he is trying to do is increase his odds of being among the
winners.
Obviously he does indeed increase this likelyhood with each additional
ticket he
purchases. He says that he has a 93% chance of winning nothing.
This, by
definition, means he has a 7% of atleast breaking even. He goes on to
say that
7% probability is the same as 1:15 odds. This is where I have a
problem. I
believe his odds are more like 1:681 (734:500000 = 734/734:500000/734
= 1:681)
but as I stated above I am not sure any more.

Thanks again for your input,
Steve

I think what youare describing is basically the "Gamblers ruin problem" see
attched link http://math.ucsd.edu/~anistat/gamblers_ruin.html

Rick