Latest Diablo 3 News
DiabloWiki Updates
Support the site! Become a Diablo: IncGamers PAL - Remove ads and more!

Merc Damage Spreadsheets for Various Setups

Discussion in 'Single Player Forum' started by Owlie, Aug 16, 2018.

  1. Owlie

    Owlie IncGamers Member

    Joined:
    Jul 17, 2012
    Messages:
    257
    Likes Received:
    187
    Trophy Points:
    71
    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.
     
    Last edited: Aug 18, 2018
  2. Albatross

    Albatross IncGamers Member

    Joined:
    Apr 27, 2009
    Messages:
    926
    Likes Received:
    527
    Trophy Points:
    175
    Thanks for putting this together! The numbers are what I'd expect with the base hitting a certain attack breakpoint, though can't say much about the specifics.

    Giant threshers are crying in the corner for not being included :) They are basically an upgrade to threshers having the same breakpoints in this instance, but 0.5 higher average damage.

    When I'm done with work, I'll put in some more time into this and try my current setup and the setup I'd like to use once a get a better insight base. From the spreadsheet, it's clear that fortitude wins the DPS race, but I'm also interested in "bursts" of damage over 1, 1.5, and 2s spans. This is usually how long it takes for the merc to dispose of a cold immune in the AT. I expect it to be a race between fortitude's higher DPS and the chance for an additional attack to trigger a critical or DS when using treachery.

    "Will attempt to math" on this and report back with my findings.
     
  3. Baltha

    Baltha IncGamers Member

    Joined:
    May 22, 2008
    Messages:
    997
    Likes Received:
    337
    Trophy Points:
    197
  4. ffs

    ffs IncGamers Member

    Joined:
    Feb 7, 2017
    Messages:
    1,446
    Likes Received:
    2,676
    Trophy Points:
    188
    Maybe a small suggestion from my end (though it doesn't apply to many builds) would be to factor in Fanaticism. It can shuffle things around quite a bit depending on setup. I visited this thread recently to check Merc damage for my untwinked 99er Necro, and it was very helpful in particular because it includes Fanaticism BPs (as I was indeed testing runs with Beast).
     
  5. Owlie

    Owlie IncGamers Member

    Joined:
    Jul 17, 2012
    Messages:
    257
    Likes Received:
    187
    Trophy Points:
    71
    Updated the OP significantly spurred by the suggestions above.

    Could I ask a mod to change the thread name to "Merc Damage Spreadsheets for Various Setups"? The current one has become even less appropriate after the update.

    First I added GT and Fanaticism, then Reaper's. But, to obtain meaningful results for Reaper's, one should introduce resistances. But then, for comparison, I had to do the same for Insight and Infinity, leading to much more work than I anticipated.

    GTs added :)

    I'm quite tired at the moment, but it seems to me the spreadsheet takes all of that into account. The aDPS field takes into account: chance to hit, Jab/Normal frequency, DS and phys resistance and spits out the average damage per second. I'm not sure how you would obtain what you're asking differently.

    Thanks for linking it, I will look it over when I get the chance. My spreadsheets give a different conclusion (they might be wrong!), but one that is pretty dependent on the details. Also, see point 5) in OP.

    You might be happy with the update :)

    I am aware of that thread. IIRC, there was some difference between those tables and the german calculator (might be just the difference in FPA vs frequency I already discussed here).
     
    Last edited: Aug 16, 2018
    ffs likes this.
  6. Yng

    Yng IncGamers Member

    Joined:
    Sep 29, 2017
    Messages:
    502
    Likes Received:
    756
    Trophy Points:
    173
    Curious how things change without 35 IAS from the Helmet slot (this is very beneficial I’d imagine to the slower harder-hitting bases) and different setups in general. In particular setups wanting Delirium, Steelshade, or CoH options. For a lot of instances Cham might outperform the IAS jewel too considering multiple sources of cold slow application. Didn’t have a lot of time to really look into your links, just browsed the quick access google docs. I’ll look into these more when I have time, thanks!
     
  7. Albatross

    Albatross IncGamers Member

    Joined:
    Apr 27, 2009
    Messages:
    926
    Likes Received:
    527
    Trophy Points:
    175
    It made sense when I woke up in the morning, I swear :D You're correct, to get what I want all I need to do is multiple aDPS with 1.5, 2, etc. as you took everything into account.

    Studied Baltha's source in more detail this time. I'd say owlie's is more accurate thanks to jab/normal attack alternation and +skills effect on might (ED) and insight's CS %. What's different is the part with the merc being decreped (or under holy freeze), and this is where a treachery setup seems to pull ahead, but only under longer period of slow. Which is obviously important for baal (and CS to some extent) runners.

    And the reason trechery wil output more damage under decrep is not (only) because of the extra IAS, but because of fade which will reduce the duration of the curse by nearly 80%.

    The reaper's segment also shows fortitude winning, unless the merc is under decrep or HF and buffed by concentration. At least that's my interpretation of the data.

    Reaper's data:
    Thanks to owlie and Baltha, it looks like I'll be dusting of that fortitude and bringing it back to AT.
     
    Last edited: Aug 16, 2018
  8. ioupainmax

    ioupainmax IncGamers Member

    Joined:
    Jul 12, 2003
    Messages:
    2,075
    Likes Received:
    830
    Trophy Points:
    236
    Great work @Owlie, I haven't looked in detail yet but did you factor in Critical Strike from Insight?
     
  9. Albatross

    Albatross IncGamers Member

    Joined:
    Apr 27, 2009
    Messages:
    926
    Likes Received:
    527
    Trophy Points:
    175
    @Yng it will depend on the weapon base and which breakpoint can be reached, but a GPA is usually ahead of the competition.

    Edit: Added the rest of the breakpoints.

    Code:
    Colossus Voulge         Thresher                 Cryptic Axe              Great Poleaxe           Giant Thresher
    91                      76.5                     91.5                     86.5                    77
    IAS   APS     DPS       IAS   APS     DPS        IAS  APS     DPS         IAS  APS     DPS        IAS   APS     DPS
    0     2.77    252.07    0     3.33    254.745    0    2.77    253.455     0    3.12    269.**     0     3.33    256.41
    5     2.94    267.54    8     3.57    273.105    5    2.94    269.01      9    3.33    288.045    8     3.57    274.89
    11    3.12    283.92    22    3.84    293.76    11    3.12    285.48     20    3.57    308.805    22    3.84    295.68
    22    3.33    303.03    42    4.16    318.24    22    3.33    304.695    37    3.84    332.16     42    4.16    320.32
    35    3.57    324.87    75    4.54    347.31    35    3.57    326.655    63    4.16    359.84     75    4.54    349.58
    56    3.84    349.44    142   5       382.5     56    3.84    351.36    105    4.54    392.71     142   5       385
    89    4.16    378.56                            89    4.16    380.64    200    5       432.5    
    147   4.54    413.14                            147   4.54    392.71
    292   5       455                               292   5       457.5
    @ioupainmax sure did, even included +2 from andariel's and +1 for BC.
     
    Last edited: Aug 17, 2018
    GalaXyHaXz and ioupainmax like this.
  10. GalaXyHaXz

    GalaXyHaXz IncGamers Member

    Joined:
    Mar 16, 2011
    Messages:
    761
    Likes Received:
    653
    Trophy Points:
    173
    @Albatross More people need to see that chart. Pretty much anyone I ever played with on battle.net tries to say the Colossus Voulge has the highest DPS, but they overlook the minimum. I usually went with a Fortitude merc and Cryptic Axe, to strike a good balance between less IAS vs. good DPS.
     
  11. Thyiad

    Thyiad Moderator Single Player, D2 Assassin, Barbarian

    Joined:
    Mar 3, 2006
    Messages:
    15,022
    Likes Received:
    238
    Trophy Points:
    488
    And this should be the sub-heading for the SPF.
     
  12. ffs

    ffs IncGamers Member

    Joined:
    Feb 7, 2017
    Messages:
    1,446
    Likes Received:
    2,676
    Trophy Points:
    188
    @Albatross That chart doesn't seem to account for Jab, which is what the Merc uses Jab 85% of the time and it changes how weapons compare. Quoting the crucial table from the thread linked above (no Fanaticism):
    Code:
          10WSM           0WSM            -10WSM          winners
    IAS   Jab  Reg  Avg   Jab  Reg  Avg   Jab  Reg  Avg   1st 2nd
    0     286  135  263   309  144  284   296  137  272   GPA GT
    10    305  143  281   333  154  306   321  148  295   GPA GT
    15    327  153  301                                   GPA CA
    20                    360  166  331   321  160  297   GPA CA
    25    352  163  324                   350  160  322   GPA CA
    30                    360  180  333   350  175  324   GPA tie
    35    381  176  350                                   CA  GPA
    40                    393  180  361                   GPA CA
    45                                    385  175  354   GPA GT
    50    381  191  353   393  197  364                   GPA GT
    55                                    385  193  356   GPA GT
    60    416  191  382                                   CA  GPA
    65                    433  197  398                   GPA CA
    70    416  208  385                                   GPA CA
    75                    433  216  400   428  193  393   GPA GT
    90    458  208  421                   428  214  396   CA  GPA
    105   458  229  424   481  216  441                   GPA CA
    125                   481  240  445                   GPA CA
    145                                   481  214  441   GPA GT
    150   508  229  466                                   CA  GPA
    175   508  254  470                                   CA  GPA
    200                   541  240  496                   GPA CA
    Conclusion: Cryptic Axe wins at 35, 60, 90-100, and 150-195 IAS. Greater Poleaxe wins at all other IAS.
    Personally, I'm always looking exactly for the information contained above. Exact damage numbers only matter so much, and of course Fortitude + Andy's is the strongest option in most cases (i.e. when looking for raw damage). Though the spreadsheet is nice in allowing to see the differences exactly.

    The question of which is the ideal base IMO depends on other considerations. As @Yng says there are intangibles depending on what you're trying to achieve that you can't really calculate in a general way. Like faster aura activation, CB application, things like CtC from Delirium. Thresher/GT are better for that of course. CNBF can be better for lvl 85 area farming when hitting many bosses since he gets chilled often, which again depends on boss pack density and is different for everyone. (Higher boss number = higher chance of CE, from that number you'd need to calculate the average percentage of the run during which he's slowed, how that lowers damage, and compare that to the overall loss of not using an IAS/ED jewel.) Guillaume's Face is another strong option with its CB and DS that change things again, in particular for Reaper setups. Then CB in general is based on monster life and varies in usefulness depending on the target, e.g. it has quite an impact for a Baal runner but barely any for P1 MF runs.

    So, depends on what you want to do exactly. But IMO differences between bases are small anyway, I'm not too picky with them.
     
  13. Albatross

    Albatross IncGamers Member

    Joined:
    Apr 27, 2009
    Messages:
    926
    Likes Received:
    527
    Trophy Points:
    175
    The table I put together uses the weapon's base damage (averaged), but with jab breakpoints in mind.

    It's not as clear as the one you posted, but it also shows that CA is better at 35 and 60, which is basically because GPA needs 37 and 63 to reach the next BP, respectively. As noted, I took out the higher BPs because I've made that one for my setup.

    I agree that each case should be approached individually as there are many considerations to take into account. That's why it's great we still have all these spreadsheets (some new ones, too!) and calculators floating around.
     
  14. Baltha

    Baltha IncGamers Member

    Joined:
    May 22, 2008
    Messages:
    997
    Likes Received:
    337
    Trophy Points:
    197
    It has to be done.
     
  15. Owlie

    Owlie IncGamers Member

    Joined:
    Jul 17, 2012
    Messages:
    257
    Likes Received:
    187
    Trophy Points:
    71
    @maxicek, @Thyiad: can I ask for another small thread name change: "A2 Merc Damage Spreadsheet for Various Setups". Should be the last one, I hope.

    Significant update to the OP again. Abandoned multiple spreadsheets - the new one should be enough for many equipment choices. This, and Crushing Blow implementation, necessitated a lot of new input fields. This, on the other hand, lead to a new value for comparison: average time needed to kill the specified monster.

    Additionally, IAS can now simply be changed to 12 values I thought were pertinent. IAS tables were generated from Best Darn Act 2 Merc IAS Table Period by
    1) FPJ --> FPJ/2 + 1
    2) FPA --> FPA + 2
    which reproduces the correct (?) attacks per second from the German calculator usually used as a reference point (for all but the one point in red font in the IAS table).

    I think you should now be able to "simulate" Obedience :)

    I am interested in these setups as well. The spreadsheet should now take into account all but slowing effects. The German speed calculator only offers the Decrep option, so I could generate those tables by hand. Is there a link to IAS tables under various slow applications? The trouble is that the "IAS" from skills is applied differently than the IAS from equipment, so you cannot simple add/subtract.

    I would really like to find a usable attack speed formula.

    Yep, working on various spreadsheets constantly reminds me of the "intangibles". The slowing effects remain a problem until I can at least find some IAS tables, see above. Faster aura activation should be workable. There's a quick aura activation calculation (chance to activate after # seconds) on the Stats sheet, but I haven't yet thought through what to do about it. DS and CS are implemented.

    I'm not quite satisfied with my implementation of CB damage (details available in OP). It essentially multiplies two averages: hits needed to kill (which is basically average dmg) and average number of hits in a second. A better option would be to have one weighted average, I believe. But, due to the mechanics of CB, I couldn't think of a better one. Any ideas?

    Having said that, I do think it's an alright calculation. I think it reproduces some common sense thinking, such as: the difference in CB usefulness depending on monster HP, faster application, etc.
     
    Last edited: Aug 18, 2018
    Albatross likes this.

Share This Page