"How to calculate a distribution curve for hits....." Topic

I'd like to build a spreadsheet that does the following:

I enter the number of dice thrown, and the target for success and get the distribution (% chance of 0 successes, % chance of 1 success, etc.) So, for example, if my target is 4+ and I have three dice what is my chance of 0,1,2, or 3 successes?

I want to do a spreadsheet so I could just enter 27 dice on 3+ or 11 dice on 5+ or 3 dice on 6 only. Output would be a breakdown by number of successes.

I'm guessing it already exists and I just need a kind soul to send me a copy?

Try this: anydice.com

To clarify, the above link is not a spreadsheet, but a website that can do all those calculations.

Aha! The "count" function does just that! Okay I may just have to do cut and paste but that helps!

What are you up to EC? Something cool I hope!

Excel has a binomial distribution function in which you can set the number of trials, the number of successes (specifically, or cumulatively), and the probability of success on each trial. Works nicely.

Doug

If you use Windows, you need to download and run SmallRoller.exe. It is an old program, but it will do what you want and lots more. I must have used it every week for the past 10 years!

Link:

link

I know of other gamers that swear by it, so it is not just me.

For something like this, the above advice will be excellent. However, sometimes the mechanics get sufficiently weird that they can't easily be modeled by a readily available distribution. In that case, you can do a monte carlo simulation.

1) Write a row in excel that calculates the results you want. This could include if statements to do re-rolls, etc. It doesn't matter how many calcs you do or columns you use, so long as it all fits on one row. Make sure there's an entry for the final result (number of hits, success/fail, whatever). Make that row 8 or so. Hint: dX = ROUNDUP(RAND()*X,0)

2) Copy that row down. 10,000 times is usually a good place to start. If there are constants you want to check, put them above the roll rows and make sure to use absolute references (dollar signs).

3) Now above the results, simply use COUNT, AVERAGE, MEDIAN, STDEV or whatever. A histogram is sometimes useful.

4) Go to a blank cell and hit the delete key several times. Watch your parameter of interest. If it changes much, then add more rows (make sure your summary stats include the new rows!) If the results are relatively stable, then congratulations! You've done an empirical distribution.

The advantages of doing it this way are that A) they're easy even when your stats background is weak, B) they're easy to verify by eye, as opposed to derivations where missing a digit can go unnoticed, and C) it's very quick to do. Corollary: because it's quick and simple, you can also test special rules, which tend to be hard to incorporate into a derivation after the fact.

Thanks to the central limit theorem, most very complicated dice systems converge on a normal distribution, but with a monte carlo simulation you can dispense with the unnecessary stats and still get a useful, actionable, decently rigorous answer.

Basically I'm recreating a spreadsheet I lost….

It replaced buckets o dice with a base 6 chart. So instead of rolling 23 dice for 4+ you roll 2d6 and read the chart. Keeps the dice off the table….

And the 2d6 are read as 11,12,13,14,15,16,21,22,23,24…..65,66