Print

[Superstate]

Download link: http://rpg-state.com/Decimation.xlsm

Version 5.4.2!

• Fixed error with average block-slip when maximum block is capped and minimum 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.

Introduction


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

[Superstate]

Code: [Select]

Version 4.4!

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.

Known bugs:

- 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!

Code: [Select]

Version 4.3!

- 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).

Code: [Select]

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.

Behind the curtains:
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.


Code: [Select]

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: [Select]

Into the Fray (Battle Rage) reworked to match
Ferocity (Battle Rage) added
Sidearm (Frenzy) added
Slaughter (WotB) added
- Other skill updates:
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.


Code: [Select]

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: [Select]

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: [Select]

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!
- Test Stats window added! It allows you to add any stat you want in a very straightforward way, rather than forcing it in somewhere else.
- Front page formatting, added a background and replaced a few item pictures to ones that are actually the Barbarian versions (remember all classes have different looks on many items).
- Added comments here and there for places I think people might wonder what that specific thing does.
- A tooltip DPS calculator added! This can't unfortunately match ingame numbers since the ingame ones appears to be rounded somewhere, whereas this calculator uses exact numbers. It should be very close though (generally within 0,1%).

Code: [Select]

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: [Select]

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.

- Items page also received an update
- You can now choose character level to change the base stats to that level.

Code: [Select]

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: [Select]

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: [Select]

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.

Code: [Select]

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

Code: [Select]

Version 1.08

Notes:

- Frenzy crimson only multiplies its own damage
- Bash indigo only multiplies its own damage

Code: [Select]

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: [Select]

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.

Code: [Select]

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.

Code: [Select]

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.

[Superstate]

Version 4.5!

• 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.

[Superstate]

Alright, hi there fellas!  Was thinking that I should start with introducing myself and answer a few questions. If you have more, please write!

There is already a Decimation topic up, why repost?

Well I need to be able to update the op for new versions for a start. 


Why post it here?

I've had Decimation up on general news sites since late September last year (the development has been longer than that), and while I believe users appreciate the efforts, I'm not THAT interested in the general mass that is out there. saesa has been pushing me to this site, and while I do understand that it isn't a 100% international site, I don't mind exposing it to a different crowd who possibly can make use of it as well.

Who the fuck is Superstate?

Former raid leader and guild master of the best raiding Swedish WoW-guild for several years (Memento Mori). First time I played D2 was in 2000, and D2 was the first game I played hardcore, in 2003 and forward. IRL I'm a regular guy, I don't work with mathematics or programming, I just enjoy doing it as pastime; I find logical builds and systems extremely interesting.


How accurate is this spreadsheet? What are some of the unknown variables?

It depends on what area of the spreadsheet you're looking at. The defensive portion with %DR, effective life, resistance DR and so forth is extremely accurate (100% in fact). There is one exception in the version that I posted now, and that's dodge, but that's fixed to the next version. The reason why is because dodge uses a very strange formula to determine dodge yield (0,1% dodge / dex up to 100 dex, 0,025% dodge / dex from 100-500, 0,02 from 500-1000, 0,01 from 1000-8000  [where you reach 100% dodge]). In this formula, 100% dodge is a possibility.

Most of the defensive calculations are derived from formulae that I haven't myself theorycrafted (I've spent my time on the offensive portion). Here is a source for the DR% Armor for example:

http://us.battle.net/d3/en/forum/topic/3967597687?page=1


I've tested this ingame and it matches 100%. So does the resistance formula, which is found elsewhere.


As for the offensive mechanics, I've found several interesting things. A few of them are very "old" now, but keep in mind it was in September when the beta started that I was really looking at this stuff, now it has all been determined and proven and instead I sometimes confirm they haven't changed any of the mechanics. Some mechanics I've found several months ago are:

- DW bonus
- Spender IAS bonus (around 10%)
- Additive IAS
- Additive DIM's
- Non prim gens uses an APS of around 1,2
- Determining elemental effects

and more..


With all that said.. there are areas where the accuracies falls off, but that's not due to calculation errors. This is due to presumptions we have to make about the game. For example, we're assuming for several reasons that IAS effects from abilities we can't reach such as Wrath of the Berserker, and DIM's such as WotB's Insanity runestone, are all additive.

In the case of IAS effects this is something I've found in game. I've tested Frenzy shrines, DW bonus, WM spear/polearm, gear bonuses and so on, and they are all additive. Most importantly, I've also tested Frenzy, and decided that it too appears to be additive. This is why it appears very likely that WotB is another additive IAS effect, and so it is modeled.

For DIM's there quite recently was a sum added to the character details depicting damage from skills. We do know that Battle Rage is a multiplicative damage boost, and it goes in this number. On another occassion I noticed that WM sword also does, which very much does make sense. Otherwise it would be increasingly powerful to stack all multiplying effects, and well.. it isn't. It's a linear return, as is most aspects of the D3 mechanics.


This is a beta, doesn't mechanics/abilities change a lot?

All passive / active skills are dynamically linked, all their tooltips as well, and names. In the Actives/Passives tab any number can be tweaked and the spreadsheet is immediately updated. Reworking percentages to match a patch for example literally takes 1 minute, and you can easily do it yourself (this is the main reason for dynamically linking everything).


As for basic game mechanics, surprisingly few has changed since September. The most notable changes have been the nerfs of runestones / passives rather than game mechanic changes. In fact, I can't really think of any mechanic that has changed.. The stat change wasn't that much of a change in the spreadsheet, just had to rehook Attack into Strength really.




So all in all.. I've spent hundreds of hours of my limited free time to do this. Hope you enjoy it and if you have any questions, go ahead and ask them.. 

[rSt]

Former raid leader and guild master of the best raiding Swedish WoW-guild for several years (Memento Mori)

Oh, that's why your name was so familiar, mi first wow char was on frostmane

[Superstate]

Oh, that's why your name was so familiar, mi first wow char was on frostmane

Superstate was only an alt at the time but yes.. 

[Spirals]

It really looks impressive. Thank you for posting it.
I will check the content out later in detail

[GuN]

Thank you for sharing your work. Its nice to see such a huge in depth TC right now.
There is one thing, i havent seen youve mentioned so far. What about DoT´s like rend. Do they scale with IAS like they do with haste in WoW ? Have you noticed any math so far regarding DoT´s ?

[Superstate]

Thank you for sharing your work. Its nice to see such a huge in depth TC right now.
There is one thing, i havent seen youve mentioned so far. What about DoT´s like rend. Do they scale with IAS like they do with haste in WoW ? Have you noticed any math so far regarding DoT´s ?

There aren't that many dots around really, as a consequence not a lot of math has been done on the particular subject of IAS effects combined with dots (at least not for Barbarians, where Rend is our only dot). The channeled spells might appear equal to dots, however they work fundamentally very different, where we know channeled spells have cycles which are directly effected by weapon speed, and thus IAS effects.

The easiest way to test this isn't to test Rend, it would probably be to test Haunt since it has a much longer duration, a time reduction via increased IAS would be much more noticable if IAS effects works. I'm pretty certain that if somebody has worked out the kinks of dot mechanics, it is very likely an aspiring witch doctor.

So no, I haven't actually tested it and it had slipped my mind. Do I believe IAS quickens the dot pace? No, but rather be certain about it. I'll look into this first thing tomorrow.

[Superstate]

Version 4.6!

• Item values added on the Equipped page! It uses an internal SEP and AEP (Armor) check using the lowest unit of each stat to roll all offensive and defensive stats of an item into offensive and defensive sums. Check picture below.
• Dodge now diminishes correctly with Dexterity (until release at least..)
• Tough as Nails and War Cry are presumed to stack additively rather than multiplicatively, ie the armor multiplier becomes 1+0,25+0,2 rather than 1,25*1,2. This slightly lowers the overall value of using both at the same time.

[Superstate]

Version 5.0!

• Introducing view modes! I was worried about unnecessary information for the new user, and as such the Minimalist view mode is now default. There is a dropdown list above the DPS box that instantly changes the layout if you want to use the Advanced mode. Check picture below.
• Introducing Life mode! The default mode is Time (down in the alabaster box in the Equipped tab), but changing it to Life brings a new box forward to set the target health in k Life. Above the DPS another box has appeared with the finished time it took to reach the target health. Check picture below.
• The Items tab now highlights the rows of items you currently have equipped so you easily can see what item to edit. Check picture below.
• Fixed a serious bug causing the combat table to break down completely. The probability of getting hit could get negative in situations where a dodge was followed immediately after a Revenge proc causing a NaN error, which in itself is a SPOF.

Time Box




Highlighted rows for equipped items






Minimalist viewmode

[Superstate]

Version 5.1!

• ​Cleave's native fury generation now separated from Reaping Swing / WMMW, fury generation for Cleave is now correct on multiple targets. Thanks to Draner for notifying me.
• Multiple primary generators no longer cast simultaneously.
• Using a gamestyle to delay casting Overpower / Revenge until after another attack has been abandoned. This means that Overpower / Revenge now incurs a regular ability cooldown.
• Momentum (Overpower) now functions correctly.

[B4ST4RD]

Thanks very much for your efforts. I definitly will use this awesome tool of yours at release.

[Superstate]

Version 5.2!

• Introducing Release day Mechanics! There's a quick nav button in the Equipped sheet that takes you to the previously hardcoded assumptions about some of the behaviours of the higher level runestones.
  More information below.
• Dread Bomb modeled!
• Implemented the specific skill affixes.
• A small Thorns calculator added. It uses the same variables used for the combat table (all editable in the Advanced tab of course).

About the Release day Mechanics!

With these added, the spreadsheet effectively doesn't need to be in a beta state any longer, since the true D3 mechanics is some combination of the following mechanics. These are the mechanics you can change with simple lists:

• Impunity - multiplicative/additive
• WotB IAS - multiplicative/additive
• Insanity DIM - multiplicative/additive
• Battle Rage DIM - multiplicative/additive
• WW Dmg - Dmg / Cycle or Dmg / Hit
• WM Fury Gain - Fury / Hit or Fury / Attack
• Maniac - All Damage or Frenzy only
• Maniac DIM - multiplicative/additive
• Punish DIM - multiplicative/additive

You can still of course change all the percentage-based events which we don't have a number on yet as well.

There is mainly one thing I'm still curious about when it comes to Barbarian damage-dealing, and that is how the WW attack speed works. At the moment the spreadsheet uses a static 3 APS regardless of weapon type, which obviously hugely favors 2handers. Regardless, the APS used can be edited in the Actives tab!

Thanks very much for your efforts. I definitly will use this awesome tool of yours at release.

Thank you. 

[Mr.Mag]

is this calculator working properly and is up to date? i'd like to put in on news