Variance of Cleopatra Keno

Somebody asked me about the variance of Cleopatra Keno. I had to dust off my college statistics books to help me with that one. As a reminder, it plays like conventional spot keno, except if the last ball drawn contributes to a win, the player gets 12 free games with a 2x multiplier. Free games do not earn more free games. Let's use the 3-10-56-180-1000 pick-8 pay table as an example.

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.

As usual, a post that took me hours to write gets no replies.

Wiz this is probably great information but what I take away from it is I should randomly pick 8 numbers hit the start button and watch my credits slowly disappear.

I saw a Cleopatra Keno yesterday, with a Blackjack game available. Blackjack pays 2 for 1, same as any other win, so no.

I used to have a Keno strategy when picking 2 numbers and winning would pay 15 to 1. I'd better stick to 17 to 1 payouts on Roulette instead.
Lots of people playing 8 to 10 spot penny or nickel Keno to win the big prizes though.

Quote: Wizard

As 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.

Thanks guys for the replies, although I'm sure they were sympathetic. To be honest, it was good for me to review those formulas and I'll get mileage from it as a future "ask the wizard" question.

Quote: Wizard

As 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.

I find Cleopatra Keno to be the funnest keno game in a casino

4 card Cleo is even more funner

Quote: tringlomane

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.

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

Quote: miplet

I'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.

I 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!

Quote: Romes

I 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 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.

Quote: Ace2

I 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: Ace2

I 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

This problem reminded me that I need to learn basic programming and covariance. I understand the concept of covariance but have never studied or used covariance formulas.

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.

Quote: Wizard

As usual, a post that took me hours to write gets no replies.

Yes I know the feeling !