Hey, I 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 by knowing the impact on your DPS before making the actual purchase.
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.
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 spreadsheet for dual wielding is not 100% accurate (more like 99% 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.
10) The dodge calculation is an estimate, so it might not be 100% accurate. (But works fine for tested sets of equipment).
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). You can also disable it by selecting "NO" to 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.
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
If you have any questions concerning the calculations/spreadsheet feel free to write them down.
And don't forget to thumbs up! :)
Current version of the spreadsheet: 1.94
Latest Changelog: - Shortened due to post character limit
- Adjustmet skill damage to reflect 1.08 changes.
- Fixed Single-stat change for All res in 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).
- Bug in Gloom in the absorption tab.
- Fixed the +X% elemental damage affix - Blizzard has indeed changed it works.
- 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 :(.
Link to the current version of the spreadsheet:
You have to make your own copy 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.
It should be 34364.55 (this is with steady shot and archery passives) but the spreadsheet insists it is 33868.91.
I've doubled, and then doubled checked again to make sure I've written my gear accurately onto the spreadsheet. The only discrepancy I can find is that in-game I have critical hit chance 8% but on the spreadsheet it is 5%. I can't figure where that comes from though. I have no crit gear at all, so it should be 5%.
I am currently adding the support of follower buffs to the sheet. Will come up soonish.
Requesting a sticky for this. I like this tool a lot.
05/06/2012 18:38Posted by kongKevinare u sure this calculates the dmg properly?
Yep. I hit my DH 60 just this morning and been playing with this sheet since then and it has been accurate every time.
Nice job on adding the follower buffs and also making it possible to change around the passives. The sharpshooter is maybe a bit redundant, since whether you have it on or off, it'll show DPS with it, but now I'm just knit picking.
Appreciate the work you're putting into this. Has made at least my life so much easier when looking for upgrades on chat, the trade forum, or the AH.
I would put out a small request, to let us alter the fonts and colors? Not just to make it for aesthetically pleasing (for me anyway... *knit picking*) but also because with the 1.2 upgrade the whole thing no longer fits on my screen.
Also reduced font so it should fit Your screen hopefully ;).