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%)
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.
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?
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?
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.
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.
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?
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.
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.
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
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.
You could use my trick until Offhand - Top End Damage.
The color scheme for the "All Resist" row is missing.
It would also be nice to know how much life / sek "Life on Hit" is worth. I think it would work with "Attacks / Sek" * "Life per Hit" * "Life on Hit Coeffecient".
I don't know how the Life on Hit Coeffecient is calculated. Could you tell me?
I guess the issue with doing that is you could have a ring with 2-6 damage on it, and another with 50 intelligence and to compare the two you would need to put zeros in the damage component of the second ring which seems fairly easy to forget. Same with comparing a mainhand and offhand to a two hander, it would require putting in zeros to the offhand slot.
The all resist row is intentionally missing its colour scheme. It is intended as an input value for comparing items, but not as a value from your character sheet, the cumulative change in resist all will go to each of the resists and come to their new totals.
I can split out health regeneration in the damage reduction section to show both sources.
JarthMader's Post shows the following table which gives all of the barbarian life on hit coefficients.
I suppose you could add as options buffs from followers - enchantres 3% IAS & 15% armor, scoundrel 3% crit and templar 155 hp/s regen.
p.s. Great job
I'll have to do some testing on the enchantress attack speed, but then i'll add these in probably sometime tommorrow morning or tonight. From initially looking at the ability it isn't giving 3% attack speed, but something closer to 2.25%
I've got a couple of questions regarding the buffs.
Armor and resistance stats, am I supposed to write those with or without nerves of steal, tough as nails and impunity?
It seems to me that when I change nerves of steals and impunity me effective health (physical) rises from 463.511 to 474.708
How can that be? That seems illogical to me.
You are supposed to write down your stats including your passives but not including your actives, the actives are included in damage reduction, not the stats.
No idea what you mean by change, but often buffs improve your character so it makes sense to me...
How can I compare a 2H weapon vs dual wield ? And the % attack speed on a weapon is already counted in the base weapon attacks so nice fail there.
It does the comparison automatically for you if you have a two hander setup equipped and compare to a dual wield setup (or the opposite). Make sure if you currently have a dual wield setup to include your offhand in the current equipped item section.
Also it is the fact that the weapon speed increase is included in the tooltip that necessitates separating them for accurate calculations as stated:
Is attack speed on weapons any different from normal attack speed? If no, then please merge the 3 attack speed rows into 1.
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.
I like the file. I did notice that the dual wield calculation for damage is slightly off from what the Damage shows in game. I fixed the calculation on the file on my end. The Problem is you can't just multiply the damage of each weapon by their adjusted swing speed because when you attack, you have to alternate swings. So really, you take 1 sec / adjusted swing speed to get the timed delay between left hand, right hand, left hand, etc. So the calculation ends up being essentially
((MH+ armor damage)*(crit variable)*(skill damage)*(stat variable)+(OH+ armor damage)*(crit variable)*(skill damage)*(stat variable))/ (1/MH speed variable + 1/OH speed variable)/ (Constant).
Where the Constant is 1.00028 as i have discovred when playing several different items to test the formula.
Here is the exact formula that i changed it to for the column C calculation, so you can copy and paste and test
=IF(((C8+C9)>0),((((1+C32)*(SUM(C3:C4,C13:C14,C15:C16,C17:C18,C19:C20)/2)*(1+(C34*C35))*(1+(C31/100)))+((1+C32)*(SUM(C8:C9,C13:C14,C15:C16,C17:C18,C19:C20)/2)*(1+(C34*C35)))*(1+(C31/100)))/(1/(C7*((1+C21)+0.15))+1/(C12*((1+C21)+0.15))))/1.00028,(SUM(C3:C4,C13:C14,C15:C16,C17:C18,C19:C20)/2)*C33*(1+C32)*(1+(C34*C35))*(1+(C31/100)))
Also, I agree with someone before about Not needing the Increases Attack Speed By & Total Weapon Attack Speed rows since the attack speed is already figured into the value listed on an item.
@Khays- For test DW vs 2H. Insted of using the Change column (D), i copied the formulas in C30:C33 over to column G, and then in column G i filled in all the 2H data i need in the required rows.
Still learning how to use this table and had a few questions that would help a noob like me out. What is the purpose of having a "Current Equipped Item" column to compare with "Possible Switch"? Since we already have a spot for the "Current" item stats in the blue input column, isn't this a bit redundant to have "Current Equipped Item" as well? If there is a clear use for this can you please explain for a me, thanks.
Also the Current Equipped Item column for Weapon 1 is now whited out, isn't it supposed to be yellow?
Other abilities worth adding for Monks would include:
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%)
Still learning how to use this table and had a few questions that would help a noob like me out. What is the purpose of having a "Current Equipped Item" column to compare with "Possible Switch"? Since we already have a spot for the "Current" item stats in the blue input column, isn't this a bit redundant to have "Current Equipped Item" as well? If there is a clear use for this can you please explain for a me, thanks.
Also the Current Equipped Item column for Weapon 1 is now whited out, isn't it supposed to be yellow?
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 autimatically assumes that you are unequipping your main hand weapon.
Rollback Post to RevisionRollBack
To post a comment, please login or register a new account.
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:
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
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.
Also, what lvl are the monsters again in act 1-4? 60, 61, 62, 63?
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:
mOOwalker
it there another way to download it?
I'm not actually worried about the legitimacy of your spreadsheet, I'm jsut curious what is causing this.
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.
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.
Sure no problem.
Act 1 - Level 61
Act 2 - Level 62
Act 3 - Level 63
Act 4 - Level 63
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.
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.
I'll look into hosting it on an outside site this afternoon once I incorporate the above changes
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
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
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
mOOwalker
Edit: Posted version 1 implementing your suggestions, hyperlinks in the main post direct you to v1 now.
I guess the issue with doing that is you could have a ring with 2-6 damage on it, and another with 50 intelligence and to compare the two you would need to put zeros in the damage component of the second ring which seems fairly easy to forget. Same with comparing a mainhand and offhand to a two hander, it would require putting in zeros to the offhand slot.
The all resist row is intentionally missing its colour scheme. It is intended as an input value for comparing items, but not as a value from your character sheet, the cumulative change in resist all will go to each of the resists and come to their new totals.
I can split out health regeneration in the damage reduction section to show both sources.
JarthMader's Post shows the following table which gives all of the barbarian life on hit coefficients.
p.s. Great job
I'll have to do some testing on the enchantress attack speed, but then i'll add these in probably sometime tommorrow morning or tonight. From initially looking at the ability it isn't giving 3% attack speed, but something closer to 2.25%
You are supposed to write down your stats including your passives but not including your actives, the actives are included in damage reduction, not the stats.
No idea what you mean by change, but often buffs improve your character so it makes sense to me...
It does the comparison automatically for you if you have a two hander setup equipped and compare to a dual wield setup (or the opposite). Make sure if you currently have a dual wield setup to include your offhand in the current equipped item section.
Also it is the fact that the weapon speed increase is included in the tooltip that necessitates separating them for accurate calculations as stated:
I like the file. I did notice that the dual wield calculation for damage is slightly off from what the Damage shows in game. I fixed the calculation on the file on my end. The Problem is you can't just multiply the damage of each weapon by their adjusted swing speed because when you attack, you have to alternate swings. So really, you take 1 sec / adjusted swing speed to get the timed delay between left hand, right hand, left hand, etc. So the calculation ends up being essentially
((MH+ armor damage)*(crit variable)*(skill damage)*(stat variable)+(OH+ armor damage)*(crit variable)*(skill damage)*(stat variable))/ (1/MH speed variable + 1/OH speed variable)/ (Constant).
Where the Constant is 1.00028 as i have discovred when playing several different items to test the formula.
Here is the exact formula that i changed it to for the column C calculation, so you can copy and paste and test
=IF(((C8+C9)>0),((((1+C32)*(SUM(C3:C4,C13:C14,C15:C16,C17:C18,C19:C20)/2)*(1+(C34*C35))*(1+(C31/100)))+((1+C32)*(SUM(C8:C9,C13:C14,C15:C16,C17:C18,C19:C20)/2)*(1+(C34*C35)))*(1+(C31/100)))/(1/(C7*((1+C21)+0.15))+1/(C12*((1+C21)+0.15))))/1.00028,(SUM(C3:C4,C13:C14,C15:C16,C17:C18,C19:C20)/2)*C33*(1+C32)*(1+(C34*C35))*(1+(C31/100)))
Also, I agree with someone before about Not needing the Increases Attack Speed By & Total Weapon Attack Speed rows since the attack speed is already figured into the value listed on an item.
@Khays- For test DW vs 2H. Insted of using the Change column (D), i copied the formulas in C30:C33 over to column G, and then in column G i filled in all the 2H data i need in the required rows.
Also the Current Equipped Item column for Weapon 1 is now whited out, isn't it supposed to be yellow?
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%)
Other than that well done.
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 autimatically assumes that you are unequipping your main hand weapon.