Download link: http://rpg-state.com/Decimation.xlsm (obsolete atm)
Fixed error with average block-slip when maxblock is capped and min block isn't (partial overblocking).
- Blocking information now fully provided!
- Superstition implemented in the resistance tables as well for easy overlooking in the Res-Box.
- Fixed a potential double-dipping of relative EHP.
- Most huge numbers have been reduced to display in thousands rather than in actual size.
- Total Relative Effective Life has been updated to include the outcome of the blocking, which means that it is dependant on the model swing. The model swing is currently set to 170k but you can edit it all you like.
Requirements: Excel 2010
Decimation is a tool to calculate both defensive and offensive aspects of the Barbarian, in both singletarget and multitarget situations. The frontend is hopefully as intuitive as possible while retaining meaningful and valuable information. Not only can you evaluate your equipped gear in the form of both DPS and Effective Life / Relative Effective Life, you can also for example find out the value of your inserted runes and passive skills with a simple click, among other things.
How to use:
It is very easy to use Decimation, there are definitions of the most common keywords in the Info sheet (the first one Excel opens), and there are comments in several areas. First off, you simply navigate to the Equipped sheet and you will see that it looks something like this!
In this sheet you can select your gear, active skills, runestones and passive skills via dropdown lists. Parameters are found within the alabaster boxes accompanied by comments about what they do if you hover them. Your character stats that you can see ingame are found to the left. In the box above the skill choices you can see your DPS, average crit, average damage increase and avg ias, as well as your Relative Effective Life and Effective Life (Worst Case Scenario).
To the right of the skillchoice box is the Saved Setup box. Clicking on the Save Current Setup! button copies your current equipped gear/skills/passives/runestones to this box. The box labeled EzCompare then uses these values to compare your new saved setup against whatever choice you're making. This makes for easy comparing between two or more choices.
The second page you should become acquainted with is the Items sheet. In this sheet all items that you can find in the dropdown lists in the Equipped sheet is found, and here you can freely edit any value or name into whatever item you want to try. The Items sheet looks like this:
Each row represents one item, and there isn't a lot more to say about it!
For the more advanced user there is the Advanced sheet, which includes several variables the combat table uses for certain abilities. A few of them are for example whether or not to save up Fury when Wrath of the Berserker comes off cooldown, and how soon, how late you want to renew your Battle Rage, whether or not you want to synchronize the usage of Killing Spree (Overpower crit) and Best Served Cold (Revenge Crit) and more. There is also a damage breakdown graph alongside with graphed uptime of buffs.
It is perfectly fine to leave this sheet unused, the default values are, in my opinion, sensible.
How is the output calculated?
This is more in-depth, so feel free to skip.
This spreadsheet uses many recursive functions in order to keep track of progression/regression of various states, such as fury level, buff durations and so on. One row equals one user input (ie "global cooldown" or "action" ingame). Which input it is (which skill) is determined by algorithms, and the outcome is affected by for example fury level, buff duration remaining, whether or not the skill is actually usable at that specific point in time (can be on cd or haven't procced for example) and so on.
Several effects has probability counts attached to them. There are two approaches used; either cumulative binomial distribution, or a simple recursive function using the formula p success(p fail ^ n -1) + rest p.
These are used to calculate for example whether or not you were hit (there are functions for incoming swings as well, you can edit the target APS), whether or not Revenge procced from this successful hit, Frenzy Sidearm procs and so on.
When the actual ability has been chosen, the damage is calculated. The Items tab + Passive & Actives works as arrays, setting up a bunch of base values to use. These values are mainly weapon range, crit %, strength, IAS, dodge and DIM (damage increasing multiplier). These are all split into different columns, so you can at all times keep track of what your crit rate or strength/DIM is at any row. These snapshot stats are then used to calculate the actual damage. Because this model uses snapshot stats, temporary buffs are well-modeled.
This is how a small portion of the combat table looks.
If you have further questions, feel free to ask away.
Download link: http://rpg-state.com/Decimation.xlsm (obsolete atm)
Results 1 to 10 of 35
17-03-2012, 17:02 #1
Last edited by Superstate; 02-11-2012 at 20:21.
17-03-2012, 17:04 #2
Version 4.1! Layout Changes!
- Now emphasizes the easiest-to-understand defensive stats: relative effective life and worst case scenario effective life.
- New EzCompare box much closer to the "action" for readability.
- Basic parameters have their own sweet little boxes.
- On the left is also a new "character sheet" stat-list. These values were formerly spread here and there, but now they are all bunched together, also for readability. All these values should be exactly equal to the ingame values; if they aren't, an input is wrong (max life is an exception).
- Moved around a few boxes.
- Frenzy Sidearm built up probability while not using Frenzy (ie while using finishers), causing its proc rate to be slightly higher than it should have been.
- Revenge-related probabilities have been converted to the new system, using a limit of your choosing (default is 80% certainty). These are whether or not the hit was successful (ie dodge now scales down the Revenge damage accordingly), and whether or not Revenge procced.
- New Macros! Runestones now have a macro for determining their value (either as reL or DPS) in comparison to the base skill. Read the Rune Value note in the spreadsheet for more information.
Version 4.0! Huge huge updates! Layout, visible effects: - Choosing rune effects have been completely reworked. The color scheme remains (for now), information helps you along the way. - More defensive information on the equipped page, you can now quickly determine how valuable for example Treatening Shout is! - Added a small Damage Range Variance number for those interested in just how much the weapon range will fluctuate. - The Passive macro calculation now calculates increases in relative effective life for defensive passives as well! - Aharoni! Also, Actives tab is now very important for all proc effects (it is already the actual array the calculations used, it isn't there for show). Since we don't know the % proc on several effects (see BR runestones), you can now quickly change the proc %. Behind the curtains: - Split up regular additive damage increases from multiplicatives. The multiplicatives are now in a separate DIM. All m Dmg% effects are now added together IAS-style. Reasoning behind this below. - Huge updates to the recurring probabilities calculation. As a result, many of the proc effects have been reworked/added. More information below.Code:
- Into the Fray (Battle Rage) reworked to match
- Ferocity (Battle Rage) added
- Sidearm (Frenzy) added
- Slaughter (WotB) added
- Overpower has been reworked to match the latest updates where crits reduces its cooldown.
- WotB Insanity now counts as an m Dmg% effect.
- Frenzy Maniac now counts as an m Dmg% effect.
- Bash Punish now counts as an m Dmg% effect.
- Weapons Master Sword/Dagger now counts as an m Dmg% effect! (it appears to finally be buffed).
- And of course, all percentages should now match beta patch 14 numbers.
Version 3.1 - Macro-check for Passives, resulting in percentage of difference so you always know what passive is most valuable to you (since you rarely will be able to use 3 of them, this is very valuable information) - Fixed a bug in the tooltip char sheet dps calculator. It does not incorporate DW calcs yet. - Basic (atm) defensive calculator implemented. This uses formulae found here: http://us.battle.net...67597687?page=1 - Nerves of Steel, War Cry and Tough as Nails implemented. - Target level parameter added for the damage reduction calculations. Known bugs: Gradient fills on multiple rows can bug, which is noticable when replacing skills or runestones. Just pick the skill/runestone a second time and it disappears. Haven't found any fix (obviously).Code:
Version 3.0! - SEP Check implemented, check the updated picture of the Equipped tab! - Save Setup implemented, check the updated picture of the Equipped tab!Code:
- All actives, their respective runestones, passives and all equipped gear saved.
- EzCompare box for instant comparisons between the current setup and the saved one.
- This is awesome! Try it out!
Version 2.0! - Huge overhaul to the Equipped page, view first page for picture! - A controller page added, this page contains various values and conditionals used for rotations.Code:
- Currently only the most interesting (from an offensive perspective) abilities are included.
- Certain passives will be added that are currently hardcoded to act certain ways.
Version 1.12 - Much more stable when choosing weird builds (such as no Battle Rage and so forth). - Revenge implemented. Basic algorithm for synchronizing OP+Revenge for crit combos. - Percentages fixed to reflect beta patch 10. - Made skill algorithms much easier to overlook, and smarter overall. - Fixed spenders recieving illegitimate IAS from Frenzy. - Implemented no incurred Frenzy/Cleave/Bash GCD when using Battle Rage (this has been tested ingame by me several months ago, I made a topic somewhere). - WotB is presumed to behave the same. - A few bug fixes in the BossKiller module. - Other smaller stuff.Code:
Version 1.11 - A huge change to the dependency tree in the combat lines, completely eliminating the problem with hitting max iterations per calculation cycle (no more repeat F9's whenever you switch one item or ability or runestone, instant calculation). - Added a simple Boss-Killer module; insert hp and it will tell you how quickly the boss dies, and what dps you kept until it died. - Added more information concerning leech. - Formatted Items-tab (check first page for updated pictures). - Added some nice avatars for the classes. - Fixed a bug causing the DI to become 100% more. - Made the calculations for each damaging ability as transparent as humanly possible; now everyone with Excel knowledge should be able to inspect how runestones are implemented without getting stuck looking at a wall of numbers. - Removed Precision - Removed Defense - Removed the incoming damage taken calculator for now, until we know how the new damage reduction mechanics works.Code:
Version 1.10 Notes: - Quick AOE dmg comparison of different abilities and runestones have been added. Not all are added yet. - Overpower has been changed slightly to reflect the changes of making it cooldown-based. - Several percentages have been brought down since they don't make sense, even if we don't know their actual stats.
Version 1.09 Notes: - Updated following percentages: No Escape 15 % damage WT 10 fury cost WT 180 % wpn dmg Frenzy 100 % wpn dmg BR 20 % dmg
Version 1.08 Notes: - Frenzy crimson only multiplies its own damage - Bash indigo only multiplies its own damageCode:
v1.07 notes: - Spender calculations rewritten to include elemental procs. - Experimenting with %dmg from abilities and runestones being multiplicative across the board. - Formatted items to be slightly more clear.Code:
Beta version 1.06 notes: - Battle Rage +% damage now multiplicative, as it appears to be ingame. - Bash and Cleave correctly calculates only one weapon swing when dual-wielding.
v1.05 - Mechanics related to spenders have been reworked according to recent finds: - Spender gcd's are based on the weapon aps and currently assumes an additive 10 % ias bonus, check spender thread for more info. - Spender dual wield damage has been updated according to very recent finds, more in spender thread and later in this thread. - Base stats added as follows: 128 attack, 68 precision, 129 defense, 129 vitality. This is according to actual level 1 base stats and what each level yields, as found by drjdredscythe. - Cleaned up some of the combat tables in order to avoid future div zeroes which plagued this release.
v1.04 - Defensive + resistance mechanics added, courtesy of pmpch. Still missing the defense + resistance combined mitigation. - Life/hit and regen life / sec implemented as leech. Leech renamed to Life Gain / sec. Lacking chance to drop health globe variables to fully complete the life gain part. - Formatted fury gain / current max fury in the mainpage - Damage reduced by X % implemented as a calculated value. This incorporates dodges, but only calculates physical damage taken at the moment. It also lacks block (mostly due to me not knowing how/if block value scales). - A magic find, extra gold find and movement speed counter implemented.
Last edited by Superstate; 17-03-2012 at 17:21.
17-03-2012, 17:08 #3
Diablo 3 Beta Tester
- Join Date
- Oct 2011
- Soviet Canuckistan BattleTag Bowzer -1925
This is quite intriguing.
I will give it a download in a bit and let you know how it goes.
17-03-2012, 17:36 #4
25-03-2012, 05:50 #5
- Quick nagivation buttons on the Equipped page to edit your items easier.
- Character sheet dps calculator now correct.
- Fixed the average swing calculation, now uses 100% less DI.
- More prettification of the combat table to emphasize data for both myself and anyone intrigued in checking out what's going on.
- When editing Weapons you no longer add the APS, just the weapon type and 1h/2h (since they determine the base aps).
- The window in the Equipped page that showcases your combat table results had an avg DI of 100% too much (directly related to avg swing bug mentioned above).
Also, a Wizard version of Decimation is incoming.
07-04-2012, 18:23 #6
- Cumulative binomial distribution for Revenge procs implemented, which enables a high amount of incoming swings, Provocation is thus finally well-modeled.
- Incoming swings are now perfectly modeled up to 10 targets with updated rest, extra return functions and so forth for rows where the inc aps is higher than the player APS.
- The APS of an individual attacker can be edited in the Advanced sheet just like before, but now the spreadsheet multiplies the set APS with the Target multiplier. This is to ensure that abilities such as Revenge essentially are modeled with a perfectly even flow of attacks rather than a big chunk of attacks at once.
- Cumulative binomial distribution also implemented for Ferocity and Into the Fray in order to ensure their proc rates are increased accordingly on multiple targets.
- Dodges are now inverted to calculate the chance to miss on target instead. Because the spreadsheet uses limits where an event is said to have occurred, enemies practically couldn't miss previously at very low levels of dodge (such as base). This was now fixed.
- Legendary has a new color scheme woot!?
- Fixed a few more max targets. No spell has an unlimited max number of targets any longer. They can all of course be edited in the Advanced sheet.
- Fixed fury gains for Cleave that wasn't max-capped even if the damage was.
- Fixed an accidental "unhook" of gear IAS, this was due to me moving around stuff in the Calculations tab.
- Started adding Proc Details for procs in the Advanced tab. Not finished, but it is a start.
- Max Life is off for any non-L13 Barbarian, due to an unknown (to me) formula for base hp, ie hp without base vitality.
Thanks to saesa for the idea of cumulative binomial distribution!
08-04-2012, 20:35 #7
08-04-2012, 20:42 #8
09-04-2012, 22:57 #9
- Resistances reworked! For some reason I hadn't done this before, but now they are in and working great. Picture below!
- Base hp should be correct up to L13 at least, presumably longer, thanks to HardRock!
- Impunity added, there's a flip for whether or not it's multiplicative or additive. Default is additive, even if that seems off from the tooltip. You can find the flip at cell CQ11 in the Calc tab.
- Percentages matching patch 16!
Known issue: Dexterity has no diminishing returns on Dodge in the sheet at the moment.
09-04-2012, 23:05 #10
That EHP table is nifty.