How to create binomial tables to handle mass die rolling

[huginn]

Years ago I seen an article for D&D for handling massive rolling of to hit or saves. Instead of rolling 20 to hit rolls or saves you make one d100 roll and consult the table to see how many were successful

Does anyone know what equations I can plug into Excel to create tables?

[Segev]

An easier way to do it than creating a d100 table would be simply to take the average.

For example, if you have an army of 100 goblins with +4 to hit who do 1d6 damage on a hit attacking a squad of 20 armored knights with AC 20, you could take the average damage of the goblins on a hit (3.5) and multiply it by 100 (350), and then multiply that by the probability that any given goblin will hit (hitting on a 16+ for 25% chance to hit, with 5%*25%=1.25% chance for "damage again"), for a net .2625*350=91.875 average damage output per round from the 100 goblins on this squad of knights. With 20 knights, just assume the damage is spread evenly by the swarming goblins, so each knight takes 4.5938 damage per round.

You can round that up or down, or accumulate the fractions as part of your simulation, whichever is easier/gives more fidelity.

You can do similar for the knights, though it's probably going to turn out that the knights will, on average, kill one goblin per round (maybe 2+ if they have multiple attacks), and it'd be easier just to track that, and re-calculate the raw damage from the goblins based on how many there are.

For a bit more fidelity, you could reduce the number of goblins from "all in the swarm" to "how many can get adjacent."

In this case, it'd be easier to take the 3.5 per goblin multiplied by the .2625 expected damage for .9188 hp/round/goblin (round up to 1 to make it easy). So each goblin that is adjacent to the armored knights deals 1 hp to a knight per turn.

Expected damage calculations like this give averages, and thus will have less than satisfactory results when dealing with individual creatures, but when dealing with large numbers, they're often faster than rolling and the results will be very close.

[meschlum]

Or you can math things out, which isn't all that hard but requires a bit of notation.

Since your focus is on d20 uses, we can work with that (if you're rolling attacks, you'd still need to roll the appropriate number of damage dice). Note that this assumes that the odds of success are the same for each roll, and the consequences are the same too (if some attacks do more damage than others, knowing that 3 attacks hit but not which ones isn't exactly helpful).

Let the probability of success with one roll be P, and the number of rolls you're making be N. Then for a number of successes S, you have p(S) = (N! / (S! * (N - S)!)) * P^S * (1 - P)^(N - S)

You can create a table from there, especially as Excel has lots of handy options:

- FACT(n) gives you n! (i.e. 1 * 2 * 3 * ... * n), and you can plug that into the equation above
- COMBIN(a, b) gives you the number of combinations of b occurrences among a tests, i.e. the first part of the equation (N! / (S! * (N - S)!)) where a = N and b = S
- BINOM.DIST or BINOMDIST(a, b, c, d) depending on your version of Excel gives you the actual probability, where a is the number of successes (S), b is the number of rolls (N), c is the probability of success (P) and d is TRUE (1) or FALSE (0). If d is TRUE (or 1), you get the odds of having a or fewer successes, and if d is FALSE (or 0) you get the odds of having exactly a successes.

[martixy]

I'd normally give you a comprehensive answer, but I'm too lazy right now, so 2 things:
1. Here's an old table I made to study when Aid Another helps using the binomial distribution: https://docs.google.com/spreadsheets/d/14P-_AUDhtIEwav7dhF5G7AD0OowlRq2MmRVabsqDTU8
2. The function you're looking for is called BINOMDIST.

[The Kool]

Math is fun! That said, if I'm doing a middling number of rolls (10-20) I just do it by hand. Fistful of d20s, go! If I'm doing more, I roll by 5's usually. Maybe 10's if it's a lot of them. Have to roll saves for 50 orcs? That's actually only 10 rolls, which is five distinct rolls if I roll two dice at a time. Saves buckets of time.

[Llyarden]

Considering that what you want is to find out how many successes there were for a given probability, rather than the probability of a certain number of successes, I think you actually want BINOM.INV or an equivalent. This is what I came up with after some brief tinkering (on Excel 2016):

=BINOM.INV(N,(21-MIN(MAX(D-B,1),20))/20,MIN(0.99,R/100))

Where N is the number of creatures making the roll, D is the DC of the roll, B is the bonus to the roll, and R is what you rolled on the d100.

(It assumes that natural 1s always fail, natural 20s always succeed, and that 100 on the d100 is equal to 99 because a probability of 1 doesn't work on my particular version of BINOM.INV.)

[Crake]

The question is, do you want an even distribution? or a weighted distribution?

[Segev]

The question is, do you want an even distribution? or a weighted distribution?

I'm confused. Why would you want weighted distribution? The dice you're simulating have uniform or pseudo-normal distributions (depending whether they're single dice, e.g. d20 or 1d6, or multiple dice like with a great sword's 2d6).