i have constructed a payoff table for a hypothetical lottery, draw 6 from 38 balls. trying to adjust the prize for each division for the estimated number of winners we'd have to split with. have just intuitively divided prize by expected winners (column K). HOWEVER, am stumped for how to discount the prize when expected winners < 1. the return column value obviously can't be >1. eg the probability of another winner is 0.16 - is the prize simply worth 0.84 of it's original value or is there a more correct way to look at this? 6 from 38 lotto balls correct probability odds prize estimated winners prize/winners return 6 3.62229E-07 2,760,681.00 500,000 0.16 3,125,000 1.13 5 6.95481E-05 14,378.55 20,000 31.00 645 0.04 4 0.002694987 371.06 10,000 1,186.00 8 0.02 3 0.035933163 27.83 - - - - 2 0.195386573 5.12 - - - - 1 0.437665924 2.28 - - - - total 1.19956

excel attached

cheers!

2. ## Re: Expected Value Adjustment

Make the "estimated number of winners" a minimum of one. Use the MIN function in Excel.

3. ## Re: Expected Value Adjustment

I meant use the MAX function in Excel.

4. ## Re: Expected Value Adjustment

Originally Posted by SlipEternal
Make the "estimated number of winners" a minimum of one. Use the MIN function in Excel.

i'm not sure that setting the expected number of winners to either 0 or 1 would accurately reflect the expected value of the prize if the number is set to zero, it implies the prize will never be split, which is untrue. similarly, a value of 1 implies that it will always be split with 1 other (ie half the EV) - which is also not true.

probability of 0.16 suggests another winner roughly 1/6th of the time.

can anyone tell me if (prize)*0.84 is the best approximation of the EV???

5. ## Re: Expected Value Adjustment

As I said, make it a minimum of one. That means it can never be zero. It will always be greater than or equal to 1. So, if it seems like it is 32, make it 32. If it seems like it is 0.16, make it 1.

6. ## Re: Expected Value Adjustment

Originally Posted by SlipEternal
As I said, make it a minimum of one. That means it can never be zero. It will always be greater than or equal to 1. So, if it seems like it is 32, make it 32. If it seems like it is 0.16, make it 1.

anyone else? comeon friends i'd imagine this is fairly simple for some of you!

7. ## Re: Expected Value Adjustment

My response was correct. No one else is responding because I answered your question. If the expected number of winners is 1, that means you expect exactly one person to win, not two (as you stated). There is no such thing as 0.16 winners. The minimum number of winners (if there is a winner) will always be one. Splitting the winnings into 0.16 portions means that the "less than one" winner would be receiving more money than is actually available. While division by 0.16 is obviously mathematically defined, it does not make sense in this application. In many applications of discrete math, 1 is the smallest number of divisions that makes sense. This is one of those applications. Hence my suggestion to set the expected number of winners to a minimum of one.

Zero winners would mean that no one wins the prize at all. Dividing the prize into zero portions is division by zero, which is mathematically not defined.

Edit: There are more sophisticated methods of dealing with 0.16 expected winners. For instance, you can weight the expected winnings by the probability that there is a winner. This would be vastly more complex, as it would require you to evaluate the distribution of the expected number of winners. This would give you more accurate estimates of expected winnings, but it is, again, far more difficult to calculate. For each prize, you multiply the prize money if there is exactly one winner by the probability that the prize will be won by exactly one winner. Then add half the prize money times the probability that the prize will be won by exactly two winners. Etc. This becomes an infinite sum for each prize that is guaranteed to converge (evaluating this type of statistics would likely be easier with a program like R rather than Excel).

8. ## Re: Expected Value Adjustment

Originally Posted by SlipEternal
There are more sophisticated methods of dealing with 0.16 expected winners. For instance, you can weight the expected winnings by the probability that there is a winner. This would be vastly more complex, as it would require you to evaluate the distribution of the expected number of winners. This would give you more accurate estimates of expected winnings, but it is, again, far more difficult to calculate. For each prize, you multiply the prize money if there is exactly one winner by the probability that the prize will be won by exactly one winner. Then add half the prize money times the probability that the prize will be won by exactly two winners. Etc. This becomes an infinite sum for each prize that is guaranteed to converge (evaluating this type of statistics would likely be easier with a program like R rather than Excel).
this is a nice idea - i'll have a go. thanks for your help!