Owlie's Merc damage spreadsheet

ffs

Well-known member
Feb 7, 2017
2,866
2,326
113
Originally posted by Owlie


________________


Here I present a spreadsheet containing detailed calculations for Act 2 Might Merc in various setups. The spreadsheet not only calculates the average damage per second, but also the average number of hits and seconds needed to kill an enemy. Additionally, it can be used for quickly comparing two different sets of equipment.

It seeks to simulate merc's physical damage output as faithfully as possible. Specifically, merc's base damage is taken into account, IAS, details of Jab, normal attack (and their frequencies), chance to hit, Deadly Strike, Critical Strike, Crushing Blow and enemy physical resistance for meaningful comparisons.

Link: Spreadsheet (usable online)

Note: The spreadsheet uses a JavaScript function for Crushing Blow calculations. This part of the spreadsheet probably only works on Google Spreadsheets, but all of the rest (DPS) should work anywhere. The input fields can be changed by anyone, while the rest is locked. I will supply the whole spreadsheet to those interested in fiddling further.

Usage

The spreadsheet consists of three sheets:

1) DMG - contains the only input fields (grey) and final outputs.
2) Stats - contains necessary intermediate data and calculations.
3) IAS - contains IAS tables.

To use the spreadsheet you only interact with the first sheet. For each setup, the darker grey input fields are used to set global variables: they set the same equipment and enemy details for all weapon bases so that they are compared on equal footing. The weapon bases considered are: Thresher, Great Poleaxe, Colossus Voulge, Cryptic Axe and Giant Thresher.

To change the base to another polearm, use the lighter grey input fields to change the base damage of a weapon with the same WSM.

Chance to hit is displayed on the right hand side of the second sheet.

Insights

1) The difference between Fortitude and Treachery setups is significant, even if one factors in the poison damage from Venom. For MFing runs, a more detailed comparison might include the average time needed to active Might with each setup as this is where Treachery has an advantage. I am not completely sure of the aura activation mechanics, but assuming the aura frequency works like any probability and using Thresher as the base weapon, the chance to activate Might after 5s is:

60.5% with Fortitude
66.6% with Treachery

With his current setup, my Pitbarb does less than 2k dmg more with Might activated than without, which is less than the difference between Fort and Treachery. (Whether merc will hit the same target as me is another question.)

2) Either way, the damage is somewhat comparable to the DPS of, say, a Pitbarb (around 25000+ DPS). This, of course, is not an unusual conclusion to anyone with some Pitbarb experience, but it is still kind of odd seeing it.

3) eth Reaper's beats eth Insight at 0% physical resistance. For greater resistances (nearly all monsters in Pit and AT have some, more in AT) the difference increases.

4) Fanaticism offers a significant boost.

The "Really Interested in Math" Part

The following JavaScript function is used to obtain hits needed to kill the monster specified.

Code:
/**
* Returns the average number of hits to reduce enemy HP to 0.
*
* @param {number} HP Enemy HP on /players1.
* @param {number} cb Chance of CB in %.
* @param {number} dmgJ Average dmg of a Jab hit.
* @param {number} dmgA Average dmg of a Att hit.
* @param {number} freqJ Jab frequency (decimal).
* @param {number} freqA Att frequency (decimal).
* @param {number} dmg Average DMG per attack.
* @param {number} p /players.
* @param {number} su Whether the monster is a Superunique.
* @param {number} boss Whether the monster is an Act Boss.
* @param {number} PR Total physical resistance for use in CB calculations.
* @customfunction
*/
function CrushBlow(HP,CB,dmgJ,dmgA,freqJ,freqA,p,su,boss,PR) {
  var pr = PR;
  if (PR > 99) {
    return "infty";
  }
  if (PR < 0) {
    pr = 0;
  }
  var cb;
  if (CB > 100) {
    cb = 100;
  } else {
    cb = CB;
  }
  var sub = Math.max(su,boss);
  var hp = HP*(1+(p-1)/2);
  var cbdmg;
  var hits = 0;
  while (hp > 0){
    cbdmg = 1/(4*(1+sub)*(0.5+0.5*p)) * hp *(1-(pr)/100);
    hp = hp - cb/100 * cbdmg - (freqA * dmgA + freqJ * dmgJ);
    if (hp == HP*(1+(p-1)/2)) {
      return "infty";
    }
    hits = hits + 1;
  }
  return hits;
}

The code should be readable regardless of one's JavaScript knowledge (I had none this morning). After some CB details, the main part is the while loop returning the number of hits needed to reduce an enemy's HP to zero. First line of note is:
Code:
cbdmg = 1/(4*(1+sub)*(0.5+0.5*p)) * hp *(1-(pr)/100)
which gives the CB dmg for a given monster hp at each step. It takes into account the reductions due to monster type (sub; SU/B suffer less CB dmg than other types of monsters of same HP), player count (p) and physical resistance (pr). The main line is
Code:
hp = hp - cb/100 * cbdmg - (freqA * dmgA + freqJ * dmgJ)
which applies the average (cb) CB dmg as well as a weighted (freqA, freqJ) average of Normal Attack (dmgA) and Jab (dmgJ) damages at each step (this is where average Jab and Normal Attack dmg plug into in the spreadsheet).

To obtain the seconds needed for the kill, the value of hits is divided by the average number of successful hits per second:
Code:
CtH_Att*freq_Att*(25/FPA)+CtH_Jab*freq_Jab*(25/FPJ)

Final Words

Thank you to @onderduiker (check out Rogue Double Damage Bug?) for his invaluable insights. The spreadsheet originated with nulio's and frozzzen's spreadsheet. The goal, for me, was to take into account as much details as I could find. Furthermore, I wanted the origin of the spreadsheet to be clear, so I kept the basic layout and the idea of step-by-step calculations. I hope that @nulio and @frozzzen don't mind. Thank you to them and the various SPFers offering useful suggestions.

All comments/suggestions/corrections very welcome! With this much formulae and so little to check them against, mistakes are quite possible, so take this all with a big chunk of salt. I don't expect anyone to go into the details of the formulas, but please report any inconsistencies you come across.[/QUOTE]
 
Diablo 4 Interactive Map
PurePremium
Estimated market value
Low
High