Vontre's Mage DPS Spreadsheet - Page 11

Tarrke · 09/11/07, 4:13 AM

Thanks a lot for this piece of information Roywyn, just have now to play with it

◊ Kalman · 09/11/07, 11:14 AM

Originally Posted by Roywyn

I downloaded what I thought was the latest version, it was 2.6.2. Guess I fail at downloading

I'll check again tomorrow, too sleepy now.

Another thing though:
I toyed around a bit with an arcane (okay, 40arc/18fire/3frost, still arcane blast rotation spec) to see how much mana/intellect benefits me. But there isn't much to see, or nothing measurable:
Your sheet checks your available mana and fishes up the rotation that does most damage while still fulfilling the mana constraint.
If I add only some mana (int/spi/mp5/whatever), nothing changes. If I add more, I'll eventually get to another rotation, making my damage leap at one point. That's kind of expected

How about adding one more easy calculation in the cycle sheet:
For any maximum sustainable rotation (like 4AB, AM, Sc), the next entry to the right is 1 AB more (5AB, AM, Sc). It is not sustainable, but a combination of those two cycles is sustainable.
It only takes one line of computation to find that you can do x% of (4AB,AM,Sc) and 100%-x% of (5AB,AM,Sc) which will leave you at 0 mana at the end.

That way, one can see damage increases even small increments of mana.

( The 2-cycle theorem states that the best damage is the use of 2 cycles. Those will most likely not be a*AB/(X for 6.5s+) and (a+1)*AB/(X for 6.5s+), but a*AB/X and a*AB/Y, so what we get is not exactly the optimum thing. But still, what we would calculate is closer to the maximum output since we manage to burn some excess mana. )

There's an additional point to be made here: any cycle >3AB is equivalent to a 3AB cycle plus a certain amount of AB spam (this is ignoring time-restricted effects like Bloodlust or trinkets, or averaging them across the board). So the 2-cycle theorem in this case is actually telling us that our low-burn is a 3AB rotation and our high burn is AB spam to empty the remaining mana pool.

It's the same idea, but may be easier to implement.

(Simple proof of this point: All ABs >3 have the same time/mana cost. AB spam has this time/mana cost per AB as well. Thus, all ABs >3 are time-invariant with respect to damage/mana. So we can relocate them in time into a blast spam section without changing our mana constraint or our damage goal.)

◊ Vontre · 09/11/07, 12:55 PM

Originally Posted by Tarrke

Hy Vontre,

first thanks for your hard work but i was used to use an old version of your spreadsheet and just download the last version. In this one it seems that the stam bonuses of my spellstrike pants and hood obtained throught gemming are missing.

I can understand that you just decided that this stat isn't revelant when it comes to DPS and other stat calculation and if so i'd just like to know it. To me this stat is revelant as I like to upgrade bit by bit my stam in order to get some survavibility in raids.

Thanks for answer and appology if this was debated beteween the pages of this thread i've read.

Me too actually, I definitely consider stamina an important pve stat to maintain, but the spreadsheet in particular is only designed to calculate dps.

In regards to the 2-cycles, I certainly plan on visiting this in the future but it is unlikely it will be part of the spreadsheet. I'm working on a replacement theorycrafting simulator which will run free on a website, in order to expand my options. Some of you may have noticed my calculations are getting a little too big for Excel (and my armory import doesn't work =p).

No ETA right now, it'll take some time to redo stuff in php. It'll be ready when it's ready.

◊ Vontre · 09/12/07, 4:34 PM

I threw up a new version of the spreadsheet, this has some accumulated minor fixes, and a new "bloodlust uptime" option that I should've included a long time ago. One bloodlust in a 5 minute fight would be 15% uptime. That's pretty much it.

◊ manly · 09/14/07, 12:12 AM

I'm surprised you put a bloodlust uptime. I would rather have had assumed you would select the number of recieved bloodlust (1, 2, 3, etc.) and then count that in relation with boss kill time to know bloodlust_time%. I think it is 'safe' to assume we always get a full duration out of bloodlust. But then again I am picky on detail :P

Roywyn · 09/14/07, 5:26 PM

TLDR Version:
Fully improved and empowered talents work as follows:
Frostbolt: 82.2857% = (((3/3.5*0.95) {base 81.x%} + 0.1 {empowered}) * 0.9 {improved})
Fireball: 103.5% = ((1 {base 100%} + 0.15 {empowered})* 0.9 {improved})
AoE coefficient is 0.5, consistend for all mage spells.
Some Waterbolt testing.
Note to Kalman about odd cycles/rotations.
TLDR End.

Hey Vontre (and all other mage TCs),

first a quick note about waterbolt. In your sheet, this line is for the bonus damage on waterbolt from your spell damage gear.

=G447*'Game Stats'!$B$93+WENN('Main Sheet'!$B$47="yes";'Game Stats'!$E$37;0)+F$8+WENN('Main Sheet'!$B$27="yes";'Game Stats'!$E$14;0)

This looks pretty messy to me:
(fireballdamagebonus*WEmultiplier + ((elixir of frost power)) + frostdamage + wrath of air totem).
I'll hop back to the PTR to check the coefficents, it should be something like this:
((damage+frostdamage)*Water_Elemental_multiplier + wrath of air totem{The elemental gains double benefit, right? The totem buffs its damage directly and indirectly because you get buff and increase the elemental's damage})*Waterbolt_multiplier)

Went back to the PTR and checked out waterbolt:
Tooltip at level 70: 224 mana, 2.5s cast, 486-558 damage (522 average, 72 delta) (note: wowwiki has a typo, top end damage is not 588 but 558)
(For some reason, you entered the base damage as 464.5 in your sheet)
Test with +0 spell damage (no spell school damage either): 486-557 damage on waterbolt. So we know it has no hidden scaling with your/your pet's intellect/spirit/whatnot.
Test with +1000 damage/frost damage (had another +50 arcane/fire fromSunfire):
764-835 damage (71 delta), that means +277-278 damage from gear, i.e. 27.7% +/-0.2% from your gear is added as damage.
Assuming waterbolt has a 2.5/3.5 coefficient and the elemental gets 40% of your spell damage, we'd get a coefficient of 28.57%. Close but not exact. 40% WE, 69.5% WB would work, but makes no sense. 39% WE, 2.5/3.5 WB isn't any better. I don't know what else it could be.

Then I spent half a day on the PTR (again

...) to test some stuff. I wanted to find out the current AoE coefficients, since some of the wowwiki data is outdated Spell Damage Coefficients - WoWWiki, the Warcraft wiki What I stumbled over though was that I have always miscalculated the improved/empowered talents.
The empowered talent is added. The improved talent is a multiplier, and is applied after the empowered talent is added. So, for fully specced talents, the coefficients are as follows:

Frostbolt
(((3/3.5*0.95) {this is the base 81.x%} + 0.1 {if 5/5 empowered}) * 0.9 {if 5/5 improved}) = 82.2857%

Fireball
((1 {the base 100%} + 0.15 {if 5/5 empowered})* 0.9 {if 5/5 improved}) = 103.5%

I did the frostbolt test myself on the 2.2 PTR and can guarantee that it works that way.
Fireball is annoying to test (it has a 172 damage delta between high and low) and someone else did it before, the link linked from wowwiki is
WoW Forums -> So I chaincast frostbolt on the PTR today...

What I actually wanted to test were the AoE coefficients. The AoE penalty now is 1/2 instead of 1/3, which it was before. So, the coefficients are:

Arcane Explosion: 21.46% = 1.5/3.5 (instant) * 0.5 (AoE)

Frost Nova: 4.2857% +/-0.1% = 1.5/3.5 (instant) * 0.5 (AoE) * 0.2 (special multiplier)
Cone of Cold: 19.2857% +/-0.3% = 1.5/3.5 (instant) * 0.5 (AoE) * 0.9 (special for slow)
Blizzard: 114.2857% +/-0.4% = 8/3.5 (8 seconds channeled) * 0.5 (AoE), 14.29% per tick

Blast Wave: 19.2857% +/-0.2% = 1.5/3.5 (instant) * 0.5 (AoE) * 0.9 (special for daze)
Dragon's Breath:Cone of Cold: 19.2857% +/-0.3% = 1.5/3.5 (instant) * 0.5 (AoE) * 0.9 (special for disorient)
Flamestrike - the damage on the base spell is split 55% direct damage - 45% damage over time (+/-1%, couldn't figure it out exactly)
Flamestrike (direct): 23.57% +/-0.2% = 3/3.5 (3s cast time) * 0.5 (AoE) * 0.55 (split)
Flamestrike (over time): 12.00% +/- 0.3% = 8/15 (8s DoT, 15s is the normalised DoT duration for calculation) * 0.5 (AoE) * 0.45% split, 3% per tick

I did the test by not picking any damage multiplier talents, note the tooltip damage of the spell and note the damage delta (difference between minimum and maximum). I then tested the spell on mobs until I got a similar damage data of my test points (fixed +damage gear, no procs, no buffs, no debuffs, etc.) with ~1k damage gear. In most cases, my test data delta was 1 less than the tooltip delta, but it was larger for some spells that are horrible to test (self-centered AoE with cooldowns are not fun), those have a higher listed error margin in the list above.

Edit: To Kalman

Thanks for clearing up the cycles.
I think that actually, the best/only(?) way to optimise is calculate "AB spam" and all "X*AB/6.5s filler" cycles for X=0,1,2,3. If all the "X*AB/6.5s filler" cycles are sustainable (they should be for most fight with a support group), then the maximum should be part "X*AB/filler" and part "AB spam". For each X and each filler, the "AB spam" ratio is easy to compute (excess mana/mana burn difference between AB and the cycle).

3*AB/filler isn't always going to be your low-burn rotation though: Take a fire heavy spec wit 2/5 T5, and maybe toss in high hit/crit and fire damage, and make it a short fight (or have very high regen rates). Then your (1?),2,3*AB/2*Fireball cycles will be inferior to Fireball spam damage and mana wise.
Your optimal rotation then could be Fireball spam as DPM cycle and AB spam as burn cycle.
It's a bit counterintuitive and can only happen in very skewed/biased settings where only the 1.5s AB beats FB DPS.
Just some food for thought about what may happen in those cycles. (I'm not even sure if my example is correct or if I'm wrong at some point there.)

◊ Vontre · 09/15/07, 12:45 AM

Thanks for the info Roywyn. Looks like that coefficient nerf is a little worse than we thought. Blizzard is good at consistency... add these, multiply these. Bleh.

Kulehan · 09/15/07, 10:27 PM

I know you said previously in the thread that the Mark of Defiance proc was something you weren't including due to it not being very effective, but hear me out. From personal experience and watching WWS logs from several guild kills recently, I think it's worth looking into. Over a 9 minute Leo fight I gained 1,962 mana back from the proc, and on a 6.5 minute Gruul (yeah, that was a rough one) I also got 1,929 mana back. It's largely based on luck and total number of casts due to what seems to be a very short internal cooldown, but all the same, nearly 2,000 mana for a single fight and roughly 16,000 over the course of a night's raid is pretty significant.

Also, I may be wrong here, but trying to put the trinket on in the gear selecter doesn't appear to add any bonus at all to spellpower. Best I can tell is that both items named "Mark of Defiance" in the trinket list add 0 stats. At least factoring in the pure spelldamage benefit from the trinket would be nice if you're set on not including the mana calculations.

One last thing that would be nice is if there was a quicker way to compare side-by-side how different specs and gear combinations would affect your overall dps. You can see now how you'd do with off-spec nukes and such, but it really doesn't give a nearly accurate representation of how, for example, 10/48/3 with fire gear would compare to 10/0/51 with your available frost gear unless you go in and manually spend 30 minutes to reselect the appropriate gear. I guess what I'm looking for is maybe a way to save gear profiles on the manual gear input page. Pick out each piece, gems, etc, then at the bottom have a dropdown to save the setup as a profile, and one to load existing profiles. Same for talents/spell rotations. I'm no grand Excel master, so I'm not sure if this might be beyond the scope of what can be done, but it's just a thought I had. I suppose I could also just make a second and third copy of the spreadsheet and use one for frost stats, one for fire, etc to accomplish the same thing

skelie · 09/18/07, 12:56 PM

first time posting, avid user of the sheet. many thanks to you guys (Vontre, Kalman, and other contributors), ive been getting my guild mages hooked on using it.

ive been working today on trying to get the armory import working over excel 2007, but from everything im viewing with Armory over IE7, blizzard has an XML error on the main page for the stats that is blocking this whole thing from working properly. the error message i receive when i try to do the import is identical to what i see at the bottom of the page:

The XML page cannot be displayed
Cannot view XML input using XSL style sheet. Please correct the error and then click the Refresh button, or try again later.

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

XML document must have a top level element.

could be down at the moment with server maintenance, but i'll keep an eye on it. all the other pages (rep, skills, etc) are working fine with importing into excel.

edit: view source in IE7 works. just copy and paste everything between the page tags into the E19 cell on the armory tab and it will import. it just wont be automated for updating, you'll have to do it manually.

Zookmottin · 09/20/07, 3:37 PM

1st thanks for all this work you have done. I am a 1st time user of it. I am trying to important and cookie cutter deep fire build and I past the url in that box but nothing updates when I click away.

Is there something special I need to do or click on?

Stein · 09/20/07, 3:49 PM

On the talents tab, make sure the "Manual Talent Entry:" is set to no.

Zookmottin · 09/20/07, 4:21 PM

Originally Posted by Stein

On the talents tab, make sure the "Manual Talent Entry:" is set to no.

I must be missing something I see the talents box but there no drop down for yes/no. hmm

aha ha! I see now TABS man I should have paid more attention in excel classes lol

◊ Vontre · 09/21/07, 2:28 PM

Thanks for the input Kulehan. For now I don't have any plans to put any more major feature updates into the spreadsheet. I'm working on a website with a transparent php background coding for an entirely new theorycrafting mechanisms. Code is much more versatile and will allow me to more easily do the things you said, like save profiles for comparisons. It will also be more accurate, as I can get working models of combustion and better simulations of trinket usage in a real situation.

I'll still be releasing spreadsheet updates to address bugs and accuracy issues.

Undeadmerlin · 09/21/07, 8:17 PM

Maybe I'm just not seeing it, but I've been trying to figure out how to import my armory link into this spreadsheet. Some insight would be great.

Thanks for your time.

◊ Vontre · 09/21/07, 10:12 PM

See the first post in the thread, I just updated it.

thessaly2 · 09/23/07, 11:05 AM

I'm having the same Armory import mystification. I'm a noob to WoW class spreadsheets but not Excel in general.

1) I am using Excel 2000

2) I can successfully import my Armory link into the Armory tab. This updates my stats *on the Armory tab* and does refresh dynamically.

Where I become confused is trying to get my armory link imported anywhere else. I would like to use it to populate the Gear tab. I select "Armory Import" from the dropdown at the top of the Main Sheet tab, and then...I have no idea what's supposed to happen from there. The Armory Import data area immediately below looks blank, but clicking on the individual cells seems to show default values (i.e., Intellect (unbuffed), followed by 400, which is not my INT). The Gear tab remains unchanged with the items that come defined with the sheet.

I feel like I'm missing a step here (or an additional place to enter the armory URL) but I just can't figure it out. Thanks in advance, and sorry if this is ridiculously stupid.

EDIT: Think I figured it out, but leaving this here for posterity. Is the point that once you have successfully imported via the Armory tab, the Gear tab becomes unnecessary?

◊ Vontre · 09/23/07, 9:32 PM

Originally Posted by thessaly2

EDIT: Think I figured it out, but leaving this here for posterity. Is the point that once you have successfully imported via the Armory tab, the Gear tab becomes unnecessary?

Correct

◊ Vontre · 09/24/07, 1:34 PM

v2.7.2 is up

First, I fixed the water elemental calculations. Second, I re-evaluated how cooldown abilities are handled. Basically the original formulas for uptime were duration / cooldown, aka Icon of the Silver Crescent had an uptime of 20/120. The problem here is that this method is counting the worst possible scenario. DPSing for more than 120 seconds, or less than 120 seconds, would result in much better uptime. So in order to get a more meaningful uptime, Blackpatch helped me derive a formula for average uptime based on an approximated duration. Please note that the new uptime percentages are not based on an exact length of a fight, they expect an approximation and offer one in return.

In short, cooldown timers are actually better than we thought because they can be activated immediately when the fight starts, thus on shorter fights they typically have a much higher uptime than given by the standard duration/cooldown formula.

• Nurru · 09/24/07, 1:39 PM

Have you looked into the difference between summoning the pet only when active damage trinkets are active vs not?

◊ Vontre · 09/24/07, 1:58 PM

Originally Posted by Nurru

Have you looked into the difference between summoning the pet only when active damage trinkets are active vs not?

You mean stacking Icon with the pet? I haven't modeled it, but I haven't modeled it stacked with AP or Combustion either. The effect is typically pretty negligible to overall dps, in particularly water elemental which lasts a whole 45 seconds compared to the 15-20 on most trinkets.

Last time I checked, though, delaying your Icon by a full minute to stack with AP wasn't as effective as just popping either timer as soon as they were up, so I doubt the effect on water elemental will be worth it either.

vokzhen · 09/24/07, 6:19 PM

I might be mistaken, but don't pets such as Shadowfiend and Water Elemental use your stats at the time of casting, and continue to use those stats until they die rather than until the trinkets go down? So, for example, getting Icon, Eye of Mag, Spellstrike, and so on all at the same time would give an increase over the entire duration of the WE, and do much more damage than if each of them were to proc while the WE was active?
I might be completely mistaken about this, I guess I assumed that's the way it worked with popping trinkets before Shadowfiend. Guess I haven't really payed attention to that since I assumed that's how it worked.