Thread Rating:

Wizard
Administrator
Wizard
  • Threads: 1514
  • Posts: 26974
Joined: Oct 14, 2009
Thanked by
tringlomanekgb92mipletdjtehch34t
March 2nd, 2019 at 7:04:10 AM permalink
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.
Last edited by: Wizard on Mar 2, 2019
"For with much wisdom comes much sorrow." -- Ecclesiastes 1:18 (NIV)
Wizard
Administrator
Wizard
  • Threads: 1514
  • Posts: 26974
Joined: Oct 14, 2009
March 2nd, 2019 at 10:13:29 AM permalink
As usual, a post that took me hours to write gets no replies.
"For with much wisdom comes much sorrow." -- Ecclesiastes 1:18 (NIV)
ck1313
ck1313
  • Threads: 1
  • Posts: 44
Joined: Aug 27, 2011
Thanked by
Foragertringlomane
March 2nd, 2019 at 10:21:53 AM permalink
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.
ChumpChange
ChumpChange
  • Threads: 131
  • Posts: 5112
Joined: Jun 15, 2018
March 2nd, 2019 at 10:30:34 AM permalink
I saw a Cleopatra Keno yesterday, with a Blackjack game available. Blackjack pays 2 for 1, same as any other win, so no.
ChumpChange
ChumpChange
  • Threads: 131
  • Posts: 5112
Joined: Jun 15, 2018
March 2nd, 2019 at 10:36:51 AM permalink
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.
tringlomane
tringlomane
  • Threads: 8
  • Posts: 6284
Joined: Aug 25, 2012
March 2nd, 2019 at 10:38:46 AM permalink
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.
Wizard
Administrator
Wizard
  • Threads: 1514
  • Posts: 26974
Joined: Oct 14, 2009
March 2nd, 2019 at 11:55:23 AM permalink
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.
"For with much wisdom comes much sorrow." -- Ecclesiastes 1:18 (NIV)
Gialmere
Gialmere
  • Threads: 45
  • Posts: 3042
Joined: Nov 26, 2018
Thanked by
tringlomane
March 2nd, 2019 at 12:29:04 PM permalink
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.
Have you tried 22 tonight? I said 22.
djatc
djatc
  • Threads: 83
  • Posts: 4477
Joined: Jan 15, 2013
Thanked by
tringlomane
March 2nd, 2019 at 9:58:43 PM permalink
I find Cleopatra Keno to be the funnest keno game in a casino

4 card Cleo is even more funner
"Man Babes" #AxelFabulous
miplet
miplet
  • Threads: 5
  • Posts: 2139
Joined: Dec 1, 2009
Thanked by
tringlomane
March 3rd, 2019 at 12:15:26 AM permalink
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.
“Man Babes” #AxelFabulous
tringlomane
tringlomane
  • Threads: 8
  • Posts: 6284
Joined: Aug 25, 2012
March 3rd, 2019 at 1:46:28 AM permalink
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.
Romes
Romes
  • Threads: 29
  • Posts: 5608
Joined: Jul 22, 2014
March 3rd, 2019 at 11:04:43 AM permalink
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!
Playing it correctly means you've already won.
Wizard
Administrator
Wizard
  • Threads: 1514
  • Posts: 26974
Joined: Oct 14, 2009
March 7th, 2019 at 4:20:11 PM permalink
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.
"For with much wisdom comes much sorrow." -- Ecclesiastes 1:18 (NIV)
Ace2
Ace2
  • Threads: 32
  • Posts: 2706
Joined: Oct 2, 2017
March 8th, 2019 at 5:58:52 PM permalink
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.
It’s all about making that GTA
beachbumbabs
beachbumbabs
  • Threads: 101
  • Posts: 14268
Joined: May 21, 2013
March 8th, 2019 at 6:08:24 PM permalink
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
If the House lost every hand, they wouldn't deal the game.
DogHand
DogHand
  • Threads: 2
  • Posts: 1790
Joined: Sep 24, 2011
Thanked by
beachbumbabs
March 9th, 2019 at 2:40:43 PM permalink
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
145402851804296163756026421344794864669
256237253563655417276516663456911603175
3455034562127226379152665766724534338449
462265210732672127035542067432414532227
579513155546852418762514722210152853


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
Ace2
Ace2
  • Threads: 32
  • Posts: 2706
Joined: Oct 2, 2017
March 18th, 2019 at 2:17:04 AM permalink
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
CatchProbabilityPayoutReturnless .5933SquaredExtended
<40.8977 - 0-0.600.32
40.0815 3 0.2452.460.47
50.0183 10 0.1839.4881.62
60.0024 56 0.13355.430707.27
70.0002 180 0.029179.4321875.16
80.0000 1,000 0.004999.49988144.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
CatchProbabilityPayoutReturnless .9015SquaredExtended
<40.8977 - 0.000-0.910.73
4NoB0.0786 3 0.2362.140.35
5NoB0.0175 10 0.1759.1831.45
6NoB0.0022 56 0.12555.130366.80
7NoB0.0002 180 0.027179.1320764.82
8NoB0.0000 1,000 0.004999.19981984.02
4WinB0.0029 82 0.24181.0655919.30
5WinB0.0008 89 0.07388.077426.39
6WinB0.0001 135 0.017134.0179522.30
7WinB0.0000 259 0.003258.0665570.67
8WinB0.0000 1,079 0.0001078.011620560.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.
Last edited by: Ace2 on Mar 18, 2019
It’s all about making that GTA
Ace2
Ace2
  • Threads: 32
  • Posts: 2706
Joined: Oct 2, 2017
March 19th, 2019 at 3:18:37 PM permalink
Quote: Wizard

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

Yes I know the feeling !
It’s all about making that GTA
  • Jump to: