Wanted to share with you guys a tool I've created in order to simplify high-end gear comparison and making it easier to make fully aware purchase decisions on the AH.
Which is extremely important to know because there is nothing better than buying a ring for a lot of gold just to see that it actually gives you only 300 more DPS, when you thought it would give you a boost of 2000.
Basically it is an Excel spreadsheet with 3 functions.
1) Gear comparison.
2) Specific stat change influence on your build damage.
3) Absorption calculation.
Number 1 is achieved by data input in the green marked areas on the left side for both variants, which should give you the picture of the final stat of the builds and damage difference.
Number 2 can be done via the little table below by typing in changes such as (-100 Dex, + 15% IAS, + 30% crit dmg).. The result is the Final DPS change (without sharpshooter maxed).
Number 3 is done be proper entry of gear data to the 3-rd sheet, which allows to see your current mitigation versus various damage types and sources.
Information about the spreadsheet - Read before using:
1) I assume you use a ranged weapon.. Naturally a DH can run around with a spear but..
2) Sheet is protected so You don't accidentally mess it up. The only cells that You should write up data in order for the sheet to work are marked with a green color.
3) IAS = Increased Attack Speed, for those that are not familiar.
4) The sheet is filled in with some entry data so You can see how it is supposed to look like.
5) Note that the little table below is a simplified model that should rather be used for 1-variable change testing, it will only give approximated data when used for multiple variable changes. It also serves to calculate weights of specific stats, which show the impact of "change of 1" of specific stat to Your DPS. Mind that this table is dependant on the gear figures of Variant 1 only.
6) It is Vital to select from the scroll-down list the appropriate Weapon Type for both Variants since the base weapon speed and the archery passive bonus are derived from the weapon type.
7) The file consists of 4 sheets. The first sheet is devoted to single-wielding style, the second for more accurate weapon min/max dmg calculation, the third to dual-wielding style, the 4th for the purpose of absorption calculation.
8) The spreadsheet might not be 100% precise in cases of weapons with +XX% damage affix, because of the rounding error, since the spreadsheet assumes whole numbers, which might not be the case.
9) The dodge calculation is an estimate, so it might not be 100% accurate. (But works fine for tested sets of equipment).
If you have any questions concerning the calculations/spreadsheet feel free to write them down.
Q1) Is there a way to disable sharpshooter, because it inflates the value of Crit DMG and underestimates Crit chance?
A1) There are 2 figures representing Your final DPS: 1) "Final DPS", 2) "Final DPS with sharpshooter maxed". If you are interested in your base dps without the use of sharpshooter passive or without any bonus crit chance from it, refer to the figure in 1). Since 1.2 You can also disable sharpshooter in the passive section.
Q2) How should i input data from gear with for example +13-26 damage from an amulet?
A2) By typing in both 13 in "Bonus MIN DMG" and 26 in "Bonus MAX DMG" in the row devoted to the gear of one of the variants.
Q3) I have input all the data as required but i keep getting "###" instead of the dps, what's wrong?
A3) "###" in Excel means that the column is too narrow to present the figure. Widen it and It'll be fine.
Q4) Where should i input the +X% damage from my weapon?
A4) You should not enter this anywhere, because it is already taken into account in the weapon minimum and maximum damage that you should enter to the spreadsheet.
Q5) Where should i input the + X-Y Damage from my weapon?
A5) You should not enter this anywhere, because it is already taken into account in the weapon minimum and maximum damage that you should enter to the spreadsheet.
Q6) Your spreadsheet says that the crossbow has a base attack speed of 1.1, but my one has 1.23, what should i do?
A6) Your weapon has an attack speed of 1.23 beacuse it has IAS on it. The base weapon speed is still 1.1, but due to the bonus it is enhanced to 1.23. Therefore you should enter the IAS bonus from your weapon to the row of weapon statistics in the AIS column.
Q7) What does "Assume Dodge as damage reduction" mean?
A7) Dodge itself does not reduce the damage taken but gives a chance to completly avoid the damage, but in order to reflect its importance in damage reduction this option has been added. It gives you a chance to simulate how much damage would you take in case of being attacked by an infinitely large amount of attacks dealing infinitely small damage, in which case dodge acts simply as damage reduction instead of chance to avoid damage.
Q8) The spreadsheet does not consider crit chance when i input it with a dot (like 3.5%) What's wrong? - (Rare occurrence)
A8) Try using a comma (like 3,5%). It might occur when using a different software that does not automatically adapt into various regional settings. The spreadsheet has been written using Polish settings in MS Excel where it is accustomed to use (,) as a decimal separator.
Q9) What's the purpose of "Base Monster Damage" and "Monster Hit Damage" in the absorption sheet?
A9) If You are aware of the damage a monster would afflict attacking player with 0 armor and 0 damage reduction (Such information might also be obtainable from some sites or the Bradygames guide) you can input it as "Base Monster Damage" and it will make You see in the appropriate "Monster Hit Damage" cell the actual damage you will receive considering Your damage reduction from armor and resistances - Mind that if You want to check for 1-hit damage, You should turn off the Dodge damage reduction in order to avoid understating the damage redeived.
Q10) My DPS in game does not agree to the sheet, help!
A10) I used to help you out, but due to too many user-made errors i gave that up, instead here's a list of what you can do:
1) Check whether your DEX,Crit dmg, crit chance, attacl speed agree sheet - game (remember some legendaries have IAS bugged)
2) Check whether you've input your weapon properly - selected weapon type
3) Check passives and follower buffs
4) Check all +MIN/MAX dmg bonusses from jewelery
5) If you still have an error, mind that some +MIN/MAX dmg are "bugged" giving a different amount than it says they do, therefore remove those items and check if it agrees, if it doesn't go back to 1), if it does, gradually equip those items until you know whats the issue item.
HOW TO USE +X% ELEMENTAL DMG
I will write here what and where You should enter in order to have DPS calculated properly using an item that gives You +X% Elemental damage.
Let us assume that You have Inna's Blessing that gives 5% holy damage and the example weapon linked below.
Example item with +X% Elemental dmg:
1) Type in weapon stats:
Select type of weapon : Crossbow
801 in MIN DMG (C9)
1241 in MAX DMG (D9)
Those are the final MIN/MAX dmg dealt by the weapon
Type in other additional stats such as IAS/CRIT DMG in E9 and F9 accordingly.
2) Type in the Elemental damage dealt by the weapon separately
231 as MIN Elemental DMG (C26)
554 as MAX elemental DMG (D26)
3) Type in the +X% Elemental dmg
5% in B26
Current version of the spreadsheet: 1.94
- Adjusted skill dmg to reflect patch 1.08 changes.
- Fixed the Single-stat change for All Res in the absorption tab.
- Added Boar Rune for Companion.
- Fixed Perfectionist Passive.
- Changed the default number format to % for reduced dmg from elites.
- Some tweaks to EHP stat-weights (due to changes to perfectionist and boar).
- Fixed an issue in single stat change in Absorption tab (was an issue with Gloom)
- +X% elemental dmg affix should now work properly in 1.05.
- The sheet should reflect the changes made in patch 1.05
- Deleted the +X% weapon damage for SW sheet, because sometimes it brings wrong results, and I prefer not to have it, than have it wrong sometimes - it requires some work, but the impact of it is marginal so it is not a big loss .
- Fixed an issue with the +X% weapon dmg sheet for SW.
- Added the skill dmg calculation for skills that can be empowered through gear giving them +crit chance.
- Fixed a display error in the +X% ele dmg in DW section and fixed a potential issue in variant 2 while both weapons did not have elemental dmg.
- (Hopefully) last change for absorption to finally work with paragons properly.
- Fixed Single stat change in absorption for lvls>60.
- Total health calculation fixed for lvls>60.
- Fixed IAS single-stat change for SW&DW - Value was wrong while selecting Enchantress buff as YES
- Paragon Level support for Absorption, which was ommited in 1.7
- Paragon Level support added - In the cell - Character Level type in a number from 1 to 160 (61 being Paragon level 1 etc.)
- DW for Open Office should work as indented now.
- Changed the skill dmg to reflect patch 1.04
- A new 4-th sheet has been added which allows to calculate the min/max weapon dmg more accurately (in case of weapons with +X% dmg bonus) - works only for SW atm.
- Change format of cells in Absorption tab to be % by default for melee/missile resistance.
- Fixed a possible DIV/0 error in DW sheet. (Leaving some cells blank).
- Hotfixed a 2nd error in the +X% Elemental dmg section. Previous version did not consider +min dmg from set bonusses.
- Hotfixed an error in the +X% Elemental dmg section.
- Added +X% Elemental dmg section - See "HOW TO USE +X% Ele in Main post for a guide".
- Fixed a refferal error in DW - it made to error in the calculations, so its just cosmetics.
- Fixed the skill dmg calculation from the item bonus for 3 runes that were bugged.
- The columns are now wider by default so users of OpenOffice who cannot widen them their selves can see 6-digit DPS figures.
- Fixed a bug with references of Other bonuses for Variant 2
- Reverted the data entry UI back to its old format due to large negative feedback on the new one.
- Added a stat weight table to the absorption sheet.
- Added support for "Gloom" rune for Shadow Power.
- Fixed a little ref error in attack speed in variant 1 of single-wield style.
- Remade the UI in order to support calculation of Skill dps, including +X% dmg to skill bonuses and +X% dmg versus Elites.
Note: Elites include (Champions, Minions, Rares, Uniques, Bosses and Treasure Goblins) - Tested.
- the UI changes also include merging all of "Gear" into one row, which makes it a bit harder to compare gear (need to deduct or add stats in one row, instead of separate gear slots), but makes the spreadsheet more legible and reduced it's size.
- Added support for Cull The Weak passive.
- Added support for Marked for Death ability.
- Added a new Sheet "Absorption". Which can be used to calculate damage mitigation of two sets of gear, comparing gear in terms of mitigation and effective health.
- Added support for the spreadsheet for characters of levels 1-60.
- Added a tiny table showing the comparison of key stats between Variants 1 of Single-Wield and Dual-Wield fighting.
- Fixed an in the calculation of impact of change of X IAS, the sheet always gave result for 1%.
- Reduced font.
- Fixed an error regarding Variant 2's weapon types, which resulted in referencing to Variant 1 instead.
- Added support for follower buffs and choosing other passives than archery, steady aim and sharpshooter.
- Fixed a little error with the weapon IAS in variant 2 of the dual-wielding spreadsheet.
- Added a second sheet allowing to calculate DPS while Dual-Wielding.
Note: Please mind that it is not 100% accurate (more like 99,9% from my tests), unfortunately after some investigation I am not aware of the source of the issue. However the range of error is so small it should not impact the decision process.
- Added a possible +Crit DMG stat on the weapon, since it has been missing previously.
- Added permission to adjust width of rows and columns to prevent from showing "###".
Link to the current version of the spreadsheet:
You have to make your own copy of the document to use it.
Link to the unprotected version -
Alternate link - Unprotected:
If You want to post a "bug complaint" or want me to help You with something You think is an error I will not consider them unless you use the protected version - since You simply could mess up the formulas.
The spreadsheet is saved in XLSX format, If you use MS Office older than 2007, you will require a plugin in order to be able to open such files.
Edited by Dretar, 14 May 2013 - 08:01 PM.