Mathfinder help! (Excel spreadsheets or code for damage calculations)

[McTaff]

Anyone here a brilliant excel wizard (or sorcerer)? Code genius will also be highly viewed upon.

I set about wanting to know under what circumstances Power Attack was better than Weapon Focus, and what difference Weapon Spec makes, and how improving critical ranges and multipliers changes damage output, and how base weapons change. On several web forums I have seen people turning out some pretty epic tables, but never tin the scope of what I was hoping for (they are always specific examples and much of it is outdated)... basically I started wanting a giant Excel spreadsheet (or some sort of program) that would take the raw data such as:

My class(es) levels
My weapon(s)
My feat(s) that I am using

.. and calculate out on an "average" foe for an equal CR, CR +1 and CR +2 what my average expected hits per round/full attack are, my critical chances, my average damage range and so on.

I even mused how it would be beneficial to know how many feats a character could spend at that level, etc.

I got partway through entering some of the data and was going to ask an excel guru buddy to help. Unfortunately, he is unable to give me any help on account of some personal stuff of his own taking a lot of precedence, so I was reaching out to anyone who might have a great deal of expertise and might like to help out (read: do all the hard work because I am dumb) with something like that.

Even better, if anyone is into App development, this could be a pretty radical little app for any tablet device.

Takers?

[Blueluck]

I whipped this up a few months ago for similar reasons. It's not brilliant or genius, but it gets the job done.
Google Spreadsheet

[McTaff]

Blueluck wrote:

I whipped this up a few months ago for similar reasons. It's not brilliant or genius, but it gets the job done.

Google Spreadsheet

That is quite good, and covers exactly what I needed.

Thanks!

[Axl]

Blueluck, the critical-based damage is too high. You need to multiply by (G2-1), not G2, in order to add the extra damage from the critical hit.

[Blueluck]

Axl wrote:

Blueluck, the critical-based damage is too high. You need to multiply by (G2-1), not G2, in order to add the extra damage from the critical hit.

Fixed. Thanks for spotting that for me!

[Scott Andrews]

Dotted.

[Knight Magenta]

Its actually pretty simple to whip up your own Spreadsheet for whatever math-fu you need. It can actually be fun once you get the hang of it.

The basic building block is: <Average Damage> * <probability of damage occurring>.

Why average damage and not max or min?:

Over time, as you make more attacks, even if you sometimes roll high or low, the total damage dealt divided by the number of attack will tend to be close to the average.

That's it. Just one multiplication.

You want to calculate damage from an attack (not counting crits), all you need to calculate is your max damage and your chance to hit.

For this example, lets use Hank the Tank. Hank is a level 1 fighter who wields a greatsword and has a +2 strength bonus. Hank's attack routine is: +3 2d6+3. At CR 1, average AC is 11.

We know Hank needs to roll an 8, that is a 13 in 20 chance.

So to calculate base damage, we take Hank's average damage:

(3.5 + 3.5 + 3 = 10)

and multiply it by his chance to hit:

(13/20)

To get his damage per round, we multiply the two numbers to get:

10 * (13/20) = 6.5.

But we are not done. We want to calculate damage from crits. We use the same formula since crits. We can treat them as a seperate damage source:

<Average Damage> * <probability of damage occurring>

We know crits do double damage, so average extra damage for Hank's greatsword is 10. The only slightly different part is the probability of it happening. We know a crit will happen 1 in 10 attacks with a greatsword. We also know that you need to confirm the crit by rolling against AC. When you know both rolls need to succeed, you can just multiply the probabilities together.

Why do we only add 10 damage from the crit and not 20?:

The great sword does x2 damage. However, one X-worth of that damage is covered by the regular hit. The crit is only adding one "X." If Hank used an earth-breaker, his average damage from the crit would be 20, since an earth-breaker is a x3 crit weapon.

So Hank's chance of landing a crit is:

(1/10)*(13/20)

Putting it together, average damage from hank's crits is:

20 * (1/10) * (13/20) = 0.65

Now you can sum the crit damage with the normal damage to get 7.15 DPR. The whole formula looks like this:

<Hit chance> * <damage> + <damage> * <hit chance> * <crit chance>

Once you understand how this works, you can do interesting things. For example, you can calculate the value of extra to-hit and damage just by playing with the numbers. You can add attacks just by adding more groups of <hit chance> * <damage>. And so on.

----------------------------

I hope this helped someone. Remember, math is your friend :)

[Axl]

There is another minor error. The threat range doesn't take account of situations where the chance of hitting is less than the threat range. Personally, I would separate the critical damage from the base damage into its own column. The critical damage would be:-

=IF(C2>F2,(F2*C2*(G2-1)*D2),(C2*C2*(G2-1)*D2))

[wraithstrike]

Here is another spreadsheet. It was used in the DPR olympics and is pretty accurate.

[McTaff]

Wholly sheet!

That's pretty much what I was hoping to arrive at eventually. That's an amazing amount of detail.

Thanks to everyone who has weighed in with some Math-jitsu - you're all awesome.

[Blueluck]

wraithstrike wrote:

Here is another spreadsheet. It was used in the DPR olympics and is pretty accurate.

Awesome! That's much better than my back-of-a-napkin version with errors!