Thread Rating:

To begin, recall var(x + y) = var(x) + var(y) + 2*cov(x,y)

In this case of this game, var(entire game) = var(base game) + var(bonus) + 2*cov(base game,bonus)

Let's start with the variance of the base game.

Catch | Pays | Winning combinations | Probability | Return | exp win^2 |
---|---|---|---|---|---|

0 | 0 | 2,558,620,845 | 0.088266 | 0.000000 | 0.000000 |

1 | 0 | 7,724,138,400 | 0.266464 | 0.000000 | 0.000000 |

2 | 0 | 9,512,133,400 | 0.328146 | 0.000000 | 0.000000 |

3 | 0 | 6,226,123,680 | 0.214786 | 0.000000 | 0.000000 |

4 | 3 | 2,362,591,575 | 0.081504 | 0.244511 | 0.733533 |

5 | 10 | 530,546,880 | 0.018303 | 0.183026 | 1.830259 |

6 | 56 | 68,605,200 | 0.002367 | 0.132536 | 7.422014 |

7 | 180 | 4,651,200 | 0.000160 | 0.028882 | 5.198747 |

8 | 1000 | 125,970 | 0.000004 | 0.004346 | 4.345661 |

Total | 28,987,537,150 | 1.000000 | 0.593301 | 19.530214 |

As a reminder, the variance equals exp(x^2) - (e(x))^2.

So, var(base game) = 19.530214 - 0.593301^2 = 19.178208.

Next, let's do the variance of the bonus, given that the player won the bonus in the first place. Recall var(ax) = a^2 * var(x). In the bonus there are 12 doubled free games. So the variance of a bonus would be...

12 * 2^2 * 19.178208 = 920.554000.

However, the player doesn't always win the bonus. This was a tricky step. It would have been nice to just multiply that by the probability of winning the bonus of 0.021644, but you can't.

Let's do the easy part first. The average bonus win is 12*2*0.593301 = 14.239212. The expected win from the bonus on any given spin is prob(bonus)*(average bonus) = 0.021644 * 14.23921236 = 0.308198. Not that we directly care, but the overall return of the game is exp(base game) + exp(bonus) = 0.593301 + 0.308198 = 0.901498.

To find the exp(x^2) of the bonus, recall variance = exp(x^2) - (e(x))^2.

To rearrange:

exp(x^2) = var(x) + (e(x))^2.

In the case of the bonus, given a bonus win in the first place:

exp(x^2) = 920.554000 + 14.239212^2 = 1123.309169.

Now we're ready to find the overall variance from the bonus (including when the player doesn't win it):

We already know exp(bonus win) = 0.308198.

exp((bonus win)^2) = 0.021644 * 1123.309169 = 24.313239.

Thus the variance of the bonus on each spin is 24.313239 - 0.308198^2 = 24.218253.

Next, let's do the covariance. Why is there any covariance, you might ask? It's because the player has to hit a winning ball on the last draw to trigger the bonus. Given the fact that the last ball won makes it more likely the player won money in the base game on that spin. In other words, winning the bonus is correlated to winning something on the base spin.

We'll need to know the expected win in the base game, given that the bonus was won. Here is that table:

Catch | Pays | Winning combinations | Probability | Return |
---|---|---|---|---|

0 | 0 | - | 0.000000 | 0.000000 |

1 | 0 | - | 0.000000 | 0.000000 |

2 | 0 | - | 0.000000 | 0.000000 |

3 | 0 | - | 0.000000 | 0.000000 |

4 | 3 | 472,518,315 | 0.753119 | 2.259358 |

5 | 10 | 132,636,720 | 0.211402 | 2.114019 |

6 | 56 | 20,581,560 | 0.032804 | 1.837010 |

7 | 180 | 1,627,920 | 0.002595 | 0.467036 |

8 | 1000 | 50,388 | 0.000080 | 0.080310 |

Total | 627,414,903 | 1.000000 | 6.757734 |

From our college statistics class, we know cov(x,y) = exp(xy) - exp(x)*exp(y).

Exp(xy) = 0.021644 * 6.757734 * 14.239212 = 2.082719.

So, the overall covariance is 2.082719 - 0.593301 * 0.308198 = 1.899865.

Thus, our bottom line is:

var(base game) + var(bonus) + 2*cov(base game,bonus) = 19.178208 + 24.218253 - 2*1.899865 = 47.19619.

Here is a summary of the key numbers.

Exp base win | 0.593301 |

Exp bonus | 0.308198 |

Return of game | 0.901498 |

Average base win given bonus won | 6.757734 |

Average bonus | 14.239212 |

Prob bonus | 0.021644 |

Variance base win | 19.178208 |

Variance bonus, given bonus | 920.554000 |

win^2 in bonus | 1123.309169 |

Variance bonus | 24.218253 |

covariance | 1.899865 |

total variance | 47.196191 |

I hope the one who asked me (I'm not sure if I can state his name) is happy, this took hours, including running a simulation to confirm the answer.

Lots of people playing 8 to 10 spot penny or nickel Keno to win the big prizes though.

Quote:WizardAs usual, a post that took me hours to write gets no replies.

Come on Mike, it's only been 3 hours since you posted it. :P

And well done! I haven't done much work with covariance. I took college statistics, but I definitely didn't learn covariance well and definitely hadn't seen it applied to a gaming example with careful detail. This is a fairly difficult calculation for most adults to do, and I think you guided the reader very well. So if even the person who asked didn't learn anything, at least I did!

Also writing any post on here that uses a table is a royal PITA and will take forever to write unless you have a nice script to add rows and columns.

Quote:WizardAs usual, a post that took me hours to write gets no replies.

Sorry. I hang out at a website that taught me Keno was for suckers so I tend not to read articles about the game.

4 card Cleo is even more funner

I've been using http://miplet.net/table/ for a long time. Just copy and paste from a spreadsheet.Quote:tringlomaneAlso writing any post on here that uses a table is a royal PITA and will take forever to write unless you have a nice script to add rows and columns.

Quote:mipletI've been using http://miplet.net/table/ for a long time. Just copy and paste from a spreadsheet.

I was hoping you'd repost that. I thought you wrote one.

Quote:RomesI just saw this thread, have been out of state... This looks awesome! Thank you for looking through this and responding in such detail. I didn't really want my name attached with this but I want to thank you!

You're welcome. You also made Ask the Wizard column #313.

I'd also like to run a simulation to confirm my result but I'm struggling with this one since it involves random samples of 20 from 80 WITHOUT REPLACEMENT. Those last two words make it difficult since I only know Excel (not a programmer). Does anyone know a relatively slick way in Excel to generate sets of random samples? Since the variance here is high, I guesstimate it would take at least a million random samples to nail this down a sufficiently precise number...like to be confident within 1/10th of 1 percent.

To reiterate I just need a way to generate a bunch of random sets of 20 winning numbers from a standard 80/20 Keno game. With that I can complete this simulation.

I've found various ways to easily generate such a random 20/80 sample ONCE...there's even an Add-On Tool that does it. But as mentioned I will need hundreds of thousands or millions of random samples...not one sample.

Thanks in advance.

Quote:Ace2I made a calculation of the total variance using a very different method and I came up with a slightly different number, within about 1% of the Wizard's.

I'd also like to run a simulation to confirm my result but I'm struggling with this one since it involves random samples of 20 from 80 WITHOUT REPLACEMENT. Those last two words make it difficult since I only know Excel (not a programmer). Does anyone know a relatively slick way in Excel to generate sets of random samples? Since the variance here is high, I guesstimate it would take at least a million random samples to nail this down a sufficiently precise number...like to be confident within 1/10th of 1 percent.

To reiterate I just need a way to generate a bunch of random sets of 20 winning numbers from a standard 80/20 Keno game. With that I can complete this simulation.

I've found various ways to easily generate such a random 20/80 sample ONCE...there's even an Add-On Tool that does it. But as mentioned I will need hundreds of thousands or millions of random samples...not one sample.

Thanks in advance.

There appear to be done answers and formulas you can adapt in this link. They're not doing exactly what you are, but it seems similar.

https://www.excelforum.com/excel-formulas-and-functions/1122639-keno-formula.html

Quote:Ace2I made a calculation of the total variance using a very different method and I came up with a slightly different number, within about 1% of the Wizard's.

I'd also like to run a simulation to confirm my result but I'm struggling with this one since it involves random samples of 20 from 80 WITHOUT REPLACEMENT. Those last two words make it difficult since I only know Excel (not a programmer). Does anyone know a relatively slick way in Excel to generate sets of random samples? Since the variance here is high, I guesstimate it would take at least a million random samples to nail this down a sufficiently precise number...like to be confident within 1/10th of 1 percent.

To reiterate I just need a way to generate a bunch of random sets of 20 winning numbers from a standard 80/20 Keno game. With that I can complete this simulation.

I've found various ways to easily generate such a random 20/80 sample ONCE...there's even an Add-On Tool that does it. But as mentioned I will need hundreds of thousands or millions of random samples...not one sample.

Thanks in advance.

Ace2,

You can use Excel spreadsheets (without the need to use VBA code) to generate selections of 20 balls from 80 total without replacement. Here's one simple method that will play 100 games each time you hit the "F9" (or FN-F9 on a laptop) function key.

Start a new Excel workbook. Rename "Sheet1" to "Keno", add a second worksheet, and name it "Random".

On "Random", leave the first four rows blank (this will allow you room to add features later). In cell A5, type Game. In B5, type #1. In C5, type #2. Select cells B5 and C5 (to do this, point the cursor at the middle (NOT the edge) of Cell B5, click and hold the left mouse button, and then drag so that Cell C5 is also selected). Release the mouse button. Now carefully point the cursor to the little black square on the bottom right corner of Cell C5, click that square, and drag to the right until you reach Cell CC5. Release the mouse button, and Excel will automatically add the labels #3, #4, …, #80. These represent the 80 numbered balls.

Click on Cell A6 and enter the number 1. Then enter the number 2 in A7. Select A6 and A7, click the black square at the bottom right of Cell A7, and drag down to Cell A105. Excel will number the rows 1, 2, 3, …, 100, to represent the 100 games.

To save a bit of typing, at this point select the range of Cells A5:U105 and click Copy. Switch back to the "Keno" worksheet, click in Cell A5, then click Paste.

Now switch back to the "Random" worksheet. Click in Cell B6 and type this formula:

=RAND()

Be sure to type the = sign as well as the open and close parentheses.This formula tells Excel to generate a random number between 0 and 1, and place it in Cell B6. Note that the RAND() function is "volatile": this means that each time you make a change to the workbook, Excel will generate a new random number.

Select Cell B6, then click the black square in the bottom right corner of B6 and drag across to Cell CC6. Release the mouse button. Notice that the cells B6:CC6 are still selected. Now point at the black square in the bottom right corner of CC6 and double-click: this tells Excel to copy the selected cells (B6:CC6) all the way down to Row 105.

Ok... we're finished working on the "Random" worksheet. Switch back to the "Keno" worksheet. In Cell B6 carefully enter this formula:

=RANK.EQ(Random!B6,Random!$B6:$CC6)

Don't miss the period in RANK.EQ, and don't forget the exclamation points and the dollar signs. This formula tells Excel to look at the range of cells Random!$B6:$CC6 (that is, cells B6 to CC6 on the "Random" worksheet) and determine where in that range of values the number in Cell Random!B6 ranks. For example, if the B6 value is the largest in the range, Excel returns the result "1": if it's the 15th largest value, Excel returns "15".

Select Cell B6, then click the black square in the bottom right corner of B6 and drag across to Cell U6. Release the mouse button. Notice that the cells B6:U6 are still selected. Now point at the black square in the bottom right corner of U6 and double-click: this tells Excel to copy the selected cells (B6:U6) all the way down to Row 105.

Yea! We're finished!

On the "Keno" worksheet, row 6 represents the 20 numbers drawn in Game 1; row 7 represents the 20 numbers drawn in Game 2, etc., all the way down to Game 100 in row 105. To generate another set of 100 games, click the F9 function key (or the FN-F9 combination on a laptop).

Note that when Excel recalculates, first it does all the rankings, then it once again randomizes the RAND() values. This means that even though Excel told you that the random value in Cell B6 was the 45th largest value, if you go to look it most likely will NOT be the 45th largest value in the new set of random numbers.

Ok... so what if you need more than 100 games? Well, each Excel worksheet can contain slightly more than one million rows, so you can simply expand each of the two worksheets to contain as many rows as necessary.

Why did I leave rows 1-4 blank? I thought you might need to put in some functions to analyze the results of each set of games.

Below is a sample of the output for 5 games (thanks again to miplet for his table-maker!):

Game | #1 | #2 | #3 | #4 | #5 | #6 | #7 | #8 | #9 | #10 | #11 | #12 | #13 | #14 | #15 | #16 | #17 | #18 | #19 | #20 |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|

1 | 45 | 40 | 28 | 51 | 80 | 4 | 29 | 61 | 63 | 75 | 60 | 26 | 42 | 13 | 44 | 79 | 48 | 64 | 6 | 69 |

2 | 56 | 23 | 7 | 25 | 35 | 6 | 36 | 55 | 41 | 72 | 76 | 51 | 66 | 63 | 45 | 69 | 11 | 60 | 31 | 75 |

3 | 45 | 50 | 34 | 56 | 21 | 27 | 22 | 63 | 79 | 15 | 26 | 65 | 76 | 67 | 24 | 53 | 43 | 38 | 4 | 49 |

4 | 62 | 2 | 6 | 52 | 10 | 73 | 26 | 72 | 12 | 70 | 35 | 54 | 20 | 67 | 43 | 24 | 14 | 53 | 22 | 27 |

5 | 79 | 5 | 1 | 31 | 55 | 54 | 68 | 52 | 41 | 8 | 7 | 62 | 51 | 47 | 2 | 22 | 10 | 15 | 28 | 53 |

If you're having trouble entering the formulas, send me a PM with an email address and I'll send a copy of the Excel file.

Hope this helps!

Dog Hand

I calculated this one before and had a slightly different number than the Wizard’s but I found my error and now agree.

I did this the “standard” way which is to list out all possible outcomes and then add up the weighted squared differences from the mean. It might not be the most efficient way but it is intuitive.

We already know that the base game has a return (R) of 0.593 and a variance (V) of 19.2. So this game is statistically similar (same return and variance) to a binary game with probability of winning (P) = 0.0180236 and payout (F) = 32.9.

P = 1 / ( V / R^2 + 1 )

F = R / P

Since the bonus round is simply 12 base games at double the payout, we also know that the return of the bonus round (r) = R * 12 * 2 = 14.2 with variance (v) = V * 12 * 2^2 = 920 (as the Wizard showed). Here’s where we reduce the possible outcomes by a factor of zillions: as it stands there are 6 possible outcomes (5 winning and 1 losing) for all 12 games, so there are 6^12 = 2.2 billion possible outcomes for every bonus round. But we can reformat this scenario to 1 statistically similar binary game with a p value = 1 / (920 / 14.2^2 + 1 ) = 0.180498 and a payout (f) = 14.2 / 0.180 = 78.9

We already have the base game table of:

Table 1 | |||||||
---|---|---|---|---|---|---|---|

Payout | |||||||

Catch | Probability | Payout | Return | less .5933 | Squared | Extended | |

<4 | 0.8977 | - | 0 | -0.6 | 0 | 0.32 | |

4 | 0.0815 | 3 | 0.245 | 2.4 | 6 | 0.47 | |

5 | 0.0183 | 10 | 0.183 | 9.4 | 88 | 1.62 | |

6 | 0.0024 | 56 | 0.133 | 55.4 | 3070 | 7.27 | |

7 | 0.0002 | 180 | 0.029 | 179.4 | 32187 | 5.16 | |

8 | 0.0000 | 1,000 | 0.004 | 999.4 | 998814 | 4.34 | |

Total | 1.000 | 0.5933 | 19.178 | ||||

So now we just need to carve out the cases that advance to the bonus round and “win” it. That table is:

Table 2 | |||||||
---|---|---|---|---|---|---|---|

Payout | |||||||

Catch | Probability | Payout | Return | less .9015 | Squared | Extended | |

<4 | 0.8977 | - | 0.000 | -0.9 | 1 | 0.73 | |

4 | NoB | 0.0786 | 3 | 0.236 | 2.1 | 4 | 0.35 |

5 | NoB | 0.0175 | 10 | 0.175 | 9.1 | 83 | 1.45 |

6 | NoB | 0.0022 | 56 | 0.125 | 55.1 | 3036 | 6.80 |

7 | NoB | 0.0002 | 180 | 0.027 | 179.1 | 32076 | 4.82 |

8 | NoB | 0.0000 | 1,000 | 0.004 | 999.1 | 998198 | 4.02 |

4 | WinB | 0.0029 | 82 | 0.241 | 81.0 | 6559 | 19.30 |

5 | WinB | 0.0008 | 89 | 0.073 | 88.0 | 7742 | 6.39 |

6 | WinB | 0.0001 | 135 | 0.017 | 134.0 | 17952 | 2.30 |

7 | WinB | 0.0000 | 259 | 0.003 | 258.0 | 66557 | 0.67 |

8 | WinB | 0.0000 | 1,079 | 0.000 | 1078.0 | 1162056 | 0.36 |

1.000 | 0.9015 | 47.196 | |||||

The variance is 47.196, shown in the bottom right, agrees to the Wizard's number.

As an example, the probability value for (catch 5, no bonus) is the previous probability of 0.0183 (irrespective of bonus) less the probability value for (catch 5, win bonus) of 0.0008 = 0.0175. The probability value for (catch 5, win bonus) is 0.0183 * 5/20 (which is the probability that the last Keno ball drawn contributed to a win) * p = 0.0008. The payout without a bonus was 10, so in this case it’s 10 + f = 88.9. The other lines are calculated the same way.

My previous error was that I was using an average value for the probability of advancing to the bonus round. But that simplification can’t be done at a transitional state like this one, since part of the total variance is due to varying probabilities of advancing to the bonus round (5 rates, 1 for each win level). This game can’t be fully simplified until the bonus round since no future states depend on it.

PS Thanks for your replies, Beachbumbabs and DogHand. I found a very simple method to sample 20 from 80 without replacement that uses the same concept as DogHands (I think). It’s 100 cells per sample using 1 RAND formula in the first 80 and 1 RANK formula in the next 20. Not perfectly random (about a 1 in 10^15 chance of a repeat) but supposedly there are no truly perfect algorithms for random generation.