Probability Math for Resistance (help!)

[keterys]

Okay, I'm working on an excel spreadsheet for monster damages and I want to be able to have it figure out the average damage for various levels of resistance... and I have no idea how to do that.

I do know that, for instance, 2d6 (avg 7) - 7 is _not_ average 0 though

3d6-4 is 10.5 + 1/216, reflecting replacing the 1 damage _not_ lost in the 1 in 216 chance of 3 1s.

2d8-4 would be 9 - 4 + 2/64 (1/64 off by 2) and 2/64 (2/64 off by 1) I think.

So how do I express that in a spreadsheet... ideally I'd like to be able to enter the resistance in a separate cell, but I am okay having duplicate pages for resist 5, 10, 15 and doing the work myself if it's too manual to be completely variable but is still very fast.

I guess worst case scenario, a link to a site that gives me the average of an arbitrary dice and bonus/penalty that treats negatives as 0 would cover my needs.

 

[CovertOps]

I can do that one manually, but I'm not sure how to convert it to a formula:

2d6 (6*6 = 36 outcomes)
Roll - resistance = damage / # of outcomes
2 - 7 = 0 / 1 -- (1/1)
3 - 7 = 0 / 2 -- (1/2, 2/1)
4 - 7 = 0 / 3 -- (1/3, 3/1, 2/2)
5 - 7 = 0 / 4 -- (1/4, 4/1, 2/3, 3/2)
6 - 7 = 0 / 5 -- (1/5, 5/1, 2/4, 4/2, 3/3)
7 - 7 = 0 / 6
8 - 7 = 1 / 5
9 - 7 = 2 / 4
10 - 7 = 3 / 3
11 - 7 = 4 / 2
12 - 7 = 5 / 1

You have to normalize to 36 (# of possible outcomes) on each result so...

All the 0 results come up 1 + 2 + 3 + 4 + 5 + 6 = 21 times
0 damage comes up 21 times = 0***
1 damage comes up 5 times = 5
2 damage comes up 4 times = 8
3 damage comes up 3 times = 9
4 damage comes up 2 times = 8
5 damage comes up 1 time = 5

Sum of the damage outcomes is 5 + 8 + 9 + 8 + 5 = 35
Then divide the final result by the # of outcomes 35/36 ~ 1 damage average.

*** The true sticking point of your formula is going to be how you handle any other static modifiers added to the roll. To get the "real" result you'll need to add the static modifier into the roll and subtract resistance THEN follow the above process.

A quick start of 3d8 would look like this:

3d8 (8*8*8 = 512 outcomes)
3 - 7 = 0 / 1 -- (1/1/1)
4 - 7 = 0 / 3 -- (1/1/2, 1/2/1, 2/1/1)
5 - 7 = 0 / 6 -- (1/1/3, 1/3/1, 3/1/1, 1/2/2, 2/1/2, 2/2/1)

In essence you have to figure out how many times any given result of the dice can occur and multiply the damage done on that result by the number of occurrences. Sum all those results and divide by the total number of possible outcomes. You could then take that result and multiply by your hit % (say .50) to come up with average DPR. This is statistics 101 so maybe a statistics major can give us the formula needed for your application based on input cells such as:

Dice: 3
Range: 8
Static Modifier: +4
Resistance: 7
which amounts to 3d8+4 - 7 (x d y+z)

You'd almost need a script so you can loop to do the stats on each outcome from x to x*y (or 3-24)

 

[keterys]

Yeah, the spreadsheet already handles 'average damage' * 'expected hit chance' with 'crits handled', for a level range (to give kinda low and high expectations)... it's just when I went to do resistances that I hit a complete block without doing it manually in a manner similar to the above.

I guess I could use a script, as a last resort. Though I guess if I did I could do it for 1-4 dice of each type for useful ranges of penalty and make sheets of output to link the cells to... PITA, but at least something I could conceivably do myself if no one knows.

Aren't I regretting not taking a probability / statistics course now

 

[CovertOps]

I took exactly 1 semester in College as a required class for my major. I think that's been 10 years ago now? I'm sure there is a formula that does what you need, but I'm almost positive you'd have to evaluate the formula for each result (looping) because with statistics formulas you're allowed to have negative results. Perhaps google for probability formulas because all you really need is how many ways are there to roll a 3 with 3d8 (and for all the other numbers from 3-24) because once you have the formula it will take the different parameters (3, 8, 3-24 as the result) as part of it's arguments and you can use it for any mix.

 

[CovertOps]

I found this with a quick google:

DPC

 

[keterys]

Yep, I've tried out the first four or so programs I found on google, and none seemed to do what I'm asking (including that one).

If that one _also_ just told me what the average was, instead of the probability of every single event, though, I'd probably not have asked and just done the manual 5/10/15 method. Hmm, though it also doesn't deal with negatives = zeroes.

 

[CovertOps]

Maybe you'd like to do lookup tables in Excel for each combo. Then you could do what you want by just plugging in the raw numbers from that site using the p(roll=x) column keeping in mind you'd have to round to the nearest whole number. For example:

@lookup 2d6      2d8
1        0       0
2       .02778  .01563
3       .05556  .03125
4       .08333  .04688
5       .11111  .0625
6       .13889  .07813
7       .16667  .09375
8       .13889  .10938
9       .11111  .125
10      .08333  .10938
11      .05556  .09375
12      .02778  .07813
13       0      .0625
14       0      .04688
15       0      .03125
16       0      .01563

Then just take the damage for the given roll such as 2d6+7-4(resistance) becomes:
2+7-4 = 5 * .02778 = .1389
3+7-4 = 6 * .05556 = .33336

And then just sum up the final numbers (.1389 + .33336 + ... = ???)

 

[DracoSuave]

=if(D6<0,0,D6) is your friend in this endevour.

 

[Elric]

Yep, I've tried out the first four or so programs I found on google, and none seemed to do what I'm asking (including that one).

If that one _also_ just told me what the average was, instead of the probability of every single event, though, I'd probably not have asked and just done the manual 5/10/15 method. Hmm, though it also doesn't deal with negatives = zeroes.

You can get the average (counting anything below 0 as zero) from a table like that with just addition (and knowing a neat formula that applies in this case). The average is the sum of (the chance the roll is >=1) + (the chance the roll is >=2), and so on.

In the 2d6-7 example, it's the last 5 numbers of the third column added together, which gives 0.972.

An equivalent method: Take the "naive average" as if results below zero counted as negatives (2d6-7=0 in this case), then add an amount equal to the chance that the roll is <=X, for each X below 0. In this case, it's the top 5 numbers on the far-right column, which again sums to 0.972.

Using the formula of this type with fewer numbers to add means that you'll rarely have to add many numbers from that table to get an answer.

 

[keterys]

Thanks for all the help all - made a table of 1dx-4dx with a -Y and that'll set me right enough.