+ Reply to Thread
Results 1 to 6 of 6

Using =Rand() to simulate dealing cards

  1. #1
    Registered User
    Join Date
    04-15-2005
    Posts
    4

    Using =Rand() to simulate dealing cards

    Hi

    I know this is unlikely to be considered a useful or practical purpose for excel, however, i like to set myself challenges using excel to see if i can find a way of acheiving this (normally i can teach myself a few new more practical tricks along the way). Anyway, this time round, i am trying to create a simulator that will function as playing a game of blackjack. Enough of the introduction, lets get on with what i need help with

    I want to find a way to simulate somebody dealing from a deck of cards.

    What I have done currently:
    - using the rand function to pick a number between 1-13 (as whole integers) - where 1=A, 2=2....12=Q, 13=K etc
    - i have a wide array of if formulas working upon this data to then dictate by the number that appears what card this displays, and what this card then equals (so it works out that a Jack equals 10 etc).
    - with all this information running in the background, I have a prettier front sheet (that looks more similar to a blackjack table at a casino) that then displays this.
    - although macros are the last thing i will add (so they are not currently on there), I have been making extensive notes on what each macro will perform. For example, there will be a button that will be titled "Deal", and pressing this will present two cards in front of you, and show one card in front of the dealer.

    What I am having problems with currently:

    (1) Mainly, I want this to work more like an actual deck of cards. By saying that I mean that there are 52 cards in the deck, and once the king of spades has been drawn, it cannot come up as the next card as it is now on the table. Admitidly, I would like to actually create something where it would be working with about 6 decks of cards, but i guess in theory if it can be done with one deck, it can be done with 6 as well.

    But it is important to remember that this will be able to function for more than just one hand of blackjack being played. So, after the first hand has been played, and we may have lost 6 cards in total, for the next hand, there will only be 46 cards left in the deck, and it will somehow need to remember this (potentially to a point where it automatically shuffles the deck - or as it would work in practice, go back to a full deck to randomly select from, and then continue without reseting everything - as you will have a running bank that will have increased/decreased by this point).

    Does anyone have any ideas, preferably without any form of coding/programming that could achieve this? Dont mind if it is tediously painstaking to type out!

    (2) The second problem I am having, which is less of an issue right now, is making the ace perform as a 1 or 11. Any not too familiar with blackjack, you can use an ace as either number...so if i had 16, took a card, and it was an ace, it would equal 17 as my total. However, if i had 10, took a card, and it was an ace, this would give me 21.

    For the life of me, i cannot find a way of making the aces value, considering that you could potentially get dealt 6 and an Ace, draw a card and take another Ace, and this would equal 18, with the first ace equalling 11 and the second equalling 1. If anyone has any ideas for that it would be very helpful also.

    Thanks in advance for your help, i will be very grateful for any assistance you can provide. Like i've said, i realise that this is not your normal, 'having a few problems with my stock take' or something like that, but i struggle to find something that cant be done in excel, and i hate admitting defeat!!

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Using =Rand() to simulate dealing cards

    HI,
    You problem is very hard if not using any VBA Code is a requirement. If Code is allowed, search the net to see what others have done: For example:
    http://www.excelgames.org/blackjack.asp
    http://www.drewcampbell.net/blackjack.html

    and many more.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Using =Rand() to simulate dealing cards

    easy enough to randomise six packs, trouble is rand is volatile and recalculates when you enter new stuff but fwiw heres a random six pack shuffler!
    Attached Files Attached Files
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Using =Rand() to simulate dealing cards

    I started to write a Texas Hold-Um workbook a few years back. I never finished it but it deals cards, without repeats. See if this gives you an example of how to do part of what you want.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    04-15-2005
    Posts
    4

    Re: Using =Rand() to simulate dealing cards

    thank you guys, unable to have a look at these now cause at work, but as soon as i get home i'll check them out.

    it sounds as though its gonna be a great help.

    Any ideas about the Ace equalling 1 or 11 depending on your score problem (and taking into account that there could be multiple aces dealt in one hand)?

  6. #6
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Using =Rand() to simulate dealing cards

    I think you should have a two different cells for the cards dealt. One is the face of the card, one is the value. Then in each card value cell do something like

    =If(CardFace = "Ace", if(11+Sum(AllOtherCards)>21, 1,11),CardValue)

    I don't know if that would cause issues with multiple Aces, but it is a start.

    I uploaded an example.


    ***Edit***
    This causes a circular reference problem with two or more Aces. I don't have a solution for that yet.
    Attached Files Attached Files
    Last edited by Whizbang; 01-26-2011 at 12:48 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1