Bolche

I know there is already several warlock DPS Spreadsheets, but I didn't found one that could easily answer the 2 following questions :
- With my current gear, how many dps do I gain from this talent ?
- What is my equivalence between +dmg, hit, and crit ?
- It is worth casting this spell in my cycle (ex : immolate if you have a lot of +shadow damage) ?

So I made my own one.


The link : WoW Warlock DPS Spreadsheet by Leulier

All versions : Index of /spreadsheet

Thank you for your comments

Warlock DPS Spreadsheet

 

• frmorrison

I assume these numbers are against a boss (with the 17% miss rate)?

I like that your spreadsheet prevents entering numbers that are not possible.

The improved immo field had an error with the restricting values (should be numbers between 1 and 1.25), all I saw was 1.25 and some text.

I noticed Nightfall was not there (I know it is hard to model, but just using the tics of Corruption and ignoring Drain Life is a good start).

What is dégat durée?

I understand "imp ago" is improved CoA, but try "imp CoA" instead (easier to understand).

 

Bolche

Thank you for your comments.

I have made a lots of improvements to my spreadsheet :
- You can now chose every spell you want to include in your cycle. This will typicaly help "full shadow" spec warlock to decide if they should cast immolation or not. Or "full fire" with CoA or corruption.
- You can also chose the curse you cast, thus answering the CoA / CoD / CoA questions
- I also added a little information about the mana used per second and per minute
- You can chose to add LT or DP to your cycle, an set the frequency of it.
- And there should be no remain of french anymore ^^

The lasted version can be grabbed at http://www.leulier.com/

Warlock DPS Spreadsheet

 

Bolche

OK, I have added nightfall and made a few corrections.

The only I don't know how to model is the dps loss when one of your dot fades while you are casting a SB or incinerate. Or when 2 dots fades at the same and you can't of course refresh them both at the same time.
The the formula "dps = damage/duration" is a little optimistic.

If someone has already thought about a model of dot gaps and can share it... :-)

Warlock DPS Spreadsheet

 

Crepe

Nice resource. I especially like the "what is X stat worth in terms of Y stat?" comparisons.

One suggestion/request: can you add an option for using conflag? I'd like to see how it compares.

 

Kahra

Isn't the value of hit rating for dots a bit overvalued in the spreadsheet? As far as I can tell, the assumption is made that dots aren't recasted if they are resisted.

Modelling dot resists as an averaged cast time increase should give a more accurate model (and it will bring the value of 1 hit rating to roughly 0.8 shadow damage for a well geared 41/0/20 lock).

 

Bolche

I was just about releasing a new version when I read your comments, so I release it anyway :

* Remove demonic tactics as it now gives 5% crit (juste add 5% crit to your global crit %)
* You can now chose the filler spell you are casting while not refreshing dots/curses
* You can now chose the curse you cast
* Added Damage per mana information
* Added a small item stat comparator
* Many bug fixes

- About conflag, I asked myself if I should include it or not when I added many spells. I beleve it is always a bad idea to cast it in a sustained fight, since you are using your global cooldown and (at least) one immolate tic for a spell that scales not well. Anyway I will include it in a future version, so we can see if it is that bad ^^

- About the way %hit effects dot, its indeed true that the current model assumes that dots aren't recasted if they are resisted. Your idea of introducing a "effective cast time" seems good, I will put it in the next version.

Warlock DPS Spreadsheet

 

weet

Something really strange happens to 'the next stat' table when I put the shadow and flame modifier at anything instead of 1.2, all the values drastically change. Might need to look at that.

Also I was a bit confused by the modifiers in the destruction talents, as your default spec has siphon life with shadow and flame, along with things like improved searing pain. Yet ruin and improved immolate start as a number of just 1, is this a spec with or without ruin/imp immo? If so what values should they be at if I wanted/didn't want them.

Overall it is very nice though, I found it useful in getting a more realistic idea of some of the hit/crit comparisons as a dot hit capped 41/0/20 lock. It is also rather depressing looking at the amount of damage lost caused to my ping of 400ms in comparison to 100ms, almost 10% =[

Keep up the good work!

 

Chmee

1. If you start including damage/mana numbers then figuring in conflagorate would be extremely helpful.

2. I have to look over your new version, but previously changing the 'lifetap time between casts' (I think default was 30 seconds) really through off numbers across the board.



3. Awesome work!

 

Apepi

A quick question: In excel is it possible to make one value a set amount from minimum of other values. By that I mean, could you make it work out what the minimum values of: Supression, +Hit from Gear, Total crit and +dmg would be to reach X amount of DPS?

 

• frmorrison

Excel is uses equations to find the dps of your spec so uses a few variables to find your dps, such as spell hit, crit, +damage and talents.

So the formula would be something like x + y + z + w = dps, so if you knew three of the variables you could solve for one of those dps stats to get a given amount of dps.

 

TheOnly

The "next stat" section is far far off from reality. Simply add +50 to spell damage and manually calculate the increase. Then add the (approx) 4% hit that +50 hit rating gives and look at the dps difference. For my gear and spec, +50 spell damage is 20 more dps and +50 spell hit is 30 more dps in your dps calculation. However, the "next stat" chart indicates that +damage is 2x as good so it is clearly not working right.

Your model for spell miss on dots does not take into account refreshing it if it resists and assumes the entire DoT is lost. This heavily overvalues +%hit gear for dps. Though we all know how good it is for CC and soulshatter.

Model for dot recasting:

If the chance to resist a Dot is R ( between 0 and 1) and the time taken to cast + lag is T, then the "effective" cast time, T_eff assuming if it resists the dot is recast, is the following on average:

T_eff = T + T*R + T*R^2 + T*R^3 . . .
or
SUM_n=0_infinity ( T * (R)^n ) Since 0 < R < 1 this has a closed form of:

T / (R-1)

For example, if it is a 50% resist rate, then

T_eff = T / (1-.5) = 2T.

For 10% resist rate it is
T_eff = T / (1-.1) = T / (9/10) = 10 *T /9 = 1.111 T


In addition, in real life the "lag" factor for recognizing a resist is larger than the normal factor of chaining spells together because it is a "change of plans". An additional parameter "resist realization time" that adds to the above would make sense. This would change the equation to the following, with the realization time, Z:

T_eff = (T+Z) / (R+1) - Z.

The subtraction of Z is due to the fact that it only applies on resists, not the first cast. So if R = 0, the effective time is T, not T+Z.



--------------

Modeling dot refresh overlap and overlap with the "filler".
In my experience, there is always some lost time. The way to model this is to increase the duration of dots on average (but not the cast time or damage). Just put a universal duration adder for any dot or curse that is less than 2 minutes.
On average, with perfect timing this addition will be (filler cast time)/2. But with several dots to chain, this lost time only affects the beginning of a chain.

An example:
I can keep UA/Corr/Immo on the same cycle throughout a fight, with 1 lost immo tick.

casting UA > Corr > Immo means that all three will finish at the exact same time, and that recasting this sequence such that UA/Corr lands right as they expire means one lost Immo tick. Since immolate is the lowest ticking dot and is front loaded with DD, this is not a big deal.

refreshing DoT's slightly early has the same effect as doing so slightly late, dps wise (but not DPM wise). On average one half of a tick of damage is lost (except for CoA). So the model of adding a fixed time to the duration of all DoTs, with the default being 1.5 seconds, is a very reasonable approximation. Since 1.5 seconds is approximately half the filler cast time, this makes sense and we can model it all with one number rather than different ones for being too early, too late, or in the middle of a filler cast.


-------------------


These changes should be fairly easy for the spreadsheet.

1: Make the DoT damage columns not be affected by resist rate.
2: Adjust the dot cast time by the resist rate modified cast time.
3: Make the duration of a DoT in a cycle (but not its damage or cast time) increased by a flat time adjustment to account for imperfect dot refresh cycles and overlap with the filler.
4: Make "the next stat" section simply run the DPS number twice with +50 equivalent of each stat, take the difference, and measure the slope of DPS increase. This is a lot easier than calculating the derivative of the whole equation, which will change as you tweak it. It requires slightly more parameterization, but that will only help maintain the spreadsheet in the long run anyway.

 

Bolche

In the "The next stat" section, you have the dps bonus for each stat. For exemple, with the default setting, +1 shadow damage gives you +0.51 dps. So you can use this to se how +shadow dmg you need to gain 100 dps.


1&2. done
3. I have done it too, but I still think this needs improvement. The more dot you cast, the harder it is to keep them all up without loosing to many filler spell.
4. This is already the way it works. There are 4 hidden tabs wich run all the equations but with juste +1 shadow dmg/ +1 fire dmg / +1 hit rating / +1 crit rating.

About the "change of plans" delay, it is true for non instant dot (Immo/UA), but for instant dot, you have the time of the GCD to realise that you spell was resisted, which is enough I believe. I will perhaps add it for Immo/UA in a future version.


I also have a question, as I have not been conflag spec for a long time : does a conflag resist remove the immolate anyway ?


1.07 changelog :
- bug fix : Immolate does no more assume you hve bane 5/5
- bug fix : the direct damage part of immolate now correctly takes in account all bonuses
- new %hit model for dots : you now recast a dot on a miss. %hit now reduce the dot effective cast time instead of inscreasing the dot damage
- add conflagrate
- added "average dot gap" as a way to model the time a dot is not immedialty refresh because you a casting your filler spell

Warlock DPS Spreadsheet

 

• stop

unfortunately, yes

 

• frmorrison

The changelog for 1.07 looks interesting, however your site is hosting 1.06 currently.

 

Bolche

oops, fixed

Warlock DPS Spreadsheet

 

Zoner

I have my own personal spreadsheets for rating gear and comparing affliction and destruction specs with it.

Hit is nearly worthless for affliction spells, but as shadowbolt still (should) make up a substantial part of your attack chain unless its against multiple targets, hit is still fairly valuable.

Assuming a ballpark of 1100 damage and ruin:

Shadowbolt:
1 dmg = 1.381 hit = 0.788 crit

Immolate
1 dmg = 0.695 hit = 0.344 crit

Corruption
1 dmg = 0.078 hit

Curse of Agony
1 dmg = 0.059 hit

Shadowbolt makes up from 40 to 65% or so of most attack cycles. Even on the upper end 65%, it dilutes the value of hit to under 1:1 ratio vs dmg. DMG is king for warlocks of all specs. And thats not even going into how it affects mana recovery. It is a flat out better stat than hit and crit.



Anyway spell misses are goverened by the following equation:

(CastTime * MissMitigationRate) / SpellDuration

MissMitigationRate is defined as ((1/(1-MissRate))-1)

For nukes CastTime and SpellDuration are equal, but for dots they are very lopsided.

Assume a boss 17% miss rate and no hit gear:

MissMitigationRate for 17% MissRate is ((1/(1-0.17))-1) = 0.2048


Shadow Bolt
(2.5 * 0.2048) / 2.5 = 0.17 = 20.48% dps loss

Instant Corruption
(1.5 * 0.2048) / 18 = 0.0141666666 = 1.70666% dps loss

 

Ironis

This isn't quite right, for DoTs you're only taking into account the possibility of either a hit or one resist, not the possibility of getting two or more resists in a row. TheOnly covered the math in his post, but basically your equation should read:

Instant Corruption
(1.5/(1-0.17) - 1.5*(1-0.17))/18 = 0.0312349 = 3.123% dps loss

The first part is the average cast time (GCD as 1.5) of a dot, and you subtract the first term off the infinite sequence because it represents the hit.

 

Zoner

.. deleted ..

 

MissMaldita

Is this spreadsheet using 1 roll or 2 rolls for averaging a nukes damage given miss/crit/hit probabilites?

 

TheOnly

Argh, my infinite sums are useless. Wait, no they aren't!

Yeah, 1000 casts of corruption, expected to resist 170 of them (at 17%) in the long run. However, that is not the question we are asking.

We want to know the average time it takes to cast 1000 corruptions successfully. In the 17% resist example above, this isn't just 170 more casts, those extra can resist.

But to fill out your cycle and maintain the DoTs, you want to know the time it takes for 1000 successful casts.

This time is represented by the infinite sum, and ends up being

CastTme * 1000 ( 1 / ( 1-resistRate)) = CastTime * 1000 *1.205 [for a 17% resist rate]

or 1807 seconds for 1000 successful casts instant corruption without lag delay, on average.


What is the average resist rate? 17%
What is the average increase in time to successfully cast a spell at 17% resist rate? 20.5%

 

TheOnly

Spells are one roll. Melee with their yellow damage have some interesting and disputable effects. Casters have pretty much proven the one roll system long ago for direct damage nukes. For DoTs, I don't recall any big proof, but considering there is only hit, and 2 kinds of resist (and target school resist is negligible for miss on non-binary spells) it wouldn't matter anyway.

 

TheOnly

Excellent!

For both the imperfect refresh and the 'change of plans' just having a number in there, imperfect as it is, helps a ton. If we can answer simple questions such as:
"How much DPS do I lose if I am an average of 2 seconds late on a refresh versus 1 second late" that is a huge improvement. If the difference is large, we will need a better model to get closer to the actual value. If the difference is not that large, the imperfect model is fine.

I have tried a few things out with it and it seems that the first second of DoT gap is the largest, with each successive second getting smaller and approaching the filler spell dps in the long run. For my gear/spec, the first second loses 1.5%, then 1.3% for the next. Good to know that my DPS is only slightly nerfed when various things get in the way of refreshes.


As for "the next stat" I just double checked it. In the version I had before it was definitely broken. In this version, it looks great! (before, it claimed +fire was as good as +shadow for affliction...)



Possible suggestions to add later, that really aren't that important (A.K.A minor nitpicks):

"global resists". Mobs at level 60 had +8 to all resists per level higher than you. This equated to 2% total damage lost per level higher back in WoW 1.12. Inidcations are that this unmitigatable resist (penetration gear does nothing) still exists, but it is unclear if it is 8 all resist per level or slightly higher to adjust for the new level cap. Either way, this is relatively unimportant because it primarily causes partial resists for all spells rather than full resists. Partial resists would eat away dps equally for all talent specs and situations.

Link the "casted curse" section with the "other" section. Primarily, if "Casted Curse" is CoE or CoS it should automatically assume the spell is active, based on a setting for your talents in Malediction. Toggling between CoA and CoS to compare dps takes changing two sections rather than one in the sheet.

The "Others" IMO should be a "Casted by Others" section. Set once and forget, as orthogonal to the talent stuff as possible. If I choose my casted curse to be CoS or CoA I don't want to change that section to reflect my change.


This 'lock spreadsheet is extremely powerful, definitely my favorite!