is there any excel Combinational analysis for blackjack?

is there any excel Combinational analysis for blackjack?

the reason for me to ask this is that, after checking Eliot't excel for bac, i now can do all calcluations of almost all side bets besides banker, player, tie. and by excel, when click once, all data changes, it is self satisfactory, what is more, it seems that the excel way can be applied to many other games , like 'niuniu'='cattle cattle
'，the popular chinese game, i never kneww i can finish this before.

now comes the asking-for-help. for blackjack, if there is any ready one i can read, then i know it is finishable and i found a good site:http://www.bjstrat.net/cgi-bin/cdca.cgi is this done by excel or some codes?

Many of us use excel for just the type of analysis you mention and develop spreadsheets that analyze blackjack and casino games of all kinds. However, I do not know whether the bjstrat hand calculator that you cite uses a spreadsheet model or a different programming language.

The most important excel function for combinational math is combin( number, number chosen) which returns the number of combinations for a given number of items. The formula in the function is:

combin( n, k ) = n! / ( k! * (n-k)! )

I would encourage you to examine the list of mathematical functions in your excel spreadsheet software.

For a bunch of Blackjack side bets, you can see some examples that I have done at http://miplet.net/blackjack/ (that reminds me I need to fix the super 4 progressive)

You can also do an infinite deck analysis easily in excel. Wizard has a youtube video at https://youtu.be/jCF-Btu5ZCk .

Quote: miplet

You can also do an infinite deck analysis easily in excel. Wizard has a youtube video at https://youtu.be/jCF-Btu5ZCk .

I have modified Wizards spreadsheet to do actual shoe derivation of house edge and optimum strategy ( ongoing through the shoe). If you want to see it, PM me. It's a bit of a lash up and modified for European NHC rules.

Quote: gordonm888

Many of us use excel for just the type of analysis you mention and develop spreadsheets that analyze blackjack and casino games of all kinds. However, I do not know whether the bjstrat hand calculator that you cite uses a spreadsheet model or a different programming language.

The most important excel function for combinational math is combin( number, number chosen) which returns the number of combinations for a given number of items. The formula in the function is:

combin( n, k ) = n! / ( k! * (n-k)! )

I would encourage you to examine the list of mathematical functions in your excel spreadsheet software.

maybe it is till my poor english, which i learned no-abroad.
here is a picture to show how bac is done.
i don't know how to attach pictures.
but the excel file can do almost all this in some seconds.https://wizardofodds.com/games/baccarat/appendix/5/#toc-DragonBonus

Quote: miplet

For a bunch of Blackjack side bets, you can see some examples that I have done at http://miplet.net/blackjack/ (that reminds me I need to fix the super 4 progressive)

You can also do an infinite deck analysis easily in excel. Wizard has a youtube video at https://youtu.be/jCF-Btu5ZCk .

thanks, i will go through it later, my vpn is not working well.
by the way, do u know there is a way to do billions of simulations of your side bet without really doing it? maybe there is a formula for true count going up and down?

addup: i cannot open this link: http://miplet.net/blackjack/
also maybe you can figure it out the main bet of blackjack by excel:to cover all possiblites by lines and columns? i read u, 95% you can do it.

I am not aware of any functions or canned 'macros" in Excel that automatically calculate Blackjack game characteristics for you. There are commercial software packages (for sale on the internet) that can calculate many things about blackjack.

Part of our difficulty in discussing this has to do with what you actually want to calculate.

1. Strategy? Hit or stand on hard 12 vs 4? What to do with a pair of 2s vs 2? EV for player options on a specific hand? (There are free hand calculators on the internet for that, such as BJStrat and the WizardofOdds site -WOO - calculators)

2. How does return/house edge/player EV change with rules? (again the WOO calculator and BJSTRAT handle that well.)

3. Blackjack sidebet returns?

4. Card counting information? Shoe penetration?

Many of us program our own models into Excel to calculate what we are interested in. Because new game variations and sidebets arise all the time, many of us are constantly revising our programs and adding to our "libraries" of analysis tools. Some of us swap ideas and analyses here on this forum, and I am always learning from the other experts on this forum.

Miplet has some spreadsheet models that he posts and offers access to via this forum. MustangSally also posts some non-spreadsheet models for others to use. I occasionally develop Excel spreadsheet models and email them to forum members to help them. And there are many other brilliant programmers here who can be helpful in various ways. So, just keep interacting and tell us more specifically about what you are interested in.

Quote: gordonm888

MustangSally also posts some non-spreadsheet models for others to use.

The OP shows a link to non-Excel CA (BJSTRAT) that is fast in my Win10 laptop except when wanting splits up to 4 hands, then it takes well over an hour for only one computation.

Excel is way too slow in comparison as it is not compiled as C or C++ can be.

Ion Saliu, iirc, did a CA (all permutations, no suits) on single deck BJ and the text files are massive as expected.

imo, the project asked for can NOT be done successfully in Excel.
The one for Baccarat the OP mentions is over 300k rows in Excel (combinations and permutations) and older Excel can not open that.
The number of 6-card permutations only for Baccarat is exactly 1 million rows and that slows down even for newer Excels using over 5 columns.

I would say, one would do way better by staying away from Excel for what the OP requests.

btw, imo, if it was possible, I say it would have already been done
I mean Dr. Thorp started massive computer work
and knows about combinations and permutations
and if it was possible, someone could have done that by now

Sally

Quote: mustangsally

...in Excel.
The one for Baccarat the OP mentions is over 300k rows in Excel (combinations and permutations) and older Excel can not open that.
The number of 6-card permutations only for Baccarat is exactly 1 million rows and that slows down even for newer Excels using over 5 columns.

With a bit of smarts (for instance P:10 9 B:10 10 is the same as P:9 10 B:10 10 but add a column to say can occur 2x reduces it quite a bit (3025 rather than 10000). Then some of these won't take more cards (or only one). So this gets it down to about 103k more lines (in my excel this has to be split across two sheets).

I've never tried (except for the push 22 sidebet) to look at all 54k combinations of the dealer's hand on s17 (which I created using a program and then worked out what I needed to know for each one).

I'm guessing you need to keep a database of combinations you've already analysed (e.g. 6 3 2 A is the same as 6 2 3 A, A 2 3 6, A 3 6 2 (yes I know you wouldn't hit soft 20 but the computer has to work that out!).

Quote: gordonm888

I am not aware of any functions or canned 'macros" in Excel that automatically calculate Blackjack game characteristics for you. There are commercial software packages (for sale on the internet) that can calculate many things about blackjack.

Part of our difficulty in discussing this has to do with what you actually want to calculate.

1. Strategy? Hit or stand on hard 12 vs 4? What to do with a pair of 2s vs 2? EV for player options on a specific hand? (There are free hand calculators on the internet for that, such as BJStrat and the WizardofOdds site -WOO - calculators)

2. How does return/house edge/player EV change with rules? (again the WOO calculator and BJSTRAT handle that well.)

3. Blackjack sidebet returns?

4. Card counting information? Shoe penetration?

Many of us program our own models into Excel to calculate what we are interested in. Because new game variations and sidebets arise all the time, many of us are constantly revising our programs and adding to our "libraries" of analysis tools. Some of us swap ideas and analyses here on this forum, and I am always learning from the other experts on this forum.

Miplet has some spreadsheet models that he posts and offers access to via this forum. MustangSally also posts some non-spreadsheet models for others to use. I occasionally develop Excel spreadsheet models and email them to forum members to help them. And there are many other brilliant programmers here who can be helpful in various ways. So, just keep interacting and tell us more specifically about what you are interested in.

thanks .what i want is to show all kinds of datas like this link: https://wizardofodds.com/games/blackjack/appendix/1/
reading is sth, but to do it on my own is another thing, which means different understanding.
what is more, macau rule is ENHC, but dealer's blackjack, we only lose original bet. for this rule, few are putting good datas on it.

thanks again for other info.

adding up, there are some hands:
like A2V5, A4V4, if we consider the cards dealt, the strategy is different. in realty, the cards are really dealt out. it is a topic including composition (in)dependent. the strategy maker and tester is not stating it. so without doing all things by myself, i shall not discover it.

Quote: mustangsally

The OP shows a link to non-Excel CA (BJSTRAT) that is fast in my Win10 laptop except when wanting splits up to 4 hands, then it takes well over an hour for only one computation.

Excel is way too slow in comparison as it is not compiled as C or C++ can be.

Ion Saliu, iirc, did a CA (all permutations, no suits) on single deck BJ and the text files are massive as expected.

imo, the project asked for can NOT be done successfully in Excel.
The one for Baccarat the OP mentions is over 300k rows in Excel (combinations and permutations) and older Excel can not open that.
The number of 6-card permutations only for Baccarat is exactly 1 million rows and that slows down even for newer Excels using over 5 columns.

I would say, one would do way better by staying away from Excel for what the OP requests.

btw, imo, if it was possible, I say it would have already been done
I mean Dr. Thorp started massive computer work
and knows about combinations and permutations
and if it was possible, someone could have done that by now

Sally

i tried to learn C C++ many times, but failed at the beginning. so even i have code from others, i don't know how to use it.
for bac, i used 146971 lines to finish all combinations:4998398275503360 more than 20 columes, to caculate all side bets. i finished this task after many give-ups. but thinking there was a hongkong guy who can do it and i saw the file and lost it. so i kept redoing it after giveups.

for blackjack, maybe it will be easier to give up because nobody have done it before.

THANK GOODNESS，

i finished blackjack dealer's hands

https://wizardofodds.com/games/blackjack/appendix/2a/
Blackjack Appendix 2: Dealer Probabilities

all is by excel, and the way is like bac. to show put all combinations in it.
it must be 12 cards,
because dealer can be 12 cards to make a hand.
312 311 310 309 308 307 306 305 304 303 302 301 686,807,714,636,276,000,000,000,000,000
1 1 1 1 1 1 6 1 1 1 1 1
1 1 1 1 1 1 6 1 1 1 1 2
1 1 1 1 1 1 6 1 1 1 1 3
1 1 1 1 1 1 6 1 1 1 1 4
1 1 1 1 1 1 6 1 1 1 1 5
1 1 1 1 1 1 6 1 1 1 1 6
1 1 1 1 1 1 6 1 1 1 1 7
1 1 1 1 1 1 6 1 1 1 1 8
1 1 1 1 1 1 6 1 1 1 1 9
1 1 1 1 1 1 6 1 1 1 1 10
there are some tricks to do all at 12 cards.
like A 10 ************ 24*96*310* 309 *308 *307 *306*305*304 *303 *302 *301
(the bad thing about excel which killed my 2 days is that excel cannot calculate huge numbers well.)
i will try to finish the left things to check how computer can play the game.

now my thread is to delare rather than to ask.

Quote: tomchina123

because dealer can be 12 cards to make a hand.
686,807,714,636,276,000,000,000,000,000

(the bad thing about excel which killed my 2 days is that excel cannot calculate huge numbers well.)

excel can calculate very large numbers, it only displays accuracy to 15 places
P(312,12)
686,807,714,636,276,391,560,347,545,600
http://www.wolframalpha.com/input/?dataset=&equal=Submit&i=P(312,12)

or in pari/gp (free program)
(09:09) gp > 312!/(312-12)!
%3 = 686807714636276391560347545600

GP is great when you really want the exact values and very easy to add them up
to keep them exact.

There are precision programs and add-ins to allow Excel to do just that too.
I tested it (xlPrecision) years ago and was impressed (ok, easy, I know)
I am sure a Google search can find more

good luck

Sally

Quote: charliepatrick

With a bit of smarts (for instance P:10 9 B:10 10 is the same as P:9 10 B:10 10 but add a column to say can occur 2x reduces it quite a bit (3025 rather than 10000). Then some of these won't take more cards (or only one). So this gets it down to about 103k more lines (in my excel this has to be split across two sheets).

I've never tried (except for the push 22 sidebet) to look at all 54k combinations of the dealer's hand on s17 (which I created using a program and then worked out what I needed to know for each one).

I'm guessing you need to keep a database of combinations you've already analysed (e.g. 6 3 2 A is the same as 6 2 3 A, A 2 3 6, A 3 6 2 (yes I know you wouldn't hit soft 20 but the computer has to work that out!).

Charlie:

I have developed Excel algorithms to calculate the probabilities of Dealer 17-21 as a function of the shoe composition and the dealers upcard. These algorithms account for dealer hands that have up to 6 cards drawn to the upcard and in some cases that have 7 cards drawn to the the upcard. While this does not cover every possible multi-card hand (especially when hitting a dealer 2) it does give perfect agreement up to at least 4 digits for the dealer probabilities for 17-21, as compared to BJstrat calculator. I do indeed take advantage of "permutation/sequence folding" of the type that you mention, and I do it massively (and carefully) for hands with large numbers of cards.

On a worksheet that is dedicated to "vs. 10" or "vs. 2" (or whatever) I then list all the possible player hands in a column. A "player hand" is defined as any string of cards whose sum exceeds hard 16 without ever having made a soft 18 or higher (or soft 17 higher for D17 rules) or a sum exceeding soft 18/19 or higher. For each string of player cards, the remaining shoe composition is then redefined in the row to account for the cards in players hand and the algorithms for dealer outcome probabilities are then used to calculate the EV for that string of cards. Other than ignoring dealer hands of 8 cards or more and player hands of 9 cards or more, the only approximation I use is for splitting and resplitting pairs (other than Aces) -in which case I use a recurrence-type approximation.

There are a lot of tricky parts to this -one is player strategy (and optimization) as a function of the dealer up card and shoe composition. Another is the calculation of the split pair probabilities. And obviously, changing the game rules is a very significant undertaking requiring developing a customized set of spreadsheets.

For operational convenience, I usually configure a separate spreadsheet for each dealer upcard because having the entire set of spreadsheets open is hard on my computer. The vs. 2 and vs. A spreadsheets are particularly large.

This gives me a compositionally-dependent calculator capability in Excel for Blackjack House Edge that I have then adapted to handle blackjack variants -such as games with Push 22, Push on 16 and other weird rules variants. Again, the caveats are:
- hands of 8 or more cards are usually not included in dealer probabilities or 9 or more cards in set of possible player hands
- calculated values of dealers probabilities are not verified for more than 4 digits of accuracy (that is as much as BJstrat calculator provides)
- recurrence approximation is used for calculating the EV of the "n+1 hands" when splitting and re-splitting pairs (except split Aces which are treated rigorously.)
- I also do not routinely include the capability to evaluate player hitting H17 and higher and S19 as higher. That would significantly expand the size of the spreadsheets.

Quote: mustangsally

excel can calculate very large numbers, it only displays accuracy to 15 places
P(312,12)
686,807,714,636,276,391,560,347,545,600
http://www.wolframalpha.com/input/?dataset=&equal=Submit&i=P(312,12)

or in pari/gp (free program)
(09:09) gp > 312!/(312-12)!
%3 = 686807714636276391560347545600

GP is great when you really want the exact values and very easy to add them up
to keep them exact.

There are precision programs and add-ins to allow Excel to do just that too.
I tested it (xlPrecision) years ago and was impressed (ok, easy, I know)
I am sure a Google search can find more

good luck

Sally

Thanks, Sally, i like this name.

here i show how excel cannot do big number. like
a=999,999,999,999,999,000
b=999,999,999,999,989,000
a-b=9,984
it is wrong answer.
for your info, i am sure it will be helpful.

Quote: mustangsally

excel can calculate very large numbers, it only displays accuracy to 15 places
P(312,12)
686,807,714,636,276,391,560,347,545,600
http://www.wolframalpha.com/input/?dataset=&equal=Submit&i=P(312,12)

or in pari/gp (free program)
(09:09) gp > 312!/(312-12)!
%3 = 686807714636276391560347545600

GP is great when you really want the exact values and very easy to add them up
to keep them exact.

There are precision programs and add-ins to allow Excel to do just that too.
I tested it (xlPrecision) years ago and was impressed (ok, easy, I know)
I am sure a Google search can find more

good luck

Sally

Thanks, Sally, i like this name.

here i show how excel cannot do big number. like
a=999,999,999,999,999,000
b=999,999,999,999,989,000
a-b=9,984
it is wrong answer.
for your info, i am sure it will be helpful.

gordonm888, thanks.

you reminds me of a friend in Romania, his name is Alex. who owns a company of renting rental equipments of moving stage, like PRG. he wrote emails of your style, very long and logical. have you been a teacher before? i am curious.

Quote: tomchina123

gordonm888, thanks.

you reminds me of a friend in Romania, his name is Alex. who owns a company of renting rental equipments of moving stage, like PRG. he wrote emails of your style, very long and logical. have you been a teacher before? i am curious.

Thanks for the kind words. I taught physics in college when I was a graduate student, and I have always had a gift for explaining complex subjects, which has helped me greatly in my career as a scientist.

Quote: gordonm888

Thanks for the kind words. I taught physics in college when I was a graduate student, and I have always had a gift for explaining complex subjects, which has helped me greatly in my career as a scientist.

you are welcome. and i tough english in college when i graduated. and i have alway had an interest for learning complex physics subjects.which helps me greatly in my study of gambling probabilities. , high probably, my scientist-minded way made me a failure when my career was factory-owner, i went too deep on electromagnetic things of the LED screen when i shoud have cared for production more.

HI， gordonm888&SALLY，

i have a question, check this linK:

https://wizardofodds.com/games/blackjack/appendix/1/

if you have a hand of score 6, dealer is 8. the ev of standing and hit once should be the rougly the same, right? but why is the EV data in this link so different?
if not hitting once, then how many times to hit to finish all the logic? why is the decision of every point not independent?

to go further, the score 6 includes 33 or A5 or not?

the 1st question is the more important.

Quote: tomchina123

HI， gordonm888&SALLY，

i have a question, check this linK:

https://wizardofodds.com/games/blackjack/appendix/1/

if you have a hand of score 6, dealer is 8. the ev of standing and hit once should be the rougly the same, right? but why is the EV data in this link so different?
if not hitting once, then how many times to hit to finish all the logic? why is the decision of every point not independent?

to go further, the score 6 includes 33 or A5 or not?

the 1st question is the more important.

The hit table isn’t for hit once, it’s for hitting and then based on the result of the first hit, following optimal play for an infinite deck, which might include further hitting.

6 does include 33 but not A5. A5 is under the soft 16 of that table.

Quote: unJon

The hit table isn’t for hit once, it’s for hitting and then based on the result of the first hit, following optimal play for an infinite deck, which might include further hitting.

6 does include 33 but not A5. A5 is under the soft 16 of that table.

thanks for your answer, unjon, here is my further doubts:

if firtist hit+optimal play,
1) what is optimal play?
2) if not hit once, is it fair to standing and doubing and splitting? because to compare, we need to have the same standardard? to say it another way, if there is a strategy after hit, how to compre this strategy?
3) for score of 12-20 of player, my result of hitting is almost the same as that link, only 0.2% difference, it is to hit once ( the small difference can be the big number prolem of excel. or the unfinished softs or pairs)., but score of 4-11, 95% are totally different.

for another answer, there is alsotable of pairs.

anyway, thanks for your answer.

after writing this, maybe this optimal play means the EV can be better or the same after hitting, because we can have another chance to stand or to hit after hitting. i will try to figure it out how to calculate them.

Your last two sentences are correct.

You might enjoy tinkering with this link as well:

https://wizardofodds.com/games/blackjack/hand-calculator/

Quote: tomchina123

HI， gordonm888&SALLY，

i have a question, check this linK:

https://wizardofodds.com/games/blackjack/appendix/1/

if you have a hand of score 6, dealer is 8. the ev of standing and hit once should be the rougly the same, right? but why is the EV data in this link so different?
if not hitting once, then how many times to hit to finish all the logic? why is the decision of every point not independent?

to go further, the score 6 includes 33 or A5 or not?

the 1st question is the more important.

On the WOO blackjack appendix you refer to, the term "hit" means that you hit the indicated hand and then make subsequent decisions according to basic strategy. For the case of 2,4(or 6) vs 8, for example, when the next card is a 3 that gives the player a 9 vs 8 and it is understood that he will hit again until hard 17 or higher is reached.

Also, by convention, the player hand 6 refers only to an unpaired hard 6. Thus, a 3-3 or A-5 are not meant to be included. Similarly, when people talk about what to with 16 vs 10, they are referring to 8-8 vs 10.

thanks. but if it is to make basic strategy, why is there a basic strategy?
i tried many time, but the result is still not close. thoug the standing data is the same.
we set an example.
player dealer
5 6
if we hit, it can be:
player dealer
soft 16 6
7,
8
9
10
11
12
13
14
15
then no matter optimal or basic, we need to hit again, because the first hit gives the result the same as standing.
we hit once, then it can be:
player dealer
soft 17 6
8
9
10
11
12
13
14
15
16
we need to hit once more:
soft 17 6
9
10
11
12
13
14
15
16
17

wow, again, after writing this, i find my mistake, the 2nd hit needs to add 1-10 for every score.i will keep moving with it.

the updates: by doing this, it is another time to do brutal filling of excel lines. it needs to go to stand on 12,13...hard 17, soft from 18 to 19th, nothing creative and smart.

and i finish most of it now, the result is going right with above talked links of Wizard, only some decimals difference. i will check how it can perform for online games.

though it is not no new findings here, but i am happy with what i can do now.

thanks, gordonm888, Sally and unJon.

thank Eliot for his shared excel doc of baccarat.