log in or register to remove this ad

 

Probability Math for Resistance (help!)

keterys

First Post
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.
 

log in or register to remove this ad

CovertOps

First Post
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

First Post
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

First Post
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.
 


keterys

First Post
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.
 
Last edited:

CovertOps

First Post
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:
Code:
@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 + ... = ???)
 


Elric

First Post
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.
 


sfedi

First Post
Naive question:

Do you really have to take this into account?

Aren't Resistances almost always below minimum damage?

If that's true, then you can do the simpler: avg damage - resistance

OTOH, a quick and dirty simplification would be to have a minimum and maximum damage for each type of roll, and assume they have a uniform distribution.
That's much more easier to calculate.

I mean, you want to detect outliers, so a little aproximation will not harm the process. With care, of course.
 

keterys

First Post
I was doing it that way when I did a batch of 8 high level critters, but something like 3 or 4 of the 8 actually went negative. I mean 2d6+7-10 isn't much negative and I did that in my head, but 3d8+7 Halved - 10 was a little more complicated but still doable in my head, but I knew when I jumped up to resist 15 it would just be coming up over and over. Really I was _hoping_ I could have it as a formula so that you could just enter the resistances for your party and it would all autoupdate appropriately.
 

sfedi

First Post
Well, with the simple approach you will detect those extreme cases.

And AFAIK you don't want the exact average damage, do you?
 

keterys

First Post
There's no such thing as an _exact_ average damage on critters like this, no. So, sure, it won't be off more than 0-2 on the average for most creatures affected. Ie, 2d6-7's avg is only like .97 and 3d8-13 is 1.87

So that's a good reason to at least put in the option to specify resistances and have it give you the close enough results. Though, I mean, if there did exist an actual formula to just plunk in and someone knew it, I'd still rather that. But I've got two solid options, good enough.
 

An Advertisement

Advertisement4

Top