Math question for average damage calculation

[JAL_1138]

I know how to calculate average damage on a hit, that's easy enough. For damage on a hit with, say, a polearm, 20 Str, and Booming Blade, I'd plug "=5.5+5+(4.5x3)" into Excel. But say I'm trying to calculate average damage based on accuracy vs a given AC, including crit damage. What equation would I plug into Excel to do that (for any attack, not just that example?)

[Contrast]

((((Chance to hit)-(chance to crit))*(damage))+((chance to crit)*(damage on crit)))*(number of attacks) = Average damage

Edit - for clarity, your chance to hit will be whatever you need to roll on a d20 to hit out of 20. Barring champion, your crit chance is 1/20. Obviously advantage or disadvantage change this.

[Laserlight]

I know how to calculate average damage on a hit, that's easy enough. For damage on a hit with, say, a polearm, 20 Str, and Booming Blade, I'd plug "=5.5+5+(4.5x3)" into Excel. But say I'm trying to calculate average damage based on accuracy vs a given AC, including crit damage. What equation would I plug into Excel to do that (for any attack, not just that example?)

[Crit Damage] CritChance * (2*DmgDie+DmgStat) +
[Normal To Hit] ((21-AC+AttackBonus)/20 - CritChance) *
[Normal Damage] (DmgDie+DmgStat)

Let's say you have 10% crit chance and do 1d6+1 with +7 vs AC 15; that yields .1 * (2*3.5+1) + ((21-15+7)20-.1)*(3.5+1) = .1*8 + .55 * 4.5 = 3.275

I'd assign cell references to Crit Chance, Damage Die et cetera.

[CaptainSarathai]

Yeah, Contrast has it.

((20 + Hit Modifier) - AC) / 20 = chance to hit
(21 - Crit Result) / 20 = chance to Crit.

(Chance To Hit - Chance to Crit) x Average Damage
+
Chance to Crit x Average Crit Damage
=
Total damage, adjusted for accuracy.

So let's say you have a Champion Fighter (Crit19+), with Str16(+3 Hit/Dam) and a WarAxe, attacking an enemy with AC14:
((20+3)-14)/20 = 9/20
(21-19)/20 = 2/20

(9/20)x(6.5+3) = 4.275
(2/20)x(6.5+6.5+3) = 1.6
4.275+1.6 = 5.875 average damage vs AC14

[JAL_1138]

Thanks for the replies.

I have trouble keeping strings of numbers straight. They get jumbled into an unrecognizeable mess in my head. Can't do arithmetic, passed Calc 1 fine back in undergrad. I dunno how that works.

So, to make sure I've got it: for Booming Blade at level 20 (+5 proficiency, +5 Str, so +10 to hit and +5 dmg), with a polearm (d10), 20 Str, a crit on nat 20s only, vs AC18, I'd do:

=((((20+10-18)/20)*.05)*((5.5*2)+(4.5*6)+5)))+(((20+10-18)/20)*(5.5+(4.5*3)+5))

with a result of 15.69 damage.

Yes?

(I'm aware there are way more parentheses than needed since Excel uses order of operations automatically. They help me keep each part of the formula in the right place.)

[CaptainSarathai]

Yep! You got it

I made a character sheet on my old PC that could auto-fill and do all this by referencing cells rather than me typing in the numbers.
It wasnt as good as the ones out there with a class drop-down, so you still had to put in your Hit Die, put a 1 next to the skills/saves you were Proficient with, and put down the damage die of your attacks.
Took me forever, and didn't format from XP to Windows 10.

[Contrast]

At level 20 you have a prof of +6 (for a total modifier of +11 including the strength).

So to hit AC18 you need a roll a 7 or more. So you miss on a 6/20 meaning you hit on a 14/20. (21-AC+mod)

So (14/20-1/20) is your chance to hit without critting. (1/20) is your chance to crit.

You do one weapon attack die plus 5 (1d10+5, 10.5) and 3d8 thunder damage (3*4.5) on a normal attack. You do 2 weapon attack die plus 5 (2d10+5, 16) and 6d8 thunder damage (6*4.5) on a crit.

So ((14/20-1/20)*(10.5+(3*4.5))) is your chance to do a normal hit and damage and ((1/20)*(16+(6*4.5)) is you chance to do a crit and crit damage.

Add them together ((14/20-1/20)*(10.5+(3*4.5)))+((1/20)*(16+(6*4.5))) = 17.75

Edit - got my maths wrong!

Its also worth noting that the 21-AC+mod thing only works for your hit chance in normal circumstances. At the extreme ends of the scale it doesn't work as a 20 always hits and a 1 always misses. For instance, that gives your level 20 character over a 100% chance to hit an AC10 creature, whereas they actually have a 95% chance (19/20).

[JAL_1138]

Yep! You got it

I made a character sheet on my old PC that could auto-fill and do all this by referencing cells rather than me typing in the numbers.
It wasnt as good as the ones out there with a class drop-down, so you still had to put in your Hit Die, put a 1 next to the skills/saves you were Proficient with, and put down the damage die of your attacks.
Took me forever, and didn't format from XP to Windows 10.

Cool, thanks for the help! I'll be reworking the formula to reference cells, so that I can compare some build ideas I've got, but had to do it longhand to make sure I got it right first.

[JAL_1138]

At level 20 you have a prof of +6 (for a total modifier of +11 including the strength).

So to hit AC18 you need a roll a 7 or more. So you miss on a 6/20 meaning you hit on a 14/20. (21-AC+mod)

So (14/20-1/20) is your chance to hit without critting. (1/20) is your chance to hit with critting.

You do one weapon attack die plus 5 (1d10+5, 10.5) and 3d8 thunder damage (3*4.5) on a normal attack. You do 2 weapon attack die plus 5 (2d10+5, 16) and 6d8 thunder damage (6*4.5) on a crit.

So ((14/20-1/20)*(10.5+(3*4.5))) is your chance to do a normal hit and damage and ((1/20)*(16+(6*4.5)) is you chance to do a crit and crit damage.

Add them together ((14/20-1/20)*(10.5+(3*4.5)))+((1/20)*(16+(6*4.5))) = 17.75

Edit - got my maths wrong!

Its also worth noting that the 21-AC+mod thing only works for your hit chance in normal circumstances. At the extreme ends of the scale it doesn't work as a 20 always hits and a 1 always misses. For instance, that gives your level 20 character over a 100% chance to hit an AC10 creature, whereas they actually have a 95% chance (19/20).

Oh dear. I've borked something else (apart from derping on the proficiency bonus), then, because when I change 10 to 11 in my formula I get 16.9975.

[Contrast]

Your crit chance is always 1/20 (barring champion) - you've added in some modifications you didn't need to.

Also its 21-AC+mod (or 21+mod-AC if you prefer) not 20 as CaptainSarathai indicated (as you hit when you meet their AC, not just beat it).

I think that resolves your issue.

Edit -

Your forumla:

=((((20+10-18)/20)*.05)*((5.5*2)+(4.5*6)+5)))+(((201+10-18)/20)*(5.5+(4.5*3)+5))

[JAL_1138]

Your crit chance is always 1/20 (barring champion) - you've added in some modifications you didn't need to.

I have the 1/20 in there as 0.05. What do I have in there that's unnecessary, and where?

Edit: Ninja'd by edit, but I don't know what I'm doing to start with.




Also its 21-AC+mod (or 21+mod-AC if you prefer) not 20 as CaptainSarathai indicated (as you hit when you meet their AC, not just beat it).

I think that resolves your issue.

Something's still off, because I get 18.305 after changing the 10 to 11 and the 20-AC+ToHit to 21-AC+ToHit.

EDIT: And it becomes 18.95 after scrapping the first instance of (21-18+11)/20.

[Giant2005]

Just make a copy of this (https://docs.google.com/spreadsheets/d/1WeLU9NWQp0aiLs5seE8t_4MlmEPYYP_VNtUP1vj2iAk/edit?usp=sharing) and go nuts.
Although I'm not sure if I would use the bonus attack calculation on there. It has been ages since I used it so I may be mis-remembering, but I think there is an issue with that part.

[JAL_1138]

Just make a copy of this (https://docs.google.com/spreadsheets/d/1WeLU9NWQp0aiLs5seE8t_4MlmEPYYP_VNtUP1vj2iAk/edit?usp=sharing) and go nuts.
Although I'm not sure if I would use the bonus attack calculation on there. It has been ages since I used it so I may be mis-remembering, but I think there is an issue with that part.

I really need to do it myself to understand what I'm tweaking, how it's changing, and why. And put it in a format with a layout I find immediately intuitive. I can't really parse that one well from first glance. I don't know how they're using certain terms, where they're getting certain things, etc. All I need is to get the formula correct once and then I can make something out of it that I can grok immediately and use fine.


EDIT: Current Excel formula is:

(Lvl 20, polearm, BB, +5 Str)

=(0.05*(5+(5.5*2)+(4.5*6)))+(((21+11-18)/20)*(5.5+5+(4.5*3))

Result is 18.95.

[Contrast]

Sorry thats my bad I'm an idiot - in using 20 rather than 21 Captain was simply removing the step where you had to subtract the crit chance! *facepalm*

So in other words:

=(0.05*(5+(5.5*2)+(4.5*6)))+(((20+11-18)/20)*(5.5+5+(4.5*3))

or

=(0.05*(5+(5.5*2)+(4.5*6)))+(((21+11-18)/20-(0.05))*(5.5+5+(4.5*3)))

Sorry, thats what happens when I try to do math while watching youtube videos :smallbiggrin:

[JAL_1138]

Sorry thats my bad I'm an idiot - in using 20 rather than 21 Captain was simply removing the step where you had to subtract the crit chance! *facepalm*

So in other words:

=(0.05*(5+(5.5*2)+(4.5*6)))+(((20+11-18)/20)*(5.5+5+(4.5*3))

or

=(0.05*(5+(5.5*2)+(4.5*6)))+(((21+11-18)/20-(0.05))*(5.5+5+(4.5*3)))

Sorry, thats what happens when I try to do math while watching youtube videos :smallbiggrin:


No worries, and thanks for the clarification, think I've got it now! :smallsmile: