Dummie Working On A BJ Audit Has An Excel ?

[NASHVEGAS]

If anyone is an EXCEL 2003 expert for dummies, I have a question on the easiest way (read some complicated ones I think) if possible with a hand history to convert text or a string of text in a column to numeric values so I do not have to keep totaling each hand (over 100K total hands/rows) row by row. A sample of 10 rows and 2 columns (not aligning) below:

Qd,Tc​

20​

3s,5c,Kd​

18​

5c,5c,6c,5d​

21​

Ac,4c,2s,6h,3h,Ks

26​

6d,8s,Tc

24​

Qd,3s,2c,8h

23​

5h,Qc,Qd

25​

Td,Ks

20​

Th,7s

17​

8c,4h

12​

P.S.-Never have done an EXCEL macro (or separate worksheet even) if that is the way to go but I can follow detailed instructions for dummies

TIA,NV

 

[RobWin]

Nash, do you know how to enter (sum=) for the rows and columns and create your formula ??

Example: =N10+N11+SUM(N10:N11)

 

[NASHVEGAS]

Nash, do you know how to enter (sum=) for the rows and columns and create your formula ??

Example: =N10+N11+SUM(N10:N11)

That I do or I use the auto sum button for the basics. Never have used it to add text (and numbers) though ,LOL, but I have an idea ,well sorta, on what you may instruct. Thanks for any help!!

 

[RobWin]

That I do or I use the auto sum button for the basics. Never have used it to add text (and numbers) though ,LOL, but I have an idea ,well sorta, on what you may instruct. Thanks for any help!!

Ok, in your formula you need to assign values to the K,Q & J of 10 and an 11 or 1 to the "A" but that could also be a little tricky since the "A" may have been used as a 1 or 11...let me check something here...be back in a few

 

[jas2587]

Hurry back Rob


woohooo Gators

Go Titans

Cindy

 

[NASHVEGAS]

Hurry back Rob

No rush, Falcons in a close and important one, in the 4th .

woohooo Gators

#1 in the AP poll but who knows with the BCS until a done deal

Go Titans

28-9 in the 4th

Cindy

Going to check all those formula's et al out again later unless someone can lead this horse to water with the correct formula. Have to account for the suits of each card and although there is prolly a if,then,>,< way to account for the Aces, prolly simplier just to assign an 11 value to all 4 Aces and manually correct values that equal 1. Will stay the course of row by row which is time consuming but could be beneficial (not statiscally) also.

 

[RobWin]

Going to check all those formula's et al out again later unless someone can lead this horse to water with the correct formula. Have to account for the suits of each card and although there is prolly a if,then,>,< way to account for the Aces, prolly simplier just to assign an 11 value to all 4 Aces and manually correct values that equal 1. Will stay the course of row by row which is time consuming but could be beneficial (not statiscally) also.

I'm stuck... Can't figure out how to account for the different value of the aces and it is most likely something real simple too but just can't manage to decipher it thru the fog...

 

[NASHVEGAS]

I'm stuck... Can't figure out how to account for the different value of the aces and it is most likely something real simple too but just can't manage to decipher it thru the fog...

Appreciate you trying. Can't expect us construction guys to know everything but just about everything,LOL

 

[RobWin]

Appreciate you trying. Can't expect us construction guys to know everything but just about everything,LOL

Yep LOL...maybe one of the math wizards will be along and make it look really simple...

 

[bryand]

Cool project for me to work on while I'm bored....lol. The formula needs an "If, Then" statement to deal with the ace issue. I'll post the formulas when complete.

 

[Stovetopp]

I assume that the "suit" (c,d,h,s) does not matter.
I have been able to separate the one column into 6 columns not including the total.
The next step is to convert K's. Q's, J's to = 10 then remove the "suits" convert the Ace and SHAZAM LOL We got it
Havent used excel in years so will work on it

 

[jas2587]

Appreciate you trying. Can't expect us construction guys to know everything but just about everything,LOL

wait I thought ya'll knew everything sigh now I am disappointed



Cindy

 

[KasinoKing]

I assume that the "suit" (c,d,h,s) does not matter.
I have been able to separate the one column into 6 columns not including the total.
The next step is to convert K's. Q's, J's to = 10 then remove the "suits" convert the Ace and SHAZAM LOL We got it
Havent used excel in years so will work on it

Well I'm quite good with Excel - but this is a toughie!
I could do it, but I would do it the long (dummies) way - requiring 4 extra columns per card to start with, then a few more at the end to handle the 'Ace' issue:-
(This was done on Excel 97 - hopefully the newer versions are similar!)

First, (assuming the suits are not important!) get rid of all the suit letters & the commas, and have 1 card per column;

Highlight all the cells with the data in, click 'Data', 'Text to columns', 'Fixed width', 'Next'.
Here put a break between each symbol (shown half-done in this screenie):-

Old Attachment (Invalid)

The result is at the top of this next picture.
Remove the unwanted columns, and you'll end up with the bottom part of this picture:-

Old Attachment (Invalid)

For each card (or column), add formulas in 4 more columns to give values of 11 for Aces & 10 for picture cards. I have shown the 'IF' formulas in blue under the cells. (Only shown for column 'A' in my shot).
Do a sum at the end for the entire row starting from column A.

In the columns to the right of your answer, add the second bit below to account for the Aces.
(Here shown underneath instead):-

Old Attachment (Invalid)

Copy & Paste these formula all the way down your spreadsheet.
A bit long winded - but quite simple I think...?

Errr.... Now you have ended up with the total you had in the first palce... what was the question again?

KK

 

[DogBoy001]

For each card (or column), add formulas in 4 more columns to give values of 11 for Aces & 10 for picture cards. I have shown the 'IF' formulas in blue under the cells. (Only shown for column 'A' in my shot).
Do a sum at the end for the entire row starting from column A.

But doesn't that assume the Ace is always counting for 11, which isn't the case?
I imagine the tricky bit is whether the Ace is 1 or 11.

Woooof


Ooops, I really should read to the end of the post...ignore me...

 

[RobWin]

Good job KK, that's where I was getting stuck...trying to remember how to apply the different values to the formula for the Ace...

 

[Stovetopp]

Very good KK!!! Although I got as far as you have shown here)
In order to determine the Value of the Ace,maybe we could use the given TOTAL and then use a real sloppy formula to determine if the ACE was used as an 11 or a 1.
Remember we can always hide all these fomulas and just show the results the OP desire..Will work on it today instead of playing video poker It's less expensive and more mentally stimulating.

 

[NASHVEGAS]

Thanks KK

I really did figure out prior to my OP post how to use the Convert Text To Columns Wizard as you can see in the lovely screenshot (showing how the hand histories were delivered-colors by moi,ftr) below to get the dealer's and player's hands in their own column(s)-a nightmare with doubled splits hands,imagine 4- but it is done. Also notice the player's hands were totaled so those totals went right into their own column (except the splits which is my bad but I cleaned the mess up).

Wonder why they do not total the dealer's hand??? Anyway, I have already totaled about 10000 dealer's hands row by row in order to create different preliminary databases (sorted) and takes longer to total than you would think. Need to do all plus to get as large a sample size as possible, I requested the balance of my hand histories and hopefully will have very soon.

KK, your mention of how to use the breakers (I did not know) will make some of the "not as easy" analysis I intend to do much easier once I get the dealer's hands totaled following your instructions. Gotta let the neurotransmitters rest before I attempt though.....MUCH OBLIGE,KK

Rob, any conspiracy theories on why the dealer's hands are, now were, presented before the player's and the dealer's hand are not totaled?? LOLOL

 

[NASHVEGAS]

Very good KK!!! Although I got as far as you have shown here)
In order to determine the Value of the Ace,maybe we could use the given TOTAL and then use a real sloppy formula to determine if the ACE was used as an 11 or a 1.
Remember we can always hide all these fomulas and just show the results the OP desire..Will work on it today instead of playing video poker It's less expensive and more mentally stimulating.

Having been retired for awhile, I agree the mental stimulation and being productive is enjoyable.

 

[RobWin]

Thanks KK

I really did figure out prior to my OP post how to use the Convert Text To Columns Wizard as you can see in the lovely screenshot (showing how the hand histories were delivered-colors by moi,ftr) below to get the dealer's and player's hands in their own column(s)-a nightmare with doubled splits hands,imagine 4- but it is done. Also notice the player's hands were totaled so those totals went right into their own column (except the splits which is my bad but I cleaned the mess up).

Wonder why they do not total the dealer's hand??? Anyway, I have already totaled about 10000 dealer's hands row by row in order to create different preliminary databases (sorted) and takes longer to total than you would think. Need to do all plus to get as large a sample size as possible, I requested the balance of my hand histories and hopefully will have very soon.

KK, your mention of how to use the breakers (I did not know) will make some of the "not as easy" analysis I intend to do much easier once I get the dealer's hands totaled following your instructions. Gotta let the neurotransmitters rest before I attempt though.....MUCH OBLIGE,KK

Rob, any conspiracy theories on why the dealer's hands are, now were, presented before the player's and the dealer's hand are not totaled?? LOLOL

Simple Alphabetization I would imagine on that one...

The Dealers Hands not being totaled should be due to the way the formula was written and entered but I'm not 100% certain on either one of those...

 

[KasinoKing]

In the columns to the right of your answer, add the second bit below to account for the Aces.
(Here shown underneath instead):-

Old Attachment (Invalid)

OK, re-visiting this a bit later I can see this Ace calculation is WRONG & does not work for all possible combinations of cards
At the moment I can't see a way around this; I need an 'IF A>X and B<Y then...' function, but I can't find it on Excel...


Anyway - looks like Nash is sorted now, so I wont waste any more time on this.
KK

 

[NASHVEGAS]

Anyway - looks like Nash is sorted now, so I wont waste any more time on this.
KK

Part I of your instructions went perfect and have been completed. Screenshot below

Not sure on Part II but may see if your formulas work for the Tens and will autofill all cells with Aces with an error (ACE=PI R SQUARED) or 11 and I can manually total. The objective (for this virgin auditor aka dummie) at least for the most basic analysis of Dealer hands is Columns S and T of the second screenshot below.

Somehow I have accomplished S. Column T is simple addition but if I can avoid further row by row human addition aka finger counting then so much time is saved even if I manually have to correct and/or proof every cell with an ACE.....Just thinking (that's the real problem), if no formula is presented and/or possible why not have the ACE'S be 11 so most ACE errors would be apparent although I would still have to check every hand with an ACE. Thoughts welcome as I am tired and may not be thinking clearly. OK, I never think clearly

 

[bryand]

OK, re-visiting this a bit later I can see this Ace calculation is WRONG & does not work for all possible combinations of cards
At the moment I can't see a way around this; I need an 'IF A>X and B<Y then...' function, but I can't find it on Excel...


Anyway - looks like Nash is sorted now, so I wont waste any more time on this.
KK

Maybe a sort command wherein the ace, if one or more, always appears in the last column(s) and then the value for aces in a vlookup table depends on the sum of the previous column(s). Just a thought.

 

[RobWin]

Maybe a sort command wherein the ace, if one or more, always appears in the last column(s) and then the value for aces in a vlookup table depends on the sum of the previous column(s). Just a thought.

Probably a good idea but you still need to justify a way to determine when (as in what scenario) the Ace is valued as 11 verses 1 and then this would be the table rule to formulate the insert per say...

 

[RobWin]

It would be more simple if there were a small (a)ace = 1 and a big (A)Ace = 11

 

[bryand]

Probably a good idea but you still need to justify a way to determine when (as in what scenario) the Ace is valued as 11 verses 1 and then this would be the table rule to formulate the insert per say...

The rule could be something like:

IF(@SUM[previous columns]<11,A=11,FALSE) and
IF(@SUM[previous columns]>11,A=1,TRUE)