Dummie Working On A BJ Audit Has An Excel ?

Discussion in 'Online Casinos' started by NASHVEGAS, Dec 7, 2008.

Dec 7, 2008
1. NASHVEGASBanned User - flamming, disrespecting admin,

Occupation:
LOL
Location:
MERS
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

2. Dec 7, 2008
3. RobWinclosed account

Occupation:
Who knows?
Location:
A Vault!
Nash, do you know how to enter (sum=) for the rows and columns and create your formula ??

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

4. Dec 7, 2008
5. NASHVEGASBanned User - flamming, disrespecting admin,

Occupation:
LOL
Location:
MERS
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!!

6. Dec 7, 2008
7. RobWinclosed account

Occupation:
Who knows?
Location:
A Vault!
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

Last edited: Dec 7, 2008
1 person likes this.
8. Dec 7, 2008
9. jas2587Ueber Meister

Occupation:
none
Location:
FL
Hurry back Rob

woohooo Gators

Go Titans

Cindy

10. Dec 7, 2008
11. NASHVEGASBanned User - flamming, disrespecting admin,

Occupation:
LOL
Location:
MERS
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.

12. Dec 7, 2008
13. RobWinclosed account

Occupation:
Who knows?
Location:
A Vault!
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...

14. Dec 7, 2008
15. NASHVEGASBanned User - flamming, disrespecting admin,

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

1 person likes this.
16. Dec 7, 2008
17. RobWinclosed account

Occupation:
Who knows?
Location:
A Vault!
Yep LOL...maybe one of the math wizards will be along and make it look really simple...

18. Dec 8, 2008
19. bryandBeach Bum

Occupation:
Legal
Location:
Just Across the Hudson River
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.

2 people like this.
20. Dec 8, 2008
21. StovetoppSenior MemberMM

Occupation:
Retired
Location:
On the Beach
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

1 person likes this.
22. Dec 8, 2008
23. jas2587Ueber Meister

Occupation:
none
Location:
FL
wait I thought ya'll knew everything sigh now I am disappointed

Cindy

24. Dec 8, 2008
25. KasinoKingWebMeister & Slotaholic..CAGMMPABnonaccredwebmeister

Occupation:
Location:
Bexhill on sea, England
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):-

View attachment 15144

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:-

View attachment 15145

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.

View attachment 15146

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

3 people like this.
26. Dec 8, 2008
27. DogBoy001Senior Memberwebmeister

Occupation:
Managing Director
Location:
Great Southern Land
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...

Last edited: Dec 8, 2008
28. Dec 8, 2008
29. RobWinclosed account

Occupation:
Who knows?
Location:
A Vault!
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...

30. Dec 8, 2008
31. StovetoppSenior MemberMM

Occupation:
Retired
Location:
On the Beach
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.

32. Dec 8, 2008
33. NASHVEGASBanned User - flamming, disrespecting admin,

Occupation:
LOL
Location:
MERS
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

Last edited: Jan 12, 2009
34. Dec 8, 2008
35. NASHVEGASBanned User - flamming, disrespecting admin,

Occupation:
LOL
Location:
MERS
Having been retired for awhile, I agree the mental stimulation and being productive is enjoyable.

36. Dec 8, 2008
37. RobWinclosed account

Occupation:
Who knows?
Location:
A Vault!
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...

1 person likes this.
38. Dec 8, 2008

Occupation: