# Probability Simulation?

1. ## 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. ## Re: Probability Simulation?

In A1 & copy down,

=if(rand() <= 0.0014, "Win", "Lose")

3. ## Re: Probability Simulation?

Originally Posted by shg
In A1 & copy down,

=if(rand() <= 0.0014, "Win", "Lose")

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?

4. ## 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.

5. ## 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. ## 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

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

#### Posting Permissions

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

## X vBulletin 4.1.8 Debug Information

• Page Generation 0.06853 seconds
• Memory Usage 9,110KB
• Queries Executed 15 (?)
Template Usage (34):
• (1)bbcode_quote
• (1)footer
• (1)forumrules
• (1)gobutton
• (6)memberaction_dropdown
• (1)navbar
• (4)navbar_noticebit
• (6)option
• (6)postbit_legacy
• (6)postbit_onlinestatus
• (6)postbit_wrapper
• (1)spacer_close
• (1)spacer_open
• (1)tagbit_wrapper

Phrase Groups Available (6):
• global
• inlinemod
• postbit
• posting
• reputationlevel
Included Files (40):
• ./vbseo.php
• ./env.php
• ./vbseo/includes/functions_vbseo.php
• ./vbseo/includes/functions_vbseo_pre.php
• ./vbseo/includes/functions_vbseo_url.php
• ./vbseo/includes/functions_vbseo_createurl.php
• ./vbseo/includes/functions_vbseo_db.php
• ./vbseo/includes/functions_vbseo_vb.php
• ./vbseo/includes/functions_vbseo_seo.php
• ./vbseo/includes/functions_vbseo_misc.php
• ./vbseo/includes/functions_vbseo_crr.php
• ./vbseo/includes/functions_vbseo_cache.php
• ./vbseo/includes/functions_vbseo_hook.php
• ./vbseo/includes/functions_vbseo_startup.php
• ./includes/config.php
• ./global.php
• ./includes/class_bootstrap.php
• ./includes/init.php
• ./includes/class_core.php
• ./includes/functions.php
• ./includes/class_friendly_url.php
• ./includes/class_hook.php
• ./includes/functions_cforum.php
• ./includes/functions_bigthree.php
• ./includes/class_postbit.php
• ./includes/class_bbcode.php
• ./includes/functions_reputation.php
• ./includes/class_bootstrap_framework.php
• ./vb/vb.php
• ./vb/phrase.php
• ./includes/functions_notice.php
• ./packages/vbattach/attach.php
• ./vb/types.php
• ./vb/cache.php
• ./vb/cache/db.php
• ./vb/cache/observer/db.php
• ./vb/cache/observer.php
• ./packages/vbforum/bbcodehelper/table.php

Hooks Called (48):
• init_startup
• friendlyurl_resolve_class
• database_pre_fetch_array
• database_post_fetch_array
• global_bootstrap_init_start
• global_bootstrap_init_complete
• cache_permissions
• fetch_foruminfo
• global_state_check
• global_bootstrap_complete
• global_start
• style_fetch
• global_setup_complete
• friendlyurl_redirect_canonical
• bbcode_fetch_tags
• bbcode_create
• postbit_factory
• postbit_display_start
• cache_templates
• template_register_var
• parse_templates
• notices_check_start
• notices_noticebit
• process_templates_complete
• reputation_image
• postbit_imicons
• bbcode_parse_start
• bbcode_parse_complete_precache
• bbcode_parse_complete
• postbit_display_complete
• memberaction_dropdown
• tag_fetchbit_complete