The rule could be something like:
IF(@SUM[previous columns]<11,A=11,FALSE) and
IF(@SUM[previous columns]>11,A=1,TRUE)
You're definitely on the right track there Bryan...
The rule could be something like:
IF(@SUM[previous columns]<11,A=11,FALSE) and
IF(@SUM[previous columns]>11,A=1,TRUE)
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.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
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.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
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 aroundWill 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.
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 but not a 100% corrrelation. About another hour or so.