front page
blackjack
poker
secrets
archive
video
gm store
resources
advertising
contact us

Back to The Blackjack Page

The Blackjack Page Archive

Excel for the Blackjack Player - Part 1

I finally can use the number-crunching abilities of my computer because I learned how to use that amazing program, MS Excel, at least as it applies to advantage-play situations like we talk about here. As you can see, this is part 1 so you can expect more articles in the future about how Excel can help BJ players. You'll also see it used in lessons on Poker, Video Poker and other casino games, all of which have a mathematical aspect to them that most people don't care to learn. I like the math involved in these games - it's what I do, plus I firmly believe, if it's worth doing, it's worth over-doing, so I'll probably overdo it on Excel.

But the good thing is that you don't have to learn the math, which is probably why you're here in the first place; all you have to do is learn how to program Excel and let it do the math for you. I'll show you how to program it for various functions and it'll be sort of like: "Give a man a fish and you've given him a meal. Teach a man how to fish and you've given him a meal for the rest of his life." Ready? Here we go.

Probably the most frequent question I receive from readers on the topic of Blackjack is about what we call "risk of ruin" (ROR), which is a fancy way of describing how likely you are to go broke. Calculating ROR for most casino games is a piece of cake. Take Roulette, for instance. If it's a double-zero wheel, the casino's edge is 5.26% on every "even-money" (red/black, high/low, odd/even) bet you make. If you have a bankroll of, say, 20 units - $20 if you're playing a $1 minimum bet game - your probability of losing everything before you double it by betting just $1 per spin on the even-money bets is calculated by the classic "gambler's ruin" equation, Ruin = 1/1+S^20. The 20 is the number of betting units and S is the "success ratio", which is calculated by p/q, where p is the probability of winning (18/36) divided by q, which is the probability of losing (20/36). This breaks down to 18 divided by 20 = 0.9. So, if you are risking $20 by betting $1 per spin on the even-money spots of a Roulette game, your probabilty of losing it all is 1 divided by 1 + .9 ^20. The "^" means to the power of, like squared, cubed and so forth. In Excel, "-" means subtract, "+" means add, "*" means multiply and "/" means divide. To take .9 to the 20th power is easy, because Excel does it in the blink of an eye, literally.

To make a calculation, just open a blank Excel worksheet and select the "cell" (like B3 or D2) where you want the answer to appear. When you select the cell, a dark box will appear around it. Then, type the equal sign (=), which tells Excel you're going to give it a formula. To multiply .9 by .9 twenty times, enter =.9^20 and you'll get 0.121577. Add that to 1 and it's 1.121577. Divide that into 1 and you get 0.888281; call it 89%. So, if you're willing to risk $20 at Roulette and you bet $1 per spin on the even-money spots (which you can see aren't so "even"), the probability that you'll lose all of your $$$ before you make a $20 profit is 89%. Such is the power of a 5.26% house edge. Actually, if you were to treat your $20 as one bet - put it all on Red, say, your probability of losing is 1/1+.9^1 = 5.26%, which is the casino edge on any one particular bet. By dividing your bankroll into a lot of small bets, you give the house edge a bigger opportunity to grind you down. Here's how the formula for 20 units looks in Excel: =1/(1+(.9^20)). If you have a friend who plays Roulette instead of Blackjack, show him or her this calculation and just substitute their number of betting units for the 20 in my formula. If it's 100 for example, the formula is =1/(1+).9^100)), which equals 0.999973, virtually a 100% probability of losing 100 bets before doubling it!

Unfortunately, caculating the risk of ruin in Blackjack isn't as easy because you often bet more than 1 unit, sometimes win more than 1 unit, split pairs, double down and so forth. But we can work with some averages and come up with fairly accurate ROR numbers. I'll talk about those averages in a minute, but let me show you the basic ROR formula for Blackjack: = ((1-w/sd)/(1+w/sd))^(BR/sd), where w is the average win per hand expressed in units, sd is the average Standard Deviation of 1 hand and BR is the bankroll, expressed in number of units. Let me warn you right now that the parens "( )" have to be placed in Excel exactly as shown or you'll get the wrong answer, if not an error message. Now you're undoubtedly wondering what numbers to put in place for "w" and "sd". Well, that's the hard part because those numbers depend upon the betting schedule you're using, the type of game, its penetration and a myriad of other factors. We can come pretty close by running simulations on a program like Statistical Blackjack Analyzer, which is what I use.

Let me walk you through a ROR calculation for a Double-deck game where the dealer hits soft 17, you may double on any first two cards including after splitting pairs, insurance is available but not surrender. Let's also assume 75% penetration and use a 1-8 bet spread with 1 unit bet at a True Count (TC) of 1 or lower; 2 units at TC 2; 4 units at TC 3; 6 units at TC 4 and 8 units at TC 5 or higher. My simulation of this game, using Basic Strategy variations - BSV - shows an overall player advantage of 1.47%, an average bet of 1.95 units and a Standard Deviation of 3.26 units per hand played. From that, we can calculate w, which is 1.47% times 1.95 and that is 0.0287. The sd is figured for us; it's 3.26. So, if we plug those numbers into the basic formula, it should look like this in Excel: =((1-0.0287/3.26(/(1+0.0287/3.26))^(600/3.26), if we assume a 600 unit bankroll. The ROR shown is 0.0391 or 3.9%. If we assume a 200 unit bankroll, just put that in the formula in place of the 600 and you should get an answer of 0.3233 or 32.3%. Since I've always recommended a minimum bankroll of 50 top bets, which is 8 units in this example, the total bankroll would be 400 units and the ROR would be 11.4%, which is a number I feel is sufficient for those who can replace a lost bankroll by outside $$$, namely a job. A professional player who relies upon his or her winnings to pay the usual monthly bills like rent and food should operate with 800 units, which gives a ROR of 1.3%.

You can see how simple this is if you know the values of w and sd. But most of you have no idea, so I'll try to provide some of those values from the 1000s of simulations I've run. Below, you'll find a list of w and sd for common games; if the game you're playing doesn't fit exactly, it'll probably be close if you do a little "juggling" of the numbers. If that doesn't work, email me and I'll try to help.

  • My "School" game: 6 decks, dealer stands on soft 17 (S17), double on any first 2 cards (DA2), double after split (DAS), do not leave the table when the count drops (play all). Penetration is 5 decks of 6 (83.33%) and Basic Strategy variations (BSV) are used. The bet ramp (in units) is 1 at TC 1 or lower; 2 at TC 2; 4 at TC 3; 8 at TC 4; 10 at TC 5 and 12 at TC 6 or higher. The value for w is 0.019 and the value for sd is 3.42.
  • Same as # 1, but leave table when the count drops to TC -1 or lower: The value of w is 0.034 and the value for sd is 4.02.
  • An "okay" double-deck game: 2 decks, dealer hits soft 17, DA2, DAS, play all, BSV. Penetration is 50% and the bet ramp is 1 unit at TC 1 or lower; 2 at TC 2; 4 at TC 3; 6 at TC 4 and 8 at TC 5 or higher. The value of w is 0.0087 and the value for sd is 2.52.
  • Same as # 3, but penetration is 62 cards or 60%. Play all, BSV. The value of w is 0.016 and the value for sd is 2.85.
  • Same as # 4, but the penetration is 78 cards or 75%. Play all, BSV. The value of w is 0.029 and the value of sd is 3.26.
  • Today's most common six-deck game: 6 decks, dealer hits soft 17, DA2, DAS, no surrender, play all, BSV. Penetration is 75% and player leaves the table when the TC drops to -2 or lower. A 1-16 bet spread is used; 1 unit at TC 1 or lower; 2 at TC 2; 8 at TC 3; 10 at TC 4; 14 at TC 6; 16 at TC 7 or higher. The value of w is 0.02 and the value of sd is 4.11.
  • Same as # 6, but with a more aggressive 1-1r6 bet spread; the player still leaves when the count drops to -2 or lower. One unit is bet at TC 1 or lower; 2 at TC 2; 6 at TC 3; 12 at TC 4 and 16 at TC 5 or higher. The value for w is 0.027 and the value for sd is 4.42.
  • A typical Reno single-deck game: 1 deck, dealer hits soft 17, double only on 9, 10 and 11; no DAS. Penetration is 50%, play all, BSV. A 1-4 bet spread is used; 1 unit at TC 1 or lower, 2 at TC 2; 3 at TC 3 and 4 at TC 4 or higher. The value of w is 0.012 and the value of sd is 1.95.

Okay, break out that Excel program and I'll see you here next time.

  

2007 Articles