Casino Blackjack Ace problem!!

[DVLARMY]

I building a player evaluation excel file for the casino game blackjack. The problem comes when a Ace is dealt.

In the game of blackjack the Ace has the value of 1 or 11.

For example when a 10 value card is dealt with an Ace the value of the Ace is 11 for a total of 21 (winner). If a 8 is dealt with an Ace the total would be soft 19 (8+11). On the other hand if a hand is dealt like..4, 6, 2, Ace the total would be 23 or 13. A 23 would be a automatic loser for the game so the player or dealer would treat the hand as a 13 and play on.

Right now I have the program using a 1 to represent an Ace for other parts of evaluation.

What I'm trying to do is have the program recognize that a 1=Ace can be a 1 value or 11 value as long when seen as an 11 the combined total is not over 21 but when the combined total is over 21 the value is 1.

Thank You for any thoughts on my problem.

 

[Scott T]

Use IF to see the hand is less than or equal to 21 with the ace =11, if it is then the the ace=11. If with ace=11 is over 21 then check if it is OK with ace=1 if it is then ace=1 otherwise the player/dealer has gone over 21.

 

[DVLARMY]

Use IF to see the hand is less than or equal to 21 with the ace =11, if it is then the the ace=11. If with ace=11 is over 21 then check if it is OK with ace=1 if it is then ace=1 otherwise the player/dealer has gone over 21.

Thank you for the advise do you happen to have the formula?

 

[Scott T]

That would depend on how your sheet is setup. Can you post an example.

 

[DVLARMY]

The sheet is set up in which each column represents a round of play.

There is an area running down for the operator to enter player cards then dealer cards. I have added formulas to conduct the running count (blackjack count) and so on.. but I'm having problems with the player's and dealer's totals. I have a cell dedicated to total the dealer's hand and one cell to total the player's hand.

Hopefully this will help, thanks.

 

[Scott T]

How are the cards entered? Is Jack entered as Jack or 10? How is the Ace entered?

 

[DVLARMY]

All Face and 10 cards are simply 10 and the Ace is entered as 1.

 

[Scott T]

Since the ace can be 1 or 11 we want to excluded it from the sum until we know what value it has. Enter Ace instead of 1.

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Player[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]Ace[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Player Total[/TD]
[TD="align: right"]18[/TD]
[/TR]
</tbody>[/TABLE]

In B9 use this to get the total of the cards. you would do the same for the dealer. Adjust cell references and need.

=IF(COUNTIF(B3:B8,"ace")=4,IF(SUM(B3:B8,14)<=21,SUM(B3:B8,14),SUM(B3:B8,4)),IF(COUNTIF(B3:B8,"ace")=3,IF(SUM(B3:B8,13)<=21,SUM(B3:B8,13),SUM(B3:B8,3)),IF(COUNTIF(B3:B8,"ace")=2,IF(SUM(B3:B8,12)<=21,SUM(B3:B8,12),SUM(B3:B8,2)),IF(COUNTIF(B3:B8,"ace")=1,IF(SUM(B3:B8,11)<=21,SUM(B3:B8,11),SUM(B3:B8,1)),SUM(B3:B8)))))

I am assuming one one deck is used so the most aces one can have is 4.

 

[DVLARMY]

Wow. Thank You.
To change to a six deck blackjack game (24=Aces) do i change the 4 to 24?

 

[Scott T]

It is highly unlikely but if someone kept hitting it is mathematical possible someone could have 21 aces, one would thing you would stop hitting at that point. Realistically you not need to test for 21 aces but if you want a formula that will work with a unexpected number of aces the you need to test for a lot of aces

Rather than nest all those if functions it would be best to put a lookup table off to the side or on another sheet.

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]Player[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]Ace[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]Total[/TD]
[TD]18[/TD]
[/TR]
</tbody>[/TABLE]

off to the side or on another sheet
[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD][/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Number of Aces[/TD]
[TD]one ace is 11[/TD]
[TD]all aces are 1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]21[/TD]
[TD]21[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]20[/TD]
[TD]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]19[/TD]
[TD]19[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]18[/TD]
[TD]18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]17[/TD]
[TD]17[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]16[/TD]
[TD]16[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]15[/TD]
[TD]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]14[/TD]
[TD]14[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]13[/TD]
[TD]13[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]12[/TD]
[TD]12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]11[/TD]
[TD]21[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]10[/TD]
[TD]20[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]9[/TD]
[TD]19[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]8[/TD]
[TD]18[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]7[/TD]
[TD]17[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]6[/TD]
[TD]16[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]5[/TD]
[TD]15[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]4[/TD]
[TD]14[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]3[/TD]
[TD]13[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]2[/TD]
[TD]12[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]1[/TD]
[TD]11[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

in B25

=IF(COUNTIF(B3:B24,"ace")>=12,SUM(B3:B24)+VLOOKUP(COUNTIF(B3:B24,"ace"),K2:L11,2,0),IF(COUNTIF(B3:B24,"ace")>=1,IF(SUM(B3:B24,VLOOKUP(COUNTIF(B3:B24,"ace"),K12:L22,2,0))<=21,SUM(B3:B24,VLOOKUP(COUNTIF(B3:B24,"ace"),K12:L22,2,0)),SUM(B3:B24,VLOOKUP(COUNTIF(B3:B24,"ace"),K12:M22,3,0))),SUM(B3:B24)))