Vontre's Mage DPS Spreadsheet - Page 10

◊ Kalman · 08/22/07, 4:56 PM

Originally Posted by Vontre

Major revision posted!!

First off, my own host. Thank you to Kalman for hosting the speadsheet all these months, I won't be eating your bandwith anymore.

http://www.homegirlsguild.com/mage_dps.zip

Awesome. Local version nuked, a redirect emplaced which doesn't work for reasons I am too lazy to deal with right now but will fix... eventually.

◊ Vontre · 08/22/07, 6:04 PM

Originally Posted by Zwink

Tempest 4p also appears to not be working in the Manual Stat Entry for Fireball and Frostbolt. It works for AM. Also, Haste rating does not seem to be added in when using the Gear selector.

When you use the Gear Selector do you still need to update Trinket/Item Effects and Set Bonuses on the Main Sheet?

Like many others have said this spreadsheet is great. Thank you for it.

I could swear Manly told me the Tempest bonus was for Arcane Missiles. Or all 3?? Grrrar!

Uploading a fix. And Gear Selector takes care of everything, you shouldn't have to switch on any trinkets or effects with the Main Sheet.

Samuraiboy · 08/23/07, 9:52 PM

Great work on the spreadsheet Vontre.
I found 1 bug, but it may just be because I am an excel/TC noob.
In the gear selector, under rings, not only is it impossible to differentiate between the 12 Violet Signets, but all of them I think give no stats(or no important mage stats.) So even if I had managed to figure out which one was the one i needed, it still wouldn't give me the right stats.

Again, this bug could just be because I'm a noob, but when using Gear Selector, the spellfire set bonus gets counted twice. It gets counted first in the "Calculation Sheet" at F7 which shows your base damage, which already incorporates the spellfire set bonus using my unbuffed intellect. BUT, then it counts the spellfire set bonus again, in cell F47 in the same sheet, when it takes the spellfire improvements of the BUFFED intellect. I think what you meant to do was to use the buffed intellect, and then subtract the damage added from the bonus from the unbuffed intellect.

Edit: problem is not just in F47, but every Base Bonus Damage cell in the calculations page.

Edit: Found 2 more bugs I think

1. In the buffed and talented mana pools on the "Calculations sheet" for all talent trees(e.c. G20 and H20), the base mana added is only 933, instead of 1961. 1961 is added properly to the unbuffed mana pool.

2. In the "Cycles sheet", the DPS cells for Cast 1, 2, 3 and 4 of Arcane blast reference the base damage of their respective cast number for the current and Arcane Tree cycles. However, for the Fire and Frost Tree cycles these same casts just reference the base damage of cast 4 of arcane blast. Normally this would not make a difference, because cast 4 of arcane blast does the same amount of damage as cast 1 just in less time. However the damage took into account procs that happen when spells are casts(e.c. Spellstrike set bonus), and so a faster cast speed would make it more likely to proc, and thus the numer reflects more damage than it should. I would suggest making the frost and fire cells also reference their respective casts of arcane blast also.

Let me know if I stated any of these bugs unclearly, I can see how it might be a bit confusing.

Gregory · 08/24/07, 3:19 AM

Is it possible to show all cycles dps/dpm values on main sheet not just a best one ?
Gear selector for compare stats option would be nice too.

Lemming · 08/24/07, 12:32 PM

Am I going crazy, or is The Maelstrom's Fury not in the list of weapons anywhere? I checked for Maelstrom's Fury and THE Maelstrom's Fury in both 1-hand list and 2-hand list and can't seem to find it.

Also, I'm having trouble finding the correct "Band of Eternity". There are a ton in the list without any indication of which is which (I guess the tank/melee dps/healing/and caster rings are all called the same?). And when I select different ones in the list, it doesn't seem to change the stats at all, and I go back to select a different one and the one that is selected is the one at the top of the list (if that makes any sense). Any suggestions?

Samuraiboy · 08/24/07, 12:49 PM

Originally Posted by Lemming

Also, I'm having trouble finding the correct "Band of Eternity". There are a ton in the list without any indication of which is which (I guess the tank/melee dps/healing/and caster rings are all called the same?). And when I select different ones in the list, it doesn't seem to change the stats at all, and I go back to select a different one and the one that is selected is the one at the top of the list (if that makes any sense). Any suggestions?

That is the same issue that I just mentioned concerning the violet signet.

Roywyn · 08/25/07, 7:57 AM

Vontre, there are a few oddities/bugs with your spreadsheet.
You can see them when you go to the Main Sheet, set all entries in Stat Comparison to 0, and see a lot of numbers come up in the Difference, although there should be no difference at all.

Any, let's be more constructive now:

Game Stats (sheet) - Ashtongue haste should be 0.092 (9.2%) when patch 2.2 hits, and Quag's haste is still converted with 21/1%. Just so you know to poke that entry one day.

Calculations (sheet) -I think if I got that right, your spell cast times are basically a bit messy.

- I assume the numbers block on the left (E-S) are with your current stats, the right (V-AJ) are with the stat difference.
From top to bottom, the stats are for your current spec, fire spec, frost spec, arcane spec.

If so, the calculation for your spells with current gear and spec and outdated. Just copy the lines 154-157, 164, column G-S, and insert them into lines 54-57,64.
The haste and lag calculations are off there, resulting in the the odd difference numbers if you enter no gear difference.

And Arcane Missiles are bugged in all calculations (except for the current gear/spec ones, which calculates everythign else wrong).

"=($B$80)/$B$31/$B$32+'Main Sheet'!$B$48" is the calculation for fire spec for example.
$B$80 is 0.9, the multiplier for stopcasting AM earlier. But the 5s channeling time is missing in the calculation.
=('Game Stats'!$B$39*$B$80)/$B$31/$B$32+'Main Sheet'!$B$48 should do the trick?

Enter that into AA64 (current spec/different gear), and J64, J164, J264, J364, worked for me! :o

On a note about the haste proc trinkets:

I think it would be better to just calculate the average haste rating from Ashtongue/Quag's like you do it now, and then recalculate the cast time as "casttime/(1+0.01*(hastegear+average_hasteproc)/15.7)", and then add the caster lag and GCD cap (currently, your haste reduces your cast delay - my opinion is that cast delay is just human/connection lag and not affected by haste).

Anyway, enough ranting. Great spreadsheet! One question though:
"AM .5 sec. stopcast:" You mean you stopcast AM 0.5s before the cast ends, because the last missile shoots at 4.5s and nothing happens after that?

vokzhen · 08/26/07, 3:29 AM

Maybe I'm missing something, but the gear selection is horribly messed up, at least in OpenOffice. I'm getting gear selection menus from the text of each of the two columns (Quotes about weapons being two selections, name of each piece of gear in that list, to-do list and procs from gear can be selected for each slot; no items actually available).

◊ Vontre · 08/27/07, 1:06 PM

Sorry, but I don't have the means to support Open Office to that degree right now =\. The normal stat entry on the Main Sheet should work for open office still.

Roywyn:

My latest version of the spreadsheet (v2.7.0.2) has both the Ashtongue and Quagmirran's values entered directly as haste rating, not a percentage value, so it will convert accurately to the new formula. Haste rating coefficient should be at cell B101. If it's not there you must have gotten a dated version somehow.

Also, all 4 of my calculations tables are synced correctly. The left side is for gear comparison (grey area on the main sheet), the bottom 3 are for predefined talent sets for fire, frost, and arcane. I recopied the calculations and they didn't change anything.

You suggested this: "=($B$80)/$B$31/$B$32+'Main Sheet'!$B$48"

$B$80 is a yes/no field, leading me to believe you don't have the latest version.

All that being said I'm going to reevaluate the way I calculate haste rating soon. The way it works now may be confusing you... the first value is on row 64, then it jumps to 54 and goes until the final casting time at row 57. I'll probably swap these rows in a future revision. I had to split the calculations to avoid an infinite loop with Ashtongue's.

Edit: Actually I'm certain you aren't using the latest version, "AM .5 sec. stopcast:" has been removed from the spreadsheet entirely.

◊ Vontre · 08/27/07, 4:23 PM

Ok, I uploaded another revision (v2.7.1), I added a stat tooltip to the gear selector, I fixed the violet signet, and I updated to the correct spell haste formula.

Roywyn · 08/27/07, 6:13 PM

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

◊ Vontre · 08/27/07, 7:01 PM

Originally Posted by Roywyn

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.

That's an interesting idea and I'll see what I can do with it.

Aldric · 08/27/07, 7:09 PM

Originally Posted by Roywyn

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.

I kind of like that idea, but it might be more interesting (and probably easier) to see a remaining mana value along with the cycle. This would give us some point in time where it would be optimal to switch over to your high burn rotation. Or Vontre could pull off some crazy Vontre excell math to compute which combination of cycles would give the highest overall dps

I think I would be satisfied with just a remaining mana total though

edit: beaten

vokzhen · 08/28/07, 2:41 AM

I don't know if it's bugged or something, but I was briefly goofing around and noticed changed spriest mp5 values didn't do anything, while sticking it in the gear had the more expected results. Again, OpenOffice, dunno if that messes something up. Might be tied in with what Roywyn was talking about, but I'm not sure, since enter 200mp5 in one changed stuff and didn't in the other.

I also think it'd be interesting to include the rotation a->rotation b->end oom to help show how much AB spamming at the end actually does. I'm an excel idiot, so I don't know if you've done it, but it'd also be nice to try and model Molten Fury.

By the sounds of some of the arcane mage threads, you'll have some updated to do come 2.2 :P Wondering if, during that, you could include [Pendant of the Violet Eye] and [Mark of Defiance] as well, since (last I checked the threads anywho) they're both proccing off every AM tick, which gives a huge advantage to AM spam.

And a couple quick functionality questions, since as I said, I'm an excel idiot:
Build Comparisons - any way of seeing what rotations these totals are being compared to, and the respective oom time? I'm assuming it takes you spec and finds the highest overall damage from a rotation, though I guess I don't know for sure.
Any way of, under the rotation builder, of forcing AM on clearcasts as you've done with one of the rotations?
How is the damage after going OOM calculated? Is it wanding, taking into consideration spirit regen and JoW, and casting as soon as mana is available? Might be worth allowing input as to how much time is spent waiting for mana - say, 20 seconds, or till 2000 mana has been gained via regen/JoW. Along the same lines, are evo//mana gems/pots converted to mp5, or actually calculated as being used at certain times? I assume if the latter, I assume item that gains x mana is used as soon as the mage is short x mana?

You don't have doomkin listed as a possible buff ;P Also, might want to add dark/demonic runes to possible "pot" usage.

Roywyn · 08/28/07, 5:39 AM

Right, I now managed to grab the right spreadsheet, I hope. 2.7.1

One nitpick - the AB cycle calculations use the cast time numbers without the lag.

The "cycles" sheet (and cycles_2_) uses the "cast times without casting lag" for its calculations.
I changed the "=Calculations!$L55" in line 23 (cycles) to =Calculations!$L57", to get the "cast time 2 (gcd capped)" for the cycles (changed lines 23, 123, 223, 323 and line 2 in cycles_2_).

I did the following change to the Cycles spreadsheet to add the added mana burn with using one more arcane blast to burn some leftover mana:
(WENN is the German name for the If-then-else(.;.;.) function, MIN and MAX are mininum and maximum, probably the same as in German.)

B31
=WENN(B51<=$B$31;B52+MAX(MIN(($B$31-B51)/(C51-B51);1);0)*(C52-B52);0)

B32
=WENN(B51<=$B$31;WENN($K53=B53;B$34;0);0)

Then copied that down to the other cycles, and replaced $31 and $34 by $131/$231/$321 for the other specs, and by $11 for cycles_2_

Explanation:

$B$31-B51 = is the difference between the target and the current mana consumption rate. It's the remaining mana/sec that remains unspent.
C51-B51 = is the difference between this cycle's mana consumption rate and the next cycle's (with one more AB). It's the mana/sec that's needed to that cycle full time.
The quotient of those is the percentage of time that you can use the second cycle.

This quotient becomes <0 if the current cycle is not sustainable, and >1 if he next cycle can be sustained for the whole fight.
MAX(MIN(($B$31-B51)/(C51-B51);1);0) limits that number to 0 (if <0) from below and 1 from above (if >1).
B52+MAX(MIN(($B$31-B51)/(C51-B51);1);0)*(C52-B52) = then is simply the DPS of the combined cycles. B52 is the DPS of your current cycle, and you add the amount of time spent with the next time multiplied with the DPS difference (gain) of the next cycle.

WENN(B51<=$B$31;WENN($K53=B53;B$34;0);0) = does the same as before, needed a small change to detect/check the right numbers.

Edit: What the? Everything gets messed up when I try to write "less or equal", i.e. the "<=" sign. Used [code] to fix that.

Keleborn · 08/30/07, 5:08 PM

I belive the armory import is not working as intended. When i upload the profile all the stats are reduced to 0 and it literally shows the armory page on the spreadsheet

Edit: I am running excel '03. Also i belive the armory domain has changed form armory.worldofwarcraft.com to wowarmory.com not sure if that could cause an issue

Balynn · 08/31/07, 12:17 PM

I've got a question about Water Elemental DPS. I apologize in advance if this has already been answered, but I did a quick search and didn't see anything.

If I manually enter 1092 spell damage (all schools), and zero frost damage, it calculates the WE at 448dps. If I swap those values (zero to all schools, 1092 frost), and leave everything else the same, it has the WE at 701dps. Finally, zeroing out both of them puts him at 228dps.

Is this a bug, or does the WE actually get more of a bonus from pure +frost dmg?

Eisenwrought · 09/01/07, 10:23 PM

Originally Posted by Keleborn

I belive the armory import is not working as intended. When i upload the profile all the stats are reduced to 0 and it literally shows the armory page on the spreadsheet

Edit: I am running excel '03. Also i belive the armory domain has changed form armory.worldofwarcraft.com to wowarmory.com not sure if that could cause an issue

I am also having this problem and I am using Excel 2007.

Selecting e19 as a cell does not bring up the proper "get external data" options

I have tried going under Connections->Properties->Definition->Edit Query which causes the address to automatically change to The Armory in the address box yet results in "The XML Page cannot be Displayed --> Cannot view xml input using style sheet. Please correct.... Access is denied error processing resource 'http://armory.worldofwarcraft.com/character-sheet.xml?r=Lightinghoof&n=Vontre..."

Clicking on GO next to the correct wowarmory link loads the proper page and gives one arrow to click on for the entire page as a table selection. Selecting the table and importing results in data being filled into cells to the right and downwards of E19 with all stats and values going to zero.

◊ Vontre · 09/02/07, 5:21 AM

Originally Posted by Balynn

I've got a question about Water Elemental DPS. I apologize in advance if this has already been answered, but I did a quick search and didn't see anything.

If I manually enter 1092 spell damage (all schools), and zero frost damage, it calculates the WE at 448dps. If I swap those values (zero to all schools, 1092 frost), and leave everything else the same, it has the WE at 701dps. Finally, zeroing out both of them puts him at 228dps.

Is this a bug, or does the WE actually get more of a bonus from pure +frost dmg?

Must be a bug, I'll take a look at it.

Asahina · 09/04/07, 5:39 AM

This is probably me being stupid again but I'm unsure if the 5% bonus damage from 4 piece tempest is being taken into account. When wearing only 3 bits and the mantle of nimble thought, I am getting more dps than if I am wearing the tempest shoulders too. Is this right and 38 haste rating is really better than 5% fireball damage, or am I missing something?

jogjog · 09/04/07, 6:36 AM

5% on FB base dmg.... maybe

◊ Vontre · 09/04/07, 8:23 PM

Originally Posted by Asahina

This is probably me being stupid again but I'm unsure if the 5% bonus damage from 4 piece tempest is being taken into account. When wearing only 3 bits and the mantle of nimble thought, I am getting more dps than if I am wearing the tempest shoulders too. Is this right and 38 haste rating is really better than 5% fireball damage, or am I missing something?

Ah actually there is a problem with the logic handle for this, it's giving you the set bonus at 3 pieces instead of 4. I'll have a hotfix in a few days probably (little busy here at work).

maxi · 09/07/07, 10:17 AM

Sorry if this question was answered already, but i was wondering if Arcane Power yes/no field should affect the Build Comparison Fire and Frost fields?

Tarrke · 09/10/07, 3:53 PM

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.

Roywyn · 09/10/07, 8:08 PM

Originally Posted by Tarrke

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.

The sheet doesn't calculate anything stamina, that's why there is no socket bonus for the spellstrike items listed.