+ Reply to Thread
Results 1 to 6 of 6

Probability Simulation?

  1. #1
    Registered User
    Join Date
    05-26-2015
    Location
    Mars
    MS-Off Ver
    3
    Posts
    3

    Probability Simulation?

    Hey folks. Excuse the pun, but I don't exactly excel at math.

    I need to build a sheet that simulates probability. For example, let's say it begins with a variable for "slot machine plays" that is either calculated elsewhere in the sheet or entered manually. Beside it is a column marked "big jackpot" with a certain percentage to "win" (let's say, 0.0014 probability). Rather than just divide and come up with a straight up number derived from the percentage, I'd like the "big jackpot" column to simulate the actual number of plays based on the original variable and come up with a random number of "wins" based on the win probability.

    I'm sure this can be done, but it's beyond my Excel-fu abilities.

    Any help?

    Thanks!

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Probability Simulation?

    In A1 & copy down,

    =if(rand() <= 0.0014, "Win", "Lose")
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    05-26-2015
    Location
    Mars
    MS-Off Ver
    3
    Posts
    3

    Re: Probability Simulation?

    Quote Originally Posted by shg View Post
    In A1 & copy down,

    =if(rand() <= 0.0014, "Win", "Lose")
    Thanks for the quick reply!

    I had considered a solution like that, but unfortunately my sheet is a little too complicated to utilize that method. I'd go into details of the specifics, but when I explain what I do for a living, folks tend to look at me like I've grown a third eye

    What I need to do, if possible, is have it all happen in a single cell. For example:

    A1 contains the number of "tries"
    B1 takes the number of tries and runs the probability (either based internally to the formula or pulled from a different cell) and returns the number of "wins" that occurred in that number of tries.

    I've done things like this in C based programming languages using for loops and incrementing a variable, but I'm not sure Excel can do things that complex in a formula?

    Thanks again for your help!

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,587

    Re: Probability Simulation?

    If it must all happen within a single cell, and you are already familiar with C programming, I would suggest that you turn your desired "simulator" into a User-defined function (http://www.excel-easy.com/vba/exampl...-function.html ) Since VBA is the programming language that comes bundled with Office, it is the most common language to use to cretae UDF's in Excel. Though, if you really wanted to, you can create them in C and call them in Excel: http://www.codeproject.com/Articles/...inedplusFuncti This latter option might be particularly interesting if you already have the code written in C and would only need to make it available to Excel.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  5. #5
    Registered User
    Join Date
    05-26-2015
    Location
    Mars
    MS-Off Ver
    3
    Posts
    3

    Re: Probability Simulation?

    Doing some more digging through the Internet, it appears as though Google Sheets use Javascript as their scripting language, which I know quite a bit better than VBA. Might try this in Google Sheets.

    Thanks to everyone for your help!

  6. #6
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Probability Simulation?

    You might be surprised how many people here have three eyes.

    Row\Col
    A
    B
    C
    D
    E
    1
    Trys
    100
    B1: Input
    2
    P(succ)
    1.00%
    B2: Input
    3
    # Succ
    Prob
    Cumu
    4
    0
    0.366032
    0.00000
    B4: =BINOMDIST(A4, B$1, B$2, FALSE)
    5
    1
    0.369730
    0.36603
    C4: =SUM(B3:C3)
    6
    2
    0.184865
    0.73576
    `
    7
    3
    0.060999
    0.92063
    8
    4
    0.014942
    0.98163
    9
    5
    0.002898
    0.99657
    10
    6
    0.000463
    0.99947
    11
    7
    0.000063
    0.99993
    12
    8
    0.000007
    0.99999
    13
    14
    Trial
    # Succ
    15
    1
    1
    B15: =INDEX($A$4:$A$12, MATCH(RAND(), $C$4:$C$12))
    16
    2
    0
    17
    3
    1
    18
    4
    3
    19
    5
    1
    20
    6
    0
    21
    7
    1
    22
    8
    1
    23
    9
    1
    24
    10
    3

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 2
    Last Post: 08-07-2013, 09:01 AM
  2. Help building a simulation based on probability
    By one11 in forum Excel General
    Replies: 4
    Last Post: 11-14-2010, 11:14 AM
  3. Probability Simulation
    By anil099 in forum Excel General
    Replies: 1
    Last Post: 02-13-2007, 03:54 PM
  4. Probability Simulation
    By anil099 in forum Excel General
    Replies: 3
    Last Post: 01-30-2007, 10:59 PM
  5. Probability Question:probability outcomes
    By Myles in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-01-2006, 06:45 AM

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