Leulier's Warlock DPS Spreadsheet - Page 10

Bakka · 02/03/09, 11:58 AM

Latest and greatest is up: WoW Warlock DPS Spreadsheet

duhwhat · 02/03/09, 1:09 PM

3.08b is broken (#NAME? errors in all dps fields) for me using Excel 2003. Version "a" was working just fine yesterday.

Drison · 02/03/09, 1:42 PM

Originally Posted by duhwhat

3.08b is broken (#NAME? errors in all dps fields) for me using Excel 2003. Version "a" was working just fine yesterday.

Getting the same errors as well using excel 2003.

Trickykid · 02/03/09, 2:49 PM

Originally Posted by Drison

Getting the same errors as well using excel 2003.

Originally Posted by release notes from newest version

Prevented cyclical refs from erroring out through use of IFERRORs

These are not supported in excel 2003, but you can follow this post to create your own function that mimics it:
IFERROR for Excel 2003

Bakka · 02/03/09, 3:19 PM

Originally Posted by Trickykid

These are not supported in excel 2003, but you can follow this post to create your own function that mimics it:
IFERROR for Excel 2003

Woo, I break yet another spreadsheet! Go me! :p

Drison · 02/03/09, 3:30 PM

Originally Posted by Trickykid

These are not supported in excel 2003, but you can follow this post to create your own function that mimics it:
IFERROR for Excel 2003

Im total noob when it comes to excel. is there just 1 area i put this fuction in or do i have to do this function in every field that has this error?

Trickykid · 02/03/09, 3:34 PM

Originally Posted by Bakka

Woo, I break yet another spreadsheet! Go me! :p

Only for the luddites and people who don't work in a cube farm... Works for the rest of us!

EDIT:

Originally Posted by Drison

Im total noob when it comes to excel. is there just 1 area i put this fuction in or do i have to do this function in every field that has this error?

The post I linked should go through that, but basically you're just adding a macro that defines the function (that works in Excel 2007). I believe it's alt 11 by default to pull up the macro window (or Tools> Macro>VB editor), insert module, and then paste:
Function IfError(formula As Variant, show As String)

On Error GoTo ErrorHandler

If IsError(formula) Then
IfError = show
Else
IfError = formula
End If

Exit Function

ErrorHandler:
Resume Next

End Function

Drison · 02/03/09, 3:44 PM

Ok i did it right but still getting the errors so not sure what im doing wrong.

Rozzenwyn · 02/03/09, 4:20 PM

Originally Posted by Bakka

Latest and greatest is up: WoW Warlock DPS Spreadsheet

Awesome!!! Thanks Bakka.

Trickykid · 02/03/09, 4:41 PM

Originally Posted by Drison

Ok i did it right but still getting the errors so not sure what im doing wrong.

Okay well that macro makes the function IFERROR work, but apparently when you open the file and it didn't know about it it leaves the functions as "_xlfn.IFERROR". You need to find/replace those from that to IFERROR.

They show up on hidden tabs, so you will need to unhide all the tabs and then do a find/replace.

svirfneblino · 02/03/09, 5:39 PM

Added VBA function. Unhid all tabs and did Find/Replace. Replaced 322 instances of _xlfn.IFERROR with IFERROR function, but it still has #NAME errors. Any other ideas?

Trickykid · 02/03/09, 6:30 PM

Originally Posted by svirfneblino

Added VBA function. Unhid all tabs and did Find/Replace. Replaced 322 instances of _xlfn.IFERROR with IFERROR function, but it still has #NAME errors. Any other ideas?

Do you have GoC/NF turned on? Pretty sure this is the source of the remaining problems. Try turning it off and then back on (set B32 to 0 and then back to 1).

Drison · 02/03/09, 6:39 PM

Your the man looks like its working to me.

svirfneblino · 02/03/09, 6:51 PM

Originally Posted by Trickykid

Do you have GoC/NF turned on? Pretty sure this is the source of the remaining problems. Try turning it off and then back on (set B32 to 0 and then back to 1).

Fixt. Thanks a lot.

nenad · 02/04/09, 7:56 AM

Nice, now I don't have to reenter all those elements for FG ;p

But I still notice few potentially incorrect things for Meta. Here they are in some order of importance:
1) Demonic Pact is incorrectly affecting directly N3 (*(1+(demo_pact*0.02*Models!B12))), thus increasing total spell damage for ~300 more than real. Problem is that Demonic Pact do not stack with Totem of Wrath, and right now if both are checked they will both add. Fixing this will decrease shown max Meta DPS for ~300 DPS
2) Immolation Aura is still missing, even if that can be ~100 DPS increase
3) Meta modificator still does not take into account using Meta during BL, or improved effect on short fights like Patch, or fights that end just after 2nd Meta is applied. That can increase DPS for ~60 DPS

Some way to fix those:
1) put Demonic Pact as additional row in Raid Buffs, and take MAXof(DemonicPact,Totem of Wrath,DivineSPirit)
2) add new Immolation Aura spell row under Felguard on DPS sheet
3) create meta_mult coeficient that use above things into consideration

I did all 3 of those in example XLS that i posted in #206, and I explained steps for meta_mult and ImmolationAura in post #222.

Net result of all those changes is that they would slightly lower expected Meta/Ruin DPS (some 150 DPS).

• Zakalwe · 02/04/09, 9:30 AM

[Edit: Deleted. I missed something obvious.]

Abidos · 02/04/09, 12:59 PM

I still find two mistakes on the spreadsheet
- Demon values don't represent the last patch changes, I believe Whitetooth has posted the correct ones in post 152# of this thread
- Demonic empathy is not triggerd by auto-attack crits, only by the demon's abilities and spells.

Bakka · 02/04/09, 2:35 PM

Originally Posted by Abidos

I still find two mistakes on the spreadsheet
- Demon values don't represent the last patch changes, I believe Whitetooth has posted the correct ones in post 152# of this thread
- Demonic empathy is not triggerd by auto-attack crits, only by the demon's abilities and spells.

The numbers White posted were based on PTR values. I don't perform changes based on PTR values. If someone wants to confirm they are all accurate and repost them then I'll update them.

Will investigate demonic empathy further, thanks.

nenad · 02/04/09, 3:44 PM

Few more comments:
- based on some posts in Demo thread (posts #438-#441) I changed base melee pet attack to start with (412.5+fg_ap/7)*1.05 , instead of old (597+fg_ap/7) . Same start should be for all melee pets, only coeficient is different (same 1.05 for Succy, but 0.80 for FH and 0.86 for VW).

- STA field for pets show too low value. I don't know what is real formula, but changing coefficient from 0.3 to 0.7 shows much closer numbers. Maybe someone can come up with real formula, but until then I would suggest changing to 0.7 and coloring it orange. That result in closer values for DK

I included those changes in warlock_dps_demo.xls, and also fixed that DemonicPact stacking issue and added Meta+ImmoAura.

I also included there one feature that I would really like to see in next version of XLS - 0/1 switch for all raid buffs (H22). Whenever I test something at target dummies, I dont have all raid buffs and without that switch its really slow to find all places to turn off ;p

nenad · 02/05/09, 7:04 AM

After intensive testing, I think I got exact formula for pets STA and INT:

STA= (baseSTA+0.75*PlayerSTA*(1+0.05*synergy)+Fort+GoTW)*(1+fel_vit*0.05)*(1+0.02*BoK)
INT= (baseINT +0.30*PlayerINT*(1+0.05*synergy)+ AI +GoTW)*(1+fel_vit*0.05)*(1+0.02*BoK)

For FH, VW, Succ and FG baseSTA=328 and baseINT=150, so XLS should be:
B32=B41=B50=(328+0.75*stam*(1+0.05*synergy)+C9+C11)*(1+fel_vit*0.05)*(1+0.02*C12)
B33=B42=B51=(150+0.3*int*(1+0.05*synergy)+C10+C11)*(1+fel_vit*0.05)*(1+0.02*C12)

For Imp baseSTA=118 and baseINT=369, so XLS should be:
B26=(118+0.75*stam*(1+0.05*synergy)+C9+C11)*(1+fel_vit*0.05)*(1+0.02*C12)
B27=(369+0.3*int*(1+0.05*synergy)+C10+C11)*(1+fel_vit*0.05)*(1+0.02*C12)

Those numbers give exact INT and STA that I could test unbuffed.

Edit: I tested buffed (BoK, MotW) and formula is confirmed for buffs - although I tested while afflic again, so not 100% sure if Fel Vitality multiply buffs also.

nenad · 02/06/09, 7:12 AM

Another issue that could be fixed are short term effects that could be used once (or few times) during fight. All of those have effects that significantly depend on fight duration. Because of that I propose that we rename "OOM time" to "Fight time" (cell should remaoi named "oom_time"), and use it for all such cases.

There is number of cases in different areas. One example are all Potions. They can be used usually once per fight, but effect should be based on fight duration. Now its fixed on 300sec, and with many fights now lasting 150sec it means real effect of those is almost double of what is shown. Solution is to divide with /oom_time instead of /300. For Potion of Wild Magic on 155sec fight it improve effect from 13 to 26.

Another , slightly more complex case, is when short term effects has CD shorter than some fights and can be reused more than once. For example, Demonic Empowerment is now "(15/60)*0.2" or around 5%. But in fight of duration of 135 sec it should be 6.7%. Similar case is with many effects/abilities on CD. For example on above Potion of Wild Magis, if its CD is 60sec it could be used twice in 155sec, so effect is 200*30/155= 39 ... much more than 13 !

Solution for that is to make function that take effect time, CD time, fight time, and return effect uptime percentage. I made such example function in XLS, in Alt-F11, new module3 :

Function uptime_perc(effectDurationSec As Double, cooldownSec As Double, fightDurationSec As Double) As Double
'Return uptime of effect, given its duration, cooldown and fight duration (in seconds)
    Dim full_N, remains, added_N As Double
    full_N = Int(fightDurationSec / cooldownSec)
    remains = fightDurationSec - full_N * cooldownSec
    If (remains > effectDurationSec) Then
        added_N = 1
    Else
        added_N = remains / effectDurationSec
    End If
    uptime_perc = (full_N + added_N) * effectDurationSec / fightDurationSec
End Function

It can be used on all those places, for example:
- instead of 15/60 for DE if field F41 (frequency for FG), use uptime_perc(15,60,oom_time)
- instead of 15/300 in Potions for WildMagic and others, use uptime_perc(15,60,oom_time)

It could even be used on more delicate trinket uptimes. Its bit tricky since it need estimated time for trinket to proc (usually based on your crit chances or number of casted spells etc). But if you have calculated such "TriggerTime", then uptime for trinket would be:

TrinketUptime%= uptime_perc( EffectTime, TriggerTime+CD, oom_time)

For example, Dying Curse is trinket with 15% chance to trigger on your casted spell and 45sec CD, and have effect lasting 10sec. One way to estimate uptime was to estimate TriggerTime (Models!$C$30/0.15) at 13sec, and say that its used once in 58sec (13+45), giving uptime of 10/58 = 17.24%, and estimated effect of 765*17.24% = 132dmg

But in fight of length 140sec, that should be instead 3xDyingCurse/140sec = 3*10/140 = 21.42%*765= 164 dmg!

Since difference is not small, correct way to calculate effect of trinket would be:

DyingCurseDmg= uptime_perc(10, Models!$C$30/0.15+45, oom_time)*765
SundialOfTheExiledDmg= uptime_perc(10, Models!$C$30/0.10+45, oom_time)*590

Those are just some of examples where more accurate numbers could be obtained by taking fight duration into consideration on timed effect.

Abidos · 02/06/09, 10:48 AM

After a few tests with the sparing dummys I confirmed that demonic pact does not take into acount the spellpower increase we get from our spirit with fel armour, only its static spellpower

Bakka · 02/06/09, 11:15 AM

Originally Posted by nenad

Another issue that could be fixed are short term effects that could be used once (or few times) during fight. All of those have effects that significantly depend on fight duration. Because of that I propose that we rename "OOM time" to "Fight time" (cell should remaoi named "oom_time"), and use it for all such cases.

I will never add any sort of fight duration to the spreadsheet. The entire spreadsheet is based around averages. It is not a real time dps calc and was never intended to be. The purpose of this sheet is not to exactly predict what sort of dmg you can put out for all situations but to help people get a good idea about what does what and how things interact with each other. People need to realize this and understand that with shorter duration fights, fights which require movement, etc. these numbers get skewed.

nenad · 02/06/09, 11:38 AM

Originally Posted by Bakka

I will never add any sort of fight duration to the spreadsheet. The entire spreadsheet is based around averages. It is not a real time dps calc and was never intended to be. The purpose of this sheet is not to exactly predict what sort of dmg you can put out for all situations but to help people get a good idea about what does what and how things interact with each other. People need to realize this and understand that with shorter duration fights, fights which require movement, etc. these numbers get skewed.

That is understandable, but fact is that spreadsheet already have "sort of fight duration", and it is called "oom_time". Which is good, since without that many calculations (like LT effects) would be meaningless. Also spreadsheet has many implicit/hidden fight duration constants, like "300" appearing in many formulas (potion effects for example).

Renaming already existing cell would not make spreadsheet deviate from being "based around averages". Even now when oom_time is changed from 360sec to some more expected value for Patch fight for example (~160sec), it change DPS numbers.

Problem is that without taking into consideration fight duration spreadsheet is giving wrong messages/answers to people who would like to use it. For example:
- should I use Potion of Wild Magic on Patch? XLS says 13 dmg - I should not bother... or?
- does Meta and Immolation Aura significantly influence my DPS on Patch? on XLS right now seems not - I should not bother ... or?

Saying that "People need to realize this and understand that with shorter duration fights, fights which require movement, etc. these numbers get skewed" is correct, but it is just listing limitations of spreadsheet. It is similar to saying "this model/spreadsheet is not accurate for different fight durations and movements" - which is true.

But with only little effort it could accurately model different fight times - which in my opinion would be significant since right now majority of DPS discussions and comparations is done based on Patchwerk fights, and those are significantly shorter than 360sec present in spreadsheet or 300sec implied in some formulas.

Trickykid · 02/06/09, 2:24 PM

Originally Posted by nenad

That is understandable, but fact is that spreadsheet already have "sort of fight duration", and it is called "oom_time". Which is good, since without that many calculations (like LT effects) would be meaningless. Also spreadsheet has many implicit/hidden fight duration constants, like "300" appearing in many formulas (potion effects for example).

Renaming already existing cell would not make spreadsheet deviate from being "based around averages". Even now when oom_time is changed from 360sec to some more expected value for Patch fight for example (~160sec), it change DPS numbers.

Problem is that without taking into consideration fight duration spreadsheet is giving wrong messages/answers to people who would like to use it. For example:
- should I use Potion of Wild Magic on Patch? XLS says 13 dmg - I should not bother... or?
- does Meta and Immolation Aura significantly influence my DPS on Patch? on XLS right now seems not - I should not bother ... or?

Saying that "People need to realize this and understand that with shorter duration fights, fights which require movement, etc. these numbers get skewed" is correct, but it is just listing limitations of spreadsheet. It is similar to saying "this model/spreadsheet is not accurate for different fight durations and movements" - which is true.

But with only little effort it could accurately model different fight times - which in my opinion would be significant since right now majority of DPS discussions and comparations is done based on Patchwerk fights, and those are significantly shorter than 360sec present in spreadsheet or 300sec implied in some formulas.

The primary use of the spreadsheet is to define what gear is worth to different specs. Secondarily it let's you decide (within a spec), where to put specific talents, glyphs, and which spells to use. In general the fight length will affect those issues identically. The main factors that contribute to DPS change when fight length changes are already included (life tapping and I believe Heroism). That said, if you really want to see those additional numbers change, it's only 1-2 cells that need editing.