Damage and Survivability Spreadsheet

  • #1
    This spreadsheet v6 is mainly designed for the barbarian and monk for calculating their defensive and damage characteristics, however the core damage components will work for any class.It can be used to compare items on the auction house and also to test relative stat values.

    I am hoping for some feedback to fine tune some of its aspects, if you are familiar with game mechanics and notice something that could be improved please let me know. I am also open to suggested additions if you would like to see something that isn't there. Also, some of the headings and names of fields feel like they could be improved, if you have a more concise way of saying some of these please let me know.

    Additional Information to help you get started:
    • Current column is for inserting all of your character information. This includes stats that are being gained by passives and followers, but not actives. It is assumed that you shouldn't have to change any of your skills/passives, or to activate any abilities when inserting your stats. The passive options on the right hand side will adjust your numbers down to their base value before calculating changes (if you have the passives turned on), further the active abilities you have turned on are included in the damage reduction formulas and are based on your "Unbuffed" stats.
    • Current Equipped Item and Possible Switch columns are for comparing items that you currently have equipped and might be thinking of switching.
    • The Change column and New Total columns will adjust based on the item stats you input into the item comparison cells.
    Options on the right hand of the screen:
    • Change your Main Damage Stat between Strength, Dexterity, and Intelligence (or Str, Dex, Int)
    • Enemy Level: used for armour and resist calculations
    • Life on Hit Coefficient should be set to your primary ability. JarthMader's Post on the official forums shows the following table which gives all of the barbarian life on hit coefficients.
    • Expected time to live without heals – Health Regeneration Multiple: This is the time in combat your character can last without the use of health globes or healing abilities. The Health Regeneration number in the damage reduction section is calculated as your average heals per second multiplied by the number you put in this section. This will calculate the extra health you gain from health regenerating items before death and allow you to compare them with vitality and % health to resistances, armor and dodge. The expected time to live should be adjusted as you change acts or significantly change gear to continue to sim properly against the other stats.
    • Class Options: Allow you to add passives and actives from barbarians and monks for defensive calculations, 1 = you are using this ability, 0 = you are not using this ability
    FAQ:
    Q: Is attack speed on weapons any different from normal attack speed?
    A: Attack speed on weapons is different then attack speed on armor. Attack speed = Weapon base attack speed * Weapon "increases attack speed by" * "Attack Speed Increases" on armor. You could leave the "increases attack speed by" column as 0, and input the weapons tooltip attack speed as its base but you will come out with a less accurate dps figure.The problem is most weapons base attack speed is 1.20, 1.30, or something else decently rounded, but when you get an increases attack speed by 11% it multiplies to 1.332, 1.443 but the game shows only 1.33, 1.44 while still using 1.332 and 1.443 to calculate your damage. So, depending on how much that matters to you, you can do either method.

    Q: What is the difference between the Current Column and the Current Equipped Item Column?
    A: The current equipped item column is for comparing 1 item to a possible switch item, or combinations, example sword and shield to two one handers or a 2 hander. Basically the current equipped column is what you will be unequipping when you equip your possible switch item.The current equipped for item 1 is whited out, because if you are replacing your main hand weapon it automatically assumes that you are unequipping your main hand weapon.

    Q: Why is my in game resist / armor % different from that shown on the spreadsheet?
    A: The spreadsheet can be adjusted for enemy level (found with the other options). If you change this value to 60, you will come to the same % as in game.

    Q: Why aren't there Templar's health regeneration and Scoundrel's crit chance options?
    A: These are flat increases to your character sheet, and if you choose to use these abilities, simply input your stats including your followers, no option is needed.

    Version 6 Update - June 21 2012
    • Added Character level option
    • Macro support for clearing / equipping items
    Version 5 Update - June 18 2012
    • Reworked health regeneration comparison
    Version 4 Update - June 12 2012
    • Relative Stat table added to main page
    Version 3 Update - June 11 2012
    • Marginal Stat Increase table added on an additional tab
    Version 2 update - June 07 2012
    • Deadly Reach + Keen Eye (50% armor increase)
    • Crippling Wave + Concussion (reduced damage of enemies hit by 20%)
    • Resolve passive (reduced damage of enemies hit by 25%)
    • Threatening Shout + Falter (20% damage, 5% attack Speed [15% over 5 seconds, 15sec CD])
    • Fists of Thunder - Lightning Flash (16% Dodge)
    • Added enchantress follower options
    • Added Percent comparison column to the options to evaluate relative worth of skills / passives.
    Version 1 Update - June 05 2012
    • Added fields for block amount, block chance, non physical resists, damage taken from elites
    • Added Passive option Superstition
  • #2
    I found a typo, can't count atm but
    "Nerves of Steal" should be "Nerves of steel" :)

    I'll check it out more once I get home :D
    Quote from name="Bashiok" »

    End game solutions
    We recognize that the item hunt is just not enough for a long-term sustainable end-game. There are still tons of people playing every day and week, and playing a lot, but eventually they're going to run out of stuff to do (if they haven't already). Killing enemies and finding items is a lot of fun, and we think we have a lot of the systems surrounding that right, or at least on the right path with a few corrections and tweaks. But honestly Diablo III is not World of Warcraft.
  • #3
    I'm having some problems understanding the results of "Resistance", "Health Regeneration" and "Total Damage Taken" in the damage reduction section.

    Would you be so kind as to explain those?

    Other than that, it's very informative and a great tool, thanks!
  • #4
    The results of resistances (and armor) take into account the enemy level option on the right hand side of the page, and any passives/actives that you have turned on. Impunity and Time of Need will adjust resistances.

    Health regeneration is the percentage of your total health that comes from life on hit and health regeneration over time. Since it is calculated as a percentage of your total health it can easily be combined and compared with effects such as armor and dodge. It is a product of your life on hit (which takes into account the LoH coefficient, and your current attack speed) and passive health regeneration multiplied by the Expected time to live without heals - Health Regeneration Multiple number, you can adjust this option based on how highly you value health regenerating items, or for gearing for different encounters/difficulties.

    EDIT: It is implied that a value of 5 indicates that your character lives for an average of 5 seconds in combat without the use health globes/healing skills, however if you do a lot of kiting you may be inclined to increase this number further than your "toe-to-toe" survivability time.

    Total Damage taken is a combination of your armor, resistance, % melee damage reduction, dodge chance, and health regeneration. It is used to calculate your effective health so that comparisons can be made between the various mitigating stats.
  • #5
    Aah, thank you very much! :)
  • #6
    This looks nice!
  • #7
    Thanks! It makes my life easier by a lot.

    You don't seem to calculate shields and block, could you add that if I ask politely? :)
  • #8
    thanks! finally a spreadsheet for barbs :D.

    But something feels off:
    It seems to me, that I have to put the same information into C and F.
    Could you please tell me what the difference between these 2 rows is? :)
    Also "All Resist" seems to be missing.
    Thanks.
  • #9
    could you please add the 'reduces damage from elites' stat? I believe its quite a common (stormshield) stat that many barbarians use, and elites are the only monsters that pretty much matter anyway so I would love if the spreadsheet could show me how much -5% from elites actually does to my survivability.

    Also, what lvl are the monsters again in act 1-4? 60, 61, 62, 63?
  • #10
    am i the only one who couldn't make a comparison? can you explain how to do it?
  • #11
    Hi,

    Very good work on the spreadsheet. In case you are working on extending it here are my comments(plus all the comment above me) to keep a track of them:
    • Superstition Passive is missing
    • Is there a need for both "Current" and "Current Equipped"? I may not understood correct the logic behind it but isn't always our Current = Current Equipped
    • Resistance are missing (Fire, Poison, etc). Is there a need for them?
    • Block Chance is missing (and shields)
    • Reduce damage from elites is missing
    • Reduce damage from melee attacks is missing
    Keep up the good work :)

    mOOwalker
  • #12
    are others allowed to extend your work and post it here for you to merge?

    Is attack speed on weapons any different from normal attack speed? If no, then please merge the 3 attack speed rows into 1.

    Nice work :)
  • #13
    Okay maybe it's just me but i can't download it, all my toolbar options are grayed out.

    it there another way to download it?
  • #14



    I'm not actually worried about the legitimacy of your spreadsheet, I'm jsut curious what is causing this.
  • #15
    I merged the weapon "Current" and "Current equiped" rows.

    If I get the permission from the author I will upload it, when I get home (in 6h). I might even improve it more.
  • #16
    Viewing in simple list mode due to high traffic to this document. OK[/quote]

    Can you upload it somewhere please?.
  • #17
    Quote from efiloN

    Thanks! It makes my life easier by a lot.

    You don't seem to calculate shields and block, could you add that if I ask politely? :)


    I could add block % and block value to compare average block amount, but combining it with effective health would require an input field for "Average melee swing" or something along those lines... Yes, I'll definitely add the first half but i'm unsure if the second half will be worth the hassle.

    Quote from GNA

    thanks! finally a spreadsheet for barbs :D.

    But something feels off:
    It seems to me, that I have to put the same information into C and F.
    Could you please tell me what the difference between these 2 rows is? :)
    Also "All Resist" seems to be missing.
    Thanks.


    Column C you input all of your stats, column F you only input the stats that you want to compare. For example if you want to compare an amulet you would put your current amulet stats in column F and your new amulet stats in column G so that the spreadsheet can subtract out the gear you have on and simulate your total stats with the new item equipped.

    I'll add all resist shortly.

    Quote from Willemh

    could you please add the 'reduces damage from elites' stat? I believe its quite a common (stormshield) stat that many barbarians use, and elites are the only monsters that pretty much matter anyway so I would love if the spreadsheet could show me how much -5% from elites actually does to my survivability.

    Also, what lvl are the monsters again in act 1-4? 60, 61, 62, 63?


    Sure no problem.
    Act 1 - Level 61
    Act 2 - Level 62
    Act 3 - Level 63
    Act 4 - Level 63
    Quote from GNA

    are others allowed to extend your work and post it here for you to merge?

    Is attack speed on weapons any different from normal attack speed? If no, then please merge the 3 attack speed rows into 1.

    Nice work :)


    Yes, if you would like to adjust the formulas and let me know i'd be glad to merge them into the spreadsheet.

    Attack speed on weapons is different then attack speed on armor. Attack speed = Weapon base attack speed * Weapon "increases attack speed by" * "Attack Speed Increases" on armor. You could leave the "increases attack speed by" column as 0, and input the weapons tooltip attack speed as its base but you will come out with a less accurate dps figure.

    The problem is most weapons base attack speed is 1.20, 1.30, or something else decently rounded, but when you get an increases attack speed by 11% it multiplies to 1.332, 1.443 but the game shows only 1.33, 1.44 while still using 1.332 and 1.443 to calculate your damage. So, depending on how much that matters to you, you can do either method.

    Quote from mOOwalker

    Hi,

    Very good work on the spreadsheet. In case you are working on extending it here are my comments(plus all the comment above me) to keep a track of them:
    • Superstition Passive is missing
    • Is there a need for both "Current" and "Current Equipped"? I may not understood correct the logic behind it but isn't always our Current = Current Equipped
    • Resistance are missing (Fire, Poison, etc). Is there a need for them?
    • Block Chance is missing (and shields)
    • Reduce damage from elites is missing
    • Reduce damage from melee attacks is missing
    Keep up the good work :)

    mOOwalker


    I could add superstition and other non physical resists to come out with two damage reduction figures, one for physical and one for other types of damage if that would be helpful.

    Quote from thebouta

    Okay maybe it's just me but i can't download it, all my toolbar options are grayed out.

    it there another way to download it?


    I'll look into hosting it on an outside site this afternoon once I incorporate the above changes
    Quote from Xathian




    I'm not actually worried about the legitimacy of your spreadsheet, I'm jsut curious what is causing this.


    This is caused by the "Main stat" formula which uses a lookup function to see which stat you have set, I could look into other ways of setting it up, but as of now it works fine and this is the only inconvenience of it

    Quote from GNA

    I merged the weapon "Current" and "Current equiped" rows.

    If I get the permission from the author I will upload it, when I get home (in 6h). I might even improve it more.


    I'd love to have improvements shared, but as stated above, the current equipped is for comparing to possible upgrades and should be a separate column
  • #18
    Quote from dai007

    Viewing in simple list mode due to high traffic to this document. OK


    Can you upload it somewhere please?.


    I'm new to dropbox, but does this download link work?

    Edit: this is the "original version" not v1, I won't be updating this post
  • #19
    Quote from Voyle

    Quote from GNA

    I merged the weapon "Current" and "Current equiped" rows.

    If I get the permission from the author I will upload it, when I get home (in 6h). I might even improve it more.


    I'd love to have improvements shared, but as stated above, the current equipped is for comparing to possible upgrades and should be a separate column


    First, thank you for your time to answer. I have solved it in a way that by no means hinders you to compare items with your current equip:

    "Change" Row in Weapons Section:
    =IF((G3>0);G3-C3;0)


    With this its possible to compare gear with your currently equiped without the need to write down you current numbers again. Please note that this trick will only work for the weapons section.
  • #20
    If you keep updating it you should add something like version control. Either v1,v2,v3 or a date of release so you avoid any confusion that might come up.

    mOOwalker
  • To post a comment, please or register a new account.
Posts Quoted:
Reply
Clear All Quotes