I've had a thread over on the official Monk forums for a few days now but I thought I'd post this here as well now that it is more fleshed out. I've been working on a Monk-specific spreadsheet that is now capable of calculating effective DR, Health and equivalency values based on mitigation, mitigation+dodge and effective health. You can also toggle all passive and active skill-based increases dynamically and compare results in each case (with active skills enabled, and with only passives), as well as viewing the relative stat values based on active or passive skills.
It also contains a sheet to calculate character sheet DPS based on all relevant stats with the ability to compare up to 5 weapons, as well as expected pre-mitigation damage values for each of the skills, along with their damage per spirit ratios, again with the ability to dynamically enable and disable all class's damage increasing skills. The damage calculator is not as fleshed out as the DR one - it won't do any sort of real time damage calculation based on rotation or anything like that.
I'm posting it here in case anyone finds it useful, but also in the hopes that people more mathy than myself can vette the information that it is outputting to be accurate enough. The official forum thread is here: http://us.battle.net...50759905?page=1
It will not work by directly exporting to excel because it uses a lot of validation cells for drop boxes. You can manually enter the cells requiring drop boxes and it will still work, or you can recreate the drop boxes. I would recommend just making a copy in googledocs and using it there.
Simple to use instructions:
For current gear DR calculations:
Enter your stats with no skills or runes active that will modify them directly as you see them on your character sheet into the white cells on the first DR sheet.
Modify the skills you want to enable or disable.
All your DR, EH and equivalency values will be visible in either yellow or orange cells - it is simply a matter of identifying which you want to know.
To compare gear for DR purposes, go to the second DR sheet, and enter the stats of the gear you want to remove, and the gear you want to replace it with. The sheet will drag all the relevant data from the previous sheet, alter it for the gear changes, and present it all to you in the same manner as the first sheet. It will provide a comparison of the two pieces based on the equivalency points on the left, along with showing the relevant drops or gains in DR/EH.
The only skill that is not covered adequately in terms of DR is block, and that is because the effective value of block changes depending on the number and strength of incoming attacks. You can enter your block chance and value in the sheet and it will present an average blocked damage per second depending on the attacks per second you set in the TTL section, but that assumes that all blocks are full blocks and isn't really a useful stat besides interest.
For the DPS sheet you can enter up to 5 weapons/shields and switch between them. Simple enter your base stats without a weapon in the top section, enter the weapon stats in the lower section, and then select which weapon you want in your main and and which in your offhand. The dps will change to reflect your weapon choices, and the expected pre-mitigation skill damage values will update below. You can choose which rune to view next to each skill.
You can also enable or disable any and all damage-modifying skills in the table below the ability damage table.
I made some very major changes to the structure of the sheet today, which _should_ have streamlined the usage to be more intuitive, and made tracking the formulas much simpler for verification purposes (particularly in the DR table, which is really the only complex bit of the project).
Would it be possible to add life on hit, life per second, and life steal to the EP section and gear comparer? I'm not sure how to make use of the EP values currently if self healing isn't modeled, since the balance of mitigation vs health would be completely different. Perhaps we're starting to get into simulator territory though.
Great spreadsheet, was digging around in it and noticed one small problem.
The Multi_Active_Armor and Multi_Passive_Armor ranges are shifted down by one, throwing the calculations for all armor with active and passive calculations off.
Thanks for the spreadsheet! If I find any more discrepencies I'll be sure to post.
Would it be possible to add life on hit, life per second, and life steal to the EP section and gear comparer? I'm not sure how to make use of the EP values currently if self healing isn't modeled, since the balance of mitigation vs health would be completely different. Perhaps we're starting to get into simulator territory though.
It's incredibly difficult to model these as you said, because they move into simulation territory rather than pure math functionality. It's a similar reason why it's unlikely I'll turn the damage page into a functional DPS simulator. The best I can probably do is offer a best case scenario heals per second calculation, and possibly make a fundamental calculation on time to live based on that value, but even that would require an incoming damage value, and the equivalent values given to TTL for healing abilities vs. effective health increases would change DRASTICALLY depending on incoming damage, incoming damage speed and incoming damage attack size.
TL:DR; it's probably outside the scope of my mathematical capabilities, particularly in a spreadsheet.
Great spreadsheet, was digging around in it and noticed one small problem.
The Multi_Active_Armor and Multi_Passive_Armor ranges are shifted down by one, throwing the calculations for all armor with active and passive calculations off.
Thanks for the spreadsheet! If I find any more discrepencies I'll be sure to post.
Thanks for that, I noticed your post on the official forums and have fixed it. It was a result of moving a couple of cells to make space for an ability I had missed, and I forgot that named cells don't update their locations automatically. It should have only affected calculations for the last day or so.
for some reason i enter everything correctly but the DPS number (cell d12 on the damage sheet) seems off by a big amount (almost 1k dps difference than my ingame tooltip)
is this the slight error you mentioned in a note in the sheet "Tooltip DPS for Dual Wield and +damage% weapons will be slightly off due to rounding order errors. " or is it supposed to be smaller than that ?
also there are 2 values that i dont exactly understand where to get them from on the character sheet
Total Minimum Damage & Total Maximum Damage, where exactly should i get these values to input them here ? (this is the only value that i havent figured out where to get, and i am guessing maybe this is the reason for the difference ?)
for some reason i enter everything correctly but the DPS number (cell d12 on the damage sheet) seems off by a big amount (almost 1k dps difference than my ingame tooltip)
is this the slight error you mentioned in a note in the sheet "Tooltip DPS for Dual Wield and +damage% weapons will be slightly off due to rounding order errors. " or is it supposed to be smaller than that ?
also there are 2 values that i dont exactly understand where to get them from on the character sheet
Total Minimum Damage & Total Maximum Damage, where exactly should i get these values to input them here ? (this is the only value that i havent figured out where to get, and i am guessing maybe this is the reason for the difference ?)
It definitely shouldn't be that big.. can you link me to your copy of the spreadsheet with this damage difference in place and tell me what your in game damage is showing?
Minimum and maximum damage is exactly that.. when an item says "increases damage by 6-11", or "increases minimum damage by 6" or something along those lines.
I have added a whole lot more to this spreadsheet, as well as expanding it to model different classes. It is currently functional for monk and wizard, and will soon be functional for barbarian.
A great many updates and changes have been made to the sheet, which should now be nearing completion. I have made a mirror of the sheet too, since the original one often has enough users for google to limit useability.
Among the changes, you can now view the difference to your DPS and skill damage expectations based on a new piece of gear in any slot, along with up to 5 different weapons, and the calculations for many of the DR formulas have been made more eloquent.
I'm currently working to make the skill damage report on the DPS more intuitive; some of the numbers don't make a lot of sense when viewed in exclusion unless you know what you are looking at. I will be adding comments to every rune that doesn't do the same basic damage as the base no-rune attack explaining exactly what the numbers show.
I have also normalised the way a lot of the abilities are showing so that they are at the very least consistent in the way they display.
not working for me can u help me plz? https://docs.google....13c1hOaXc#gid=4
using the second weapon sheet says 51k dps but on my character is just 38k :'(
Your sheet is locked, and I'd need more information than that to tell you why the difference. Chances are you added something you shouldn't have, or you have abilities enabled on the sheet that aren't in game. Don't add elemental damage from your weapon to the reported range, for example - it is already included.
Anyway, the reason I am replying is because I have been banned from the official forums for a week for saying, and I quote, "they broke the fuck out of Ghom". I've put a note on the sheet, but I thought I'd post in each of the locations (that I can, anyway), where I have the sheet. I won't be playing D3 anymore, and subsequently I won't be updating the sheet. I feel the game does not have a strong enough basis, and even if it did, does not have a development team with the right mindset, to turn it into a game with any sort of longevity. The forums were more interesting than the game, and now I have been banned from those. Oh well.
Thanks for the support!
Rollback Post to RevisionRollBack
To post a comment, please login or register a new account.
It also contains a sheet to calculate character sheet DPS based on all relevant stats with the ability to compare up to 5 weapons, as well as expected pre-mitigation damage values for each of the skills, along with their damage per spirit ratios, again with the ability to dynamically enable and disable all class's damage increasing skills. The damage calculator is not as fleshed out as the DR one - it won't do any sort of real time damage calculation based on rotation or anything like that.
I'm posting it here in case anyone finds it useful, but also in the hopes that people more mathy than myself can vette the information that it is outputting to be accurate enough. The official forum thread is here:
http://us.battle.net...50759905?page=1
The spreadsheet is here:
It will not work by directly exporting to excel because it uses a lot of validation cells for drop boxes. You can manually enter the cells requiring drop boxes and it will still work, or you can recreate the drop boxes. I would recommend just making a copy in googledocs and using it there.
Simple to use instructions:
For current gear DR calculations:
Enter your stats with no skills or runes active that will modify them directly as you see them on your character sheet into the white cells on the first DR sheet.
Modify the skills you want to enable or disable.
All your DR, EH and equivalency values will be visible in either yellow or orange cells - it is simply a matter of identifying which you want to know.
To compare gear for DR purposes, go to the second DR sheet, and enter the stats of the gear you want to remove, and the gear you want to replace it with. The sheet will drag all the relevant data from the previous sheet, alter it for the gear changes, and present it all to you in the same manner as the first sheet. It will provide a comparison of the two pieces based on the equivalency points on the left, along with showing the relevant drops or gains in DR/EH.
The only skill that is not covered adequately in terms of DR is block, and that is because the effective value of block changes depending on the number and strength of incoming attacks. You can enter your block chance and value in the sheet and it will present an average blocked damage per second depending on the attacks per second you set in the TTL section, but that assumes that all blocks are full blocks and isn't really a useful stat besides interest.
For the DPS sheet you can enter up to 5 weapons/shields and switch between them. Simple enter your base stats without a weapon in the top section, enter the weapon stats in the lower section, and then select which weapon you want in your main and and which in your offhand. The dps will change to reflect your weapon choices, and the expected pre-mitigation skill damage values will update below. You can choose which rune to view next to each skill.
You can also enable or disable any and all damage-modifying skills in the table below the ability damage table.
Let me know what mistakes you find!
Check it out.
The Multi_Active_Armor and Multi_Passive_Armor ranges are shifted down by one, throwing the calculations for all armor with active and passive calculations off.
Thanks for the spreadsheet! If I find any more discrepencies I'll be sure to post.
TL:DR; it's probably outside the scope of my mathematical capabilities, particularly in a spreadsheet.
Thanks for that, I noticed your post on the official forums and have fixed it. It was a result of moving a couple of cells to make space for an ability I had missed, and I forgot that named cells don't update their locations automatically. It should have only affected calculations for the last day or so.
is this the slight error you mentioned in a note in the sheet "Tooltip DPS for Dual Wield and +damage% weapons will be slightly off due to rounding order errors. " or is it supposed to be smaller than that ?
also there are 2 values that i dont exactly understand where to get them from on the character sheet
Total Minimum Damage & Total Maximum Damage, where exactly should i get these values to input them here ? (this is the only value that i havent figured out where to get, and i am guessing maybe this is the reason for the difference ?)
Minimum and maximum damage is exactly that.. when an item says "increases damage by 6-11", or "increases minimum damage by 6" or something along those lines.
I have made a mirror of the sheet because google seems to throttle heavy traffic spreadsheets:
https://docs.google.com/spreadsheet/ccc?key=0ApVAeMW4XRfcdHRLVllQMTd5TW9YSmNrOEVZMFo5S3c#gid=6
The mirror is here:
Among the changes, you can now view the difference to your DPS and skill damage expectations based on a new piece of gear in any slot, along with up to 5 different weapons, and the calculations for many of the DR formulas have been made more eloquent.
I have also normalised the way a lot of the abilities are showing so that they are at the very least consistent in the way they display.
https://docs.google.com/spreadsheet/ccc?key=0AoS59o87mX-XdHlCSm91Qi1Oc2o3LWlMYW13c1hOaXc#gid=4
using the second weapon sheet says 51k dps but on my character is just 38k :'(
Anyway, the reason I am replying is because I have been banned from the official forums for a week for saying, and I quote, "they broke the fuck out of Ghom". I've put a note on the sheet, but I thought I'd post in each of the locations (that I can, anyway), where I have the sheet. I won't be playing D3 anymore, and subsequently I won't be updating the sheet. I feel the game does not have a strong enough basis, and even if it did, does not have a development team with the right mindset, to turn it into a game with any sort of longevity. The forums were more interesting than the game, and now I have been banned from those. Oh well.
Thanks for the support!