Dummie Working On A BJ Audit Has An Excel ?

Okay, bit of an easier solution from my guru brother:

.....
In the Excel Spreadsheet with the hands:

Hit Alt-F8 to open Macros Dialog Box

Type GetValues in Macro Name:

Hit Create to open the Visual Basic Editor.

Copy in the following macro (delete any other text appearing in the create macro area):



Sub GetValues()

Dim r, c, v, a, aces, value As Integer

For r = 1 To Selection.Rows.Count

aces = 0

value = 0

With Selection.Cells(r, 1)

For c = 1 To .Characters.Count

v = CInt(Val(.Characters(c, 1).Text))

value = value + v

If v = 0 Then

Select Case .Characters(c, 1).Text

Case "A"

aces = aces + 1

Case "K", "Q", "J", "T"

value = value + 10

End Select

End If

Next c

End With

If (aces > 0) And (value < 11) Then

value = value + 11

aces = aces - 1

End If

value = value + aces

Selection.Offset(0, 1).Cells(r, 1) = value

Next r

End Sub



Hit Save (to anywhere, name it anything) and then Alt-Q to close Visual Basic Editor.

Hit Alt-F8 to open the Macros Dialog Box again.

Hit Options

Type t in Shortcut Key: (or any other letter you want)

Enter or OK

Hit Cancel

................
The macro is now ready to use.

To use it:

Select a group of hands (any number of rows within 1 column), or just select entire column

hit Ctrl-t

The hand values will appear in the right adjacent column.
If you've selected the entire column, hit ESC to end the macro

Woooof
 
Thanks Rob,KK,BryanD,Stovetopp,Woof et Jaz U2

I went the following route. Mission accomplished afaic:thumbsup:.

From hands 50,000 to 97,000+, the Dealer's largest number of cards in a single hand was 7. Thus, I used KK's "IF formula's" to assign values to the 3 face cards as well the T or 10 card so there were actually 5 Cards represented by Text rather than the 4 KK mentions. KK was still the Keyman to my ~90% more efficient solution for now.:)

Since the largest number of cards in a hand is 7 in the current group, I had to set up 5 text to numbers formulas for each card for a total of 35 in a row of cells plus a 36th formula that merges and totals the 35 converted text to number values with the original numbers as listed in the provided hand histories.

As far as the ACES, I used my own fully certified and tested by Bear Stearns, AIG, and Lehman Bros., 125 year old RNG that has been preserved in both the vaults of Wachovia and WaMu to insure 100% fairness. The RNG determined the value for the ACES. The number was neither 1 or 11 but one can not argue with the RNG as the number was 111. Must be a minor glitch but not a big deal. Afterall, 111 is really the same as 1 and 11:eek:.

With the just kidding aside, I will now simply go adjust all hands that had ACES accordingly and used 111 for all ACES so those hands requiring manual adjustment would be obvious and easy to recognize. Thus, with the help I received the time necessary to complete organizing the data (I have,hurry please with the balance) has probably been reduced by ~90% as previouly mentioned.

Once I complete organizing the data , then simple but important BJ analysis such as BJ's, 2 Card 20's, Dealer Upcard Distribution, Dealer Final Totals, Win/Lose/Push %'s*, Bust Rates*, Etc.,can proceed. Furthermore, where applicable for both the dealer and/or player the results can be compared to known statisical expectation. This can all be done very quickly.

Other analysis will take more time for several reasons.

I also will have to remove my played hands that deviate (guessing a few hundred hands out of 100,000 plus) from Basic Strategy (see prior asterisks above also). My BS deviation occurs at times from just playing too fast, visiting LaLa Land on occasion, sometimes on purpose by moi and a 4th reason beyond any player's control.

A small sample below of how the data looks after the sample formulas were posted by KK and the dummie (moi) ran with them,lol....The totals on the right took about 20 minutes to total 47,000 hands once the formulas were completed........The numbers on the left about 8 or 9 hours to manually total row by row ~10,000 hands with ~ 90,000 remaining prior to today. Many thanks:)



Qh,Kc
20​
20​
2c,4d,3d,8s
17​
17​
As,9h
20​
120​
Td,3s,Qd
23​
23​
6c,2d,Kd
18​
18​
Ks,As
21​
121​
2h,8c,Jc
20​
20​
 
Last edited:
Okay, bit of an easier solution from my guru brother:

.....
In the Excel Spreadsheet with the hands:

Hit Alt-F8 to open Macros Dialog Box

Type GetValues in Macro Name:

Hit Create to open the Visual Basic Editor.

Copy in the following macro (delete any other text appearing in the create macro area):



Sub GetValues()

Dim r, c, v, a, aces, value As Integer

For r = 1 To Selection.Rows.Count

aces = 0

value = 0

With Selection.Cells(r, 1)

For c = 1 To .Characters.Count

v = CInt(Val(.Characters(c, 1).Text))

value = value + v

If v = 0 Then

Select Case .Characters(c, 1).Text

Case "A"

aces = aces + 1

Case "K", "Q", "J", "T"

value = value + 10

End Select

End If

Next c

End With

If (aces > 0) And (value < 11) Then

value = value + 11

aces = aces - 1

End If

value = value + aces

Selection.Offset(0, 1).Cells(r, 1) = value

Next r

End Sub



Hit Save (to anywhere, name it anything) and then Alt-Q to close Visual Basic Editor.

Hit Alt-F8 to open the Macros Dialog Box again.

Hit Options

Type t in Shortcut Key: (or any other letter you want)

Enter or OK

Hit Cancel

................
The macro is now ready to use.

To use it:

Select a group of hands (any number of rows within 1 column), or just select entire column

hit Ctrl-t

The hand values will appear in the right adjacent column.
If you've selected the entire column, hit ESC to end the macro

Woooof
Thanks and read something similar a couple days ago but had not read your post until now and as I mention in my original post, I have never used a macro so prolly was not up to the challenge.

That said, your post is clearer and more detailed than what I read in regards elsewhere and the dummie (moi) is getting cocky,lol. Have 50000+ more hands currently in hand (npi) to total so I will attempt to lose my macro virginity. Always enjoy learning and appreciate your knowledge and post:thumbsup:
 
Thanks and read something similar a couple days ago but had not read your post until now and as I mention in my original post, I have never used a macro so prolly was not up to the challenge.

That said, your post is clearer and more detailed than what I read in regards elsewhere and the dummie (moi) is getting cocky,lol. Have 50000+ more hands currently in hand (npi) to total so I will attempt to lose my macro virginity. Always enjoy learning and appreciate your knowledge and post:thumbsup:

No worries, let me know if you run into difficulties.
I tested it after my bro sent it across and it generated the entire column in seconds, so if you can get it to work it'll be useful. Good luck!

Woooof
 
No worries, let me know if you run into difficulties.
I tested it after my bro sent it across and it generated the entire column in seconds, so if you can get it to work it'll be useful. Good luck!

Woooof
Will try it for the learning experience and if successful will save me some time but not much. Will let you know how it goes.

In actuality, I just needed the formula to simply convert the 4 text 10's to 4 numeric 10's and the ACES to 111 despite my post above about randomly selecting 111. I really was not concerned about the Aces per se and 111 was the key magic, lol, for a real dummie:D.

I am not sure why but I, yes just moi, had realized that by using 111 (well duh the same as 11;) but still a reason not to just default all ACES to 11) as the numeric value for Aces, that all hands with 2 and 3 cards and only one ACE as in Blackjack ,lol, could simply be handled very quickly with the "find and replace all" function to change all values of 121 to 21, 120 to 20, 119 to 19 ,118 to 18, 117 to 17 and so forth to 112 with 12. Took about 5 minutes to change those hands which corrected ~ 70% to 75% of hands with ACES. Then to be safe I simply manually checked the remaining cells with ACES despite some more magic of 111 :D but not a 100% corrrelation. About another hour or so.
 
Will try it for the learning experience and if successful will save me some time but not much. Will let you know how it goes.

In actuality, I just needed the formula to simply convert the 4 text 10's to 4 numeric 10's and the ACES to 111 despite my post above about randomly selecting 111. I really was not concerned about the Aces per se and 111 was the key magic, lol, for a real dummie:D.

I am not sure why but I, yes just moi, had realized that by using 111 (well duh the same as 11;) but still a reason not to just default all ACES to 11) as the numeric value for Aces, that all hands with 2 and 3 cards and only one ACE as in Blackjack ,lol, could simply be handled very quickly with the "find and replace all" function to change all values of 121 to 21, 120 to 20, 119 to 19 ,118 to 18, 117 to 17 and so forth to 112 with 12. Took about 5 minutes to change those hands which corrected ~ 70% to 75% of hands with ACES. Then to be safe I simply manually checked the remaining cells with ACES despite some more magic of 111 :D but not a 100% corrrelation. About another hour or so.
OOPS, bold above should read "and so forth to 113 with 13". The "find and replace all value" for 12 was 222. (A,A=111+111)....I'll do better proofing next time. Glad GM must not be around:lolup:
 
Not sure if this helps, but this code should do the trick. First calculate the sum of cards with Ace = 11, then the code calculates the corrected sum in cell below.
 

Users who are viewing this thread

Meister Ratings

Back
Top