Calculating Success Probability for D20 Skill Challenges in 4E D&D

[jgsugden]

A d20 probability question...

If you know what a d20 is, perhaps you can help out a fellow gamer who is trying to make the gaming world a better place. I'm trying to create a tool that helps people design balanced skill challenges for the new 4E D&D game.

To create this tool for fellow gamers, I need to figure out the easiest way to calculate the chance of succeeding on a probability roll X times before failing Y times. Preferably, I'd like this to be something I could calculate in excel.

I'd like to be able to figure out things like:

* The chance of rolling 5 numbers greater than or equal to 8 before I roll 3 numbers less than 8 on a twenty sided die (a d20).

* The chance of rolling 2 numbers greater than or equal to 14 before I roll 4 numbers less than 14 on a twenty sided die.

* The chance of rolling 4 numbers greater than or equal to 12 before I roll 3 numbers less than 12 on a twenty sided die.

I don't need to know how to calculate this for more than 10 rolls of the dice (# of successes needed + number of failures to avoid = 11).

Can anyone help me out and explain it in a way that would make sense to someone who's most recent experience in this arena is a basic probaility class 15 years ago?

 

[CRGreathouse]

This should be doable in a spreadsheet.

Make a cell (say, B2) with the probability to succeed on a given roll (9 or higher on a d20 = 60% = 0.60). This represents the chance of 1 victory before 1 failure.

The cell to its right (C2) then represents the chance of 2 victories before 1 failure, and so on to the right. Enter the formula =$B$2 * B2 and drag to the right as far as desired. ("The chance of winning X in a row is the chance of winning 1 * the chance of winning X-1 in a row")

The cell below the first cell (B3) represents the chance of 1 victory before 2 failures. Enter the formula =$B$2 + (1 - $B$2) * B2 and drag down as far as desired. ("The chance of winning 1 before losing X is the chance of winning 1, plus the chance of losing 1 * the chance of winning 1 before losing X-1")

The cell below and to the right of the original (C3) represents the probability of 2 victories before 2 failures. Enter the formula =$B$2 * B3 + (1 - $B$2) * C2, drag to the right as desired, then drag down as desired. ("The chance of winning M before losing N is the chance of winning 1 * the chance of winning M-1 before losing N, plus the chance of losing 1 * the chance of winning M before losing N-1")

Now you just need to check me. I did this off the top of my head, so I may have made some silly mistake.

 

[jgsugden]

That is exactly what I needed. At first blush, it looks about right. I'll test it out later using Excel's Rand Function to make sure it works.

I'll post your answer over to various D&D threads (once tested). Thanks!