Math, Dice, and Burning Heretics - Help with Probability in Excel

[Lord Herman]

I'm kinda stuck with a math problem, and I figured I'd ask the playground for help.

You see, I'm writing a semi-simple excel sheet which lets me input weapon and armour data for GURPS weapons (specifically, Warhammer 40k weapons I'm converting to GURPS), and which then returns the probability of a certain weapon wounding, killing, or vapourizing the target.

Problem is, I'm not sure how to calculate the probability of the sum of X d6 rolls being a certain minimum number. Would any of you happen to know how to do this in Excel?

[mikeejimbo]

Ahhhhhhh statistics! Keep it away from me! It burns, it burns!!!!!!

(So that's how you're burning the heretics!)

Um, let's see... this sounds like a combinatorics problem, right?

Edit: You may want to google it. I bet you tuppence someone has done it before.

Further edit: This (http://wizardofodds.com/gambling/dice2.html) may be a good place to start.

[Erloas]

In excel it is easy enough to do 1d6 and 2d6. Anything larger then that either takes a lot of data entry or getting trickier with excel.

I don't know GURPS though so I don't know what system they use. Most things that use any sort of chart don't use over 2d6 that I've seen.

Either way you make a column of possible dice roles and then another column of the numbers added together, then use a cell with countif(range, ">=10") for instance to give you all of the possibilities that are 10 or greater and then divide that number by the total possible roll combinations. In the case of 2d6 the countif would return 6 and there are 36 possibilities, so a 1/6 chance or 16.67%.

{table]_A_|_B_|___C
1|1|=Ar+Br
1|2|=Ar+Br
1|3|=Ar+Br
1|4|=Ar+Br
1|5|=Ar+Br
1|6|=Ar+Br
2|1|=Ar+Br
2|2|=Ar+Br
...
6|5|=Ar+Br
6|6|=Ar+Br
[/table]
Where of course r is the row number.
Of course that is the "brute force" method and there are probably more elegant ways of doing it. Of course if you wanted 3d6 you would add another column and duplicate this 6 times and add a 1-6 column in front of it and add that to it as well, and then divide your target number by 216 instead of 36.

You can also do it using x/6 multiplied out a few times based on the numbers, which is a lot easier for more then 2d6, but its a little harder to type out in a way that is really easy to understand.

[Eldan]

Well... http://en.wikipedia.org/wiki/Dice#Probability

This can probably help. It contains the formula you would use on paper, just make one that can calculate the chance of rolling a certain sum with a certain number of dice with a certain number of sides.

[mikeejimbo]

Well, GURPS uses 3d6 as its base mechanic, but it's a roll-under. Damage, however, can be any number of d6s, so if that's what you're calculating it might be more difficult, especially because that link I sent gets screwy rounding for 15 and 20 dice, AND it doesn't have any dice between 10-15 or 15-20 or 20-25.

[Erloas]

Oh, there is also a "math and statistics based" search engine you can try to use.

I don't know how easy it would be to get it to understand what you want all the time, but you can see what you can find

as an example I tried
http://www40.wolframalpha.com/input/?i=10+on+3+dice
or
http://www40.wolframalpha.com/examples/Probability.html Might give you a better idea of how to get it to understand what you want from it.

[Lord Herman]

GURPS does usually switch to fewer dice with a multiplier once it gets past 10d damage. So I think those tables might indeed help. It's not as elegant as a formula, but it'll have to do.


Edit: Alright, I've used the tables, and everything seems to be working fine. Thanks for the help! :smallsmile: