DPS Excel calculator for Sorcerer / Sage

[lostdummy]

(01-13-2012 09:24 AM)CaseyTheRetard Wrote:  That explains our miscommunication; I was assuming the first three "buff to xxx" items were there to model the three major class buffs that increase crit chance, bonus damage, and stats.

To my knowledge there are no static/passive buffs to force damage or casting speed.

I changed (in ver 1.38) 'buff % to force damage' to 'buff % to bonus damage', to cover buffs like 'Unnatural Might' , as per your comment - which also brings it to what Kor said in last post. Now SI,IA and SW class buffs should be covered with those 3 'buff %'.

I'm only not certain if Coordination (buff % to crit) increase crit rating stat on player info, or only crit % stat. I assume it does not change crit rating, so it is safe to copy player stats to excel while having that buff - unlike Mark of Power, which change willpower stat and should either be removed while reading willpower stat for excel, or should set 0% to 'buff % to willpower' if already included in willpower. But I guess Mark of Power is only % buff that directly change basic stat that is used in excel (willpower) - I assume other two buffs change derived stats (bonus damage and crit%).


BTW, my tests show that 5% buff to willpower from Mark of Power (MoP) works on willpower without 6% from Will of the Sith (WotS) . In other words, if you cast MoP on yourself (and you have WotS talent), then your willpower will not increase by 5% - it will increase some 4.7%.

Data suggest that instead of TotalWillpower = (base+gear willpower) * (1+ WotS%)*(1+MoP%), actual formula is:

Total Willpower = (base + gear willpower) * (1+ WotS% + MoP%)

[CaseyTheRetard]

Yes, Coordination is a 5% buff to crit - it does not buff rating.

(01-13-2012 10:26 AM)lostdummy Wrote:  BTW, my tests show that 5% buff to willpower from Mark of Power (MoP) works on willpower without 6% from Will of the Sith (WotS).

Data suggest that instead of TotalWillpower = (base+gear willpower) * (1+ WotS%)*(1+MoP%), actual formula is:

Total Willpower = (base + gear willpower) * (1+ WotS% + MoP%)

I've never looked, but it's certainly reasonable that two % stat buffs would stack additively. Also, the S_WillPower formula is not including the effect of MoP on the delta used to calculate stat values. Should probably be

=ROUND((G18+ 'Stat values'!E5) * (1 + T_WillOfSith + G26),0)

to fix both issues.

[lostdummy]

(01-14-2012 12:51 AM)CaseyTheRetard Wrote:  Also, the S_WillPower formula is not including the effect of MoP on the delta used to calculate stat values. Should probably be

=ROUND((G18+ 'Stat values'!E5) * (1 + T_WillOfSith + G26),0)

to fix both issues.

Yes, I already noticed and corrected that after my post above - only made it after version 1.38 was posted, so will come with 1.39 ;p

[lostdummy]

Added to next version ability to model 'DPS breaks', ie periods during figth when we can not do DPS from various reasons.

I noticed that on number of boss fights in EternityVault that I have such breaks (like hiding behind turrets on entry robot, or runnig from island to island and knockbacks on Gjarl etc).

And while it generally hold true that if one build have better DPS than other in 'ideal fight', it should also have advantage in fight with such 'DPS breaks', there are certain spells that are less affected with such breaks (like DoTs), and if some build can use significantly more such spells, it can be advantage. Also some force limited builds could benefit more from additional force regenerated during break .

But I remember when I was adding similar function to simcraft for wow, that different DPS breaks behave differently - it needed diffferent handling in sim code, but unfortunately it also need different formulas in excel. Example 'DPS break' types:
1) hiding : you can not cast during break, but DoTs can tick.
2) running: you can cast insta spells (unless they require facing boss), DoTs can tick.
3) invulnerability: no damage can hurt boss in this period

So far I only did math for 1st version of DPS breaks, 'hiding', and it shows that it is not only DoTs that can benefit builds in such break. Any build can 'benefit' (ie be less affected by DPS break) if it use lot of low frequency, high damage spells. Examples are DoTs, long CD spells (Crushing Darkness, Thundering Blast), but even normal spells used infrequently are less hit by breaks.

In fact, in 'hiding breaks' DoTs are no different than identical spell that does all damage on initial hit, but have CD same as DoT. In other words, spells with such long CDs (Crushing Darkness, TB) and DoTs all share same 'benefit' - they will not 'lose' all time spend during DPS break, since some of that time would be anyway lost waiting for their CD to expire.

After some bit of math, I came with following formulas to express effect of DPS break on number of spells we can use:

if ( pb* Ns> Nb) , or duration of break longer than spell period (CD, duration):
Ns2= Ns*(1-pb)+ Nb/2
else
Ns2= Ns-(Ns*pb)^2/2/Nb

where 'Ns' is number of spells used in fight without break, 'pb' is percent of time spent on break, 'Nb' is average number of breaks during fight, and result 'Ns2' is number of spells that can be used with breaks.

As it show, there is no use of 'CD' or spell duration in above formula, only number of used spells, which relate to frequency spells are used in fight - regardless of reason , be it CD, DoT duration, or simply spell used less frequently.

Another thing it shows is that number of breaks does matter. If for example 20% of time is spent on breaks, it si not same if it was 3 long breaks or 12 short breaks - since short breaks affect DoTs and long CD spells even less. And both formulas reflect that more Nb result in more available spells during break.

For fillers without CD, like LS or FL, there is actually no need to use above formulas, since for them all remaining time (when also breaks are removed) will be used - and reduction of that remaining time due to break will also reduce number of possible filler spells. But for DoTs , spells with CD, and even spells used on procs, above formulas can be used.

Interesting thing is that when I plugged above formulas in excel, I got expected reduction in overall DPS when I set some example DPS break ( I set as inputs 'break repeat time' and 'break duration', so I can calculate both percentage of time under break, and number of breaks). I also noticed change in relative damage% for spells in rotations ... spells with longer CDs have increased % of damage they contribute to total DPS.

But I did not get any significant change in relative build DPS even for large breaks ( 1/3rd of figth spent in breaks) - without breaks, it was for example relative DPS at 100% balanced : 98% Madness : 97% Lightning , and with such breaks it is 100%:98%:95% - indicating that Balanced/Madness fare slightly better in DPS breaks.

BTW, I'm still checking if I implemented above correctly, or if formulas are ok, so numbers may change. Also I will later on check how formulas look for other two DPS break types.

For example, 'Invulberability' breaks will have opposite effect on builds with DoTs - in such breaks DoTs are slightly penalized, since they are canceled mid-duration. And while total damage of all spells in such case is easy to calculate (ie it is pb* Damage without break), force usage for DoTs will increase since number of DoTs will deal only part of damage - ie DPF of DoTs will reduce. But that can have effect only on force limited builds, and seeing above results for 'hiding', I do not expect big changes.

[Kaedis]

Hmm...might want to differentiate between "Invulnerability" and "Invul + debuff wipe". A short version of the former just kills a couple DoT ticks, while the latter actually cancels them min-duration.

[lostdummy]

(01-15-2012 01:48 AM)Kor Wrote:  Hmm...might want to differentiate between "Invulnerability" and "Invul + debuff wipe". A short version of the former just kills a couple DoT ticks, while the latter actually cancels them min-duration.

True - that would be 4th type.

For now I posted new version (1.40) where I model this 'hiding' type of DPS break. Problem with all those DPS break types is that is is fairly complex to model them accurately in excel (and was even for sim), and in the end they often do not change what talent or build is 'best', and especially do not change much results between two types of DPS break. But if I don't find something more interesting to add to excel, I'll eventually model also other break types, just to check if they really give similar results ;p

[lostdummy]

Posted new version (1.41) - this time in attachment to original post, since Save File hosting (that I used so far) seems to have some issues.

Some of main additions to new version are support for latency and raid buffs like Adrenals/Stims, and debuffs like -20% armor. This makes it easier to test raid or non raid DPS without need to reenter stats, and also makes it easier to compare to simulation results.

[DPSFTW]

Hi, thank for the spreasheet, but it doesnt work very well with Openoffice i keep getting errors when clicking on dropdown menus.

Also can anyone answer me the following?

How much crit = 1%?
How much Will = 1%?

[frmorrison]

(01-20-2012 03:09 AM)DPSFTW Wrote:  Hi, thank for the spreasheet, but it doesnt work very well with Openoffice i keep getting errors when clicking on dropdown menus.

Also can anyone answer me the following?

How much crit = 1%?
How much Will = 1%?

You need to get Excel 2007 or higher for it to work without errors.

The answer is it depends, you need more crit to get another 1% as you get more of it.
Willpower provides power and crit (not it provides a lot less crit than crit rating). If you want to see the formulas, there is a thread on it.

[lostdummy]

Posted new version (1.42)

Main addition is support for Republic (Sage) names for talents/skills, spells and companions.

Also renamed default 'Balanced' build to 'Hybrid' ... since apparently Sage has Balance tree , and what I named 'balanced' was intended as balanced mix between Madness and Lightning - so 'Hybrid' is better name for that default build.