1. By continuing to use the site, you agree to the use of cookies .This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy.Find out more.
    Dismiss Notice
  2. Follow Casinomeister on Twitter | Facebook | YouTube | Casinomeister.us US Residents Click here! |  Svenska Svenska | 
Dismiss Notice
REGISTER NOW!! Why? Because you can't do diddly squat without having been registered!

At the moment you have limited access to view most discussions: you can't make contact with thousands of fellow players, affiliates, casino reps, and all sorts of other riff-raff.

Registration is fast, simple and absolutely free so please, join Casinomeister here!

Dummie Working On A BJ Audit Has An Excel ?

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

    Dec 7, 2008
  1. NASHVEGAS

    NASHVEGAS Banned 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:oops:) 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:confused:

    TIA,NV:)
     
  2. Dec 7, 2008
  3. RobWin

    RobWin closed 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. NASHVEGAS

    NASHVEGAS Banned 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. RobWin

    RobWin closed 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 :D
     
    Last edited: Dec 7, 2008
    1 person likes this.
  8. Dec 7, 2008
  9. jas2587

    jas2587 Ueber Meister

    Occupation:
    none
    Location:
    FL
    Hurry back Rob;)


    woohooo Gators:thumbsup:

    Go Titans:cool:

    Cindy
     
  10. Dec 7, 2008
  11. NASHVEGAS

    NASHVEGAS Banned 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. RobWin

    RobWin closed account

    Occupation:
    Who knows?
    Location:
    A Vault!
    I'm stuck...:oops: 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. NASHVEGAS

    NASHVEGAS Banned 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:thumbsup:
     
    1 person likes this.
  16. Dec 7, 2008
  17. RobWin

    RobWin closed 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. bryand

    bryand Beach Bum PABnonaccred mm1

    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. Stovetopp

    Stovetopp Senior Member MM

    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. jas2587

    jas2587 Ueber Meister

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



    Cindy;)
     
  24. Dec 8, 2008
  25. KasinoKing

    KasinoKing WebMeister & Slotaholic.. CAG MM PABnonaccred webmeister

    Occupation:
    House-Husband and Casino Advisor
    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.
    (Here shown underneath instead):-

    View attachment 15146

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

    KK
     
    3 people like this.
  26. Dec 8, 2008
  27. DogBoy001

    DogBoy001 Senior Member webmeister

    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...:rolleyes:
     
    Last edited: Dec 8, 2008
  28. Dec 8, 2008
  29. RobWin

    RobWin closed 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...:thumbsup:
     
  30. Dec 8, 2008
  31. Stovetopp

    Stovetopp Senior Member MM

    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. NASHVEGAS

    NASHVEGAS Banned User - flamming, disrespecting admin,

    Occupation:
    LOL
    Location:
    MERS
    Thanks KK:thumbsup:

    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.:what:;)....MUCH OBLIGE,KK:)

    Rob, any conspiracy theories on why the dealer's hands are, now were:rolleyes:, presented before the player's and the dealer's hand are not totaled?? LOLOL:D:D
     
    Last edited: Jan 12, 2009
  34. Dec 8, 2008
  35. NASHVEGAS

    NASHVEGAS Banned User - flamming, disrespecting admin,

    Occupation:
    LOL
    Location:
    MERS
    Having been retired for awhile, I agree the mental stimulation and being productive is enjoyable.:thumbsup:
     
  36. Dec 8, 2008
  37. RobWin

    RobWin closed 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
  39. KasinoKing

    KasinoKing WebMeister & Slotaholic.. CAG MM PABnonaccred webmeister

    Occupation:
    House-Husband and Casino Advisor
    Location:
    Bexhill on sea, England
    OK, re-visiting this a bit later I can see this Ace calculation is WRONG & does not work for all possible combinations of cards :oops:
    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...
    :confused:

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

Share This Page